Select data from a record collection type.

Question

I have a stored proc in oracle 11g server that has an out variable of record. I cannot edit this procedure. I am creating a function that will call the procedure and return the information in the record set. I looked at the following question asked here: past question

My Question is can I create a type of table for a record and query it directly in SQL? Or do I need to convert the record to type object and create a table type for it to query directly?

Answer 1

The question your link points to is tagged Oracle, so I'm assuming that's what you're using.

The easiest way is probably returning a CURSOR:

SQL> VAR cr_dual REFCURSOR

SQL> BEGIN
  2          OPEN    :cr_dual FOR
  3          SELECT  1
  4          FROM    dual
  5          UNION ALL
  6          SELECT  2
  7          FROM    dual;
  8  END;
  9  /

Процедура PL/SQL успешно завершена.

SQL> PRINT cr_dual

         1
----------
         1
         2

Answer 2

if you are receiving only one record and you know the record definition, you should be able to the field of the record directly by name.

for example:

TYPE myrecord IS RECORD (
       itemA NUMBER,
       itemB myTable.columnName%TYPE
  );

when you have a record of myrecord type, you can reference itemA in a manner like this:

CREATE OR REPLACE FUNCTION myFunction(theRecord IN myrecord%TYPE) RETURN NUMBER
IS
BEGIN    
   RETURN recordInstance.itemA;
END myFunction;

note that you may be able to get around this in your calling code be simply handling the record type that the original procedure returns.

Answer 3

You can try varray.

First -

create or replace type addr_type
as object
(name   varchar2(20)
,city   varchar2(20)
)

And create your varray as

create or replace type varr_addr as varray(10) of addr_type
/

Now you can return work with varr_addr. An example:

SQL> create or replace procedure ret_user_addr (p_out out varr_addr)
  2  is
  3  begin
  4    p_out := varr_addr(addr_type('NAME1','CITY1'),addr_type('NAME2','CITY2'));
  5  end;
  6  /

Procedure created.

SQL> sho err
No errors.

Now you need have the out variable configured correctly from where you call.And you can select from table(VARIABLE_NAME) as you would do.

Answer 4

RECORD is a PL/SQL concept. So we cannot create a TABLE TYPE based on a RECORD and use that TYPE in a SQL statement. SQL only recognises TYPEs which are created in SQL.

So, if I understood your scenario correctly, you need to create an object and/or a nested table in SQL. Then your function can call the procedure and translate the RECORD to the nested table. The nested table can then be returned or pipelined.