Select data from record collection type

Question

I have a saved proc in oracle 11g server that has a variable variable. I can not edit this procedure. I am creating a function that will call a procedure and return information to a recordset. I reviewed the following question asked here: last question

My question is: can I create a table type for writing and query it directly in SQL? Or do I need to convert a record to an object type and create a table type for a direct query?

Answer 1

RECORD is a PL / SQL concept. Therefore, we cannot create a TABLE TYPE based on RECORD and use this TYPE in an SQL statement. SQL only recognizes TYPEs that are created in SQL.

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

Answer 2

The question your link points to is marked Oracle, so I assume you are using.

The easiest way is probably to return 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 3

.

Answer 4

, .

:

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

myrecord, itemA , :

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

, , .

Answer 5

varray.

-

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

varray

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

varr_addr. :

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 the out variable to be set correctly from where you are calling. And you can select from the table (VARIABLE_NAME), just like you.