I have a function in Oracle that neither I nor my team wrote and is heavily obfuscated. However, it has a cursor out parameter. How can I find out the number and types that the cursor contains?
For an example of what I need, consider this cursor:
DECLARE TEMP1 VARCHAR2(500); TEMP2 NUMBER; TEMP3 RAW(16); TEMP SYS_REFCURSOR; PROCEDURE MAKE_TEMP(TEMP_RETURN OUT SYS_REFCURSOR) IS BEGIN OPEN TEMP_RETURN FOR SELECT DUMMY, 5, HEXTORAW('111') FROM DUAL; END; BEGIN MAKE_TEMP(TEMP); LOOP FETCH TEMP INTO TEMP1, TEMP2, TEMP3; EXIT WHEN (TEMP%NOTFOUND); DBMS_OUTPUT.PUT_LINE('1: '||TEMP1); DBMS_OUTPUT.PUT_LINE('2: '||TO_CHAR(TEMP2)); DBMS_OUTPUT.PUT_LINE('3: '||RAWTOHEX(TEMP3)); END LOOP; CLOSE TEMP; END; /
MAKE_TEMP were the function I'm trying to use, I would need to know that it has 3 values per row, and that the values are a
NUMBER, and a
RAW in that order so I can define variables to hold the results.
I'm using Oracle 22.214.171.124.0, although I would appreciate if answers for newer versions exist as well.
Why I can't just look at the source
If you must know, the function is part or Oracle ASP.NET Membership Provider and is written by Oracle. I'm trying to debug a problem and would like to see what this function is returning. The function's source code starts like this:
create or replace FUNCTION "ORA_ASPNET_MEM_GETALLUSERS" wrapped 0 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 3 8 9200000 1 4 0 33
As you can see, it's (at best) going to be extremely difficult to extract any information about the cursor of that.