0

http://docs.oracle.com/cd/B14117_01/appdev.101/b10779/oci05bnd.htm#422771

section PL/SQL REF CURSORs and Nested Tables in OCI

says SQLT_RSET is passed for the dty parameter.

If I use SQLT_RSET for the return value of a function that returns a table and pass a statement handle's address for the OCI parameter data pointer, I expected that the statement handle will be instantiated as a result of executing the function on which I can further perform fetch, similar to a cursor. But it throws exception PLS-00382: expression is of wrong type ORA-06550: line 2, column 3. Is the above documentation wrong?

From the OCI header file I see that for varray and nested table it mentions to use SQLT_NCO. I could find no example in the OCI documentation on how to pass or receive as return value a nested value when using SQLT_NCO.

Please help before I shoot myself.

Walking Corpse
  • 107
  • 7
  • 31
  • 49

1 Answers1

0

If you examine examples closer, you can see that nested table used in conjunction with cursor() function. E.g. 

static const text *nst_tab = (text *)
       "SELECT last_name, CURSOR(SELECT department_name, location_id \
        FROM  departments)  FROM employees WHERE last_name = 'FORD'";

where key part for your question is 

CURSOR(SELECT department_name, location_id FROM  departments)

where departments is a nested table.
So if you need to use nested table as a cursor you need to select from it and convert to cursor type.

In case of PL/SQL collection variable, if her record's type defined on database schema level you can cast it to a table and afterward select from it to get a cursor:

declare
  vMyTable TMyTableType;
  vCursor  sys_refcursor;
begin
  -- populate table with values ...

  open vCursor for (
    select * from table(vMyTable);
  );

  :OutCursorParameter := vCursor;
end;
ThinkJet
  • 6,725
  • 24
  • 33
  • Ok that explains a lot. But my requirement is to call a stored function that returns a cursor or a nested table, and from the documentation I quoted I assumed I could bind them both similarly using SQLT_RSET and a OCIStmt*. I don't have the privilege of doing a select on top of nested table, because there is no way to get metadata about the return type, is there? – Walking Corpse Sep 16 '13 at 13:11