CODEWITHSUNDEEP
sqloracle-databaseplsql
Floppy Retop
Floppy Retop

Reputation: 13

How to iterate through a sys_refcursor with unknown type?

Let's define a function 

create or replace function GET_SOME_CURSOR(X number) return sys_refcursor is
  R sys_refcursor;
begin
  open R for
    select * from MY_TABLE T where T.RATING = X;
  return R;
end;

Suppose we don't know what names MY_TABLE columns have (in addition to RATING). Please, tell me how to solve the following task. Maybe it looks pointless, but I hope your solution will show me some aspects of PL/SQL I need to know.

Write a function which in cycle (for x = 1 to 10) gets another refcursor from GET_SOME_CURSOR(x) and if a column named TITLE is NOT present in the refcursor result set then return NULL, otherwise find all rows in the result set, which contain substring 'ABC' in the TITLE column and put into an array (or anyting you want) and return this array.

Upvotes: 1

Views: 4331

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59455

This one should provide desired information

DECLARE
    r SYS_REFCURSOR;

    cur INTEGER;
    col_tab DBMS_SQL.DESC_TAB;
    col_cnt INTEGER;

BEGIN
    OPEN r FOR 
    select * from MY_TABLE T where T.RATING = X;

    cur := DBMS_SQL.TO_CURSOR_NUMBER(rc);
    DBMS_SQL.DESCRIBE_COLUMNS(cur, col_cnt, col_tab);
    FOR i IN 1..col_cnt LOOP
        DBMS_OUTPUT.PUT_LINE('Column '||i||': '||col_tab(i).col_name 
            ||' Data type is: '||col_tab(i).col_type );
    END LOOP;
    CLOSE r;
END;

Translation from col_type number to readable data type string you get by this query:

SELECT text
FROM all_source
WHERE owner = 'SYS' 
    AND NAME = 'DBMS_TYPES' 
    AND TYPE = 'PACKAGE' 
    AND REGEXP_LIKE(text, 'TYPECODE');

Upvotes: 4

Related Questions