Oracle 11g R2 – Determine Structure of OUT Cursor

cursorsoracleoracle-11g-r2stored-procedures

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;
/

If 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 VARCHAR2, a NUMBER, and a RAW in that order so I can define variables to hold the results.

I'm using Oracle 11.2.0.1.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.

Best Answer

This example code shows how to parse a ref_cursor. There are additional columns available, see the definition of SYS.DBMS_SQL.desc_tab2 for a full list of available columns.

In this example, my ref_cursor is defined as select * from user_tables. You would of course substitute in your own ref_cursor.

DECLARE
   l_csrid       INTEGER;
   l_coldesc     SYS.DBMS_SQL.desc_tab2;
   l_colcnt      INTEGER;
   l_refcursor   SYS_REFCURSOR;

   FUNCTION tostring (p_label   IN VARCHAR2
                     , p_value   IN VARCHAR2)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN RPAD (p_label, 30)
             || ': '
             || p_value
             || UTL_TCP.crlf;
   END tostring;

   PROCEDURE myput_line (p_rec      IN DBMS_SQL.desc_rec2
                       , p_colnum   IN INTEGER)
   AS
   BEGIN
      DBMS_OUTPUT.put_line (   RPAD ('_', 30, '_') 
                        || UTL_TCP.crlf
                        || tostring (p_label => 'Column'
                                   , p_value => p_colnum)
                        || tostring (p_label => 'Name'
                                   , p_value => p_rec.col_name)
                        || tostring (p_label => 'Type'
                                   , p_value => p_rec.col_type)
                        || tostring (p_label => 'Null OK'
                                   , p_value => CASE WHEN p_rec.col_null_ok
                                                     THEN 'Yes' 
                                                     ELSE 'No' 
                                                END));
   END myput_line;
 BEGIN
   OPEN l_refcursor FOR
      SELECT * FROM user_tables;

   l_csrid   := DBMS_SQL.to_cursor_number (rc => l_refcursor);
   DBMS_SQL.describe_columns2 (c       => l_csrid
                             , col_cnt => l_colcnt
                             , desc_t  => l_coldesc);

   FOR i IN 1 .. l_colcnt LOOP
      myput_line (l_coldesc (i), i);
   END LOOP;
END;

Related Solutions

How to set the sequence to the last used id for all tables in Oracle

First you have to find out what the current sequence value is and then reset it. I would write a script which does the following tasks:

Get the max value from the table
update/recreate the sequence

The script could look like this:

declare
  maxval number;
begin
  select max(id) into maxval from <table_1>;
  execute immediate 'ALTER SEQUENCE <sequence_name_1> START WITH '|| maxval+1 ||';';

  select max(id) into maxval from <table_2>;
  execute immediate 'ALTER SEQUENCE <sequence_name_2> START WITH '|| maxval+1 ||';';
end;
/

I'm pretty sure there are better scripts but if you have to do this task frequently this might do the job without much development time.

SQL Server – Improve Query Performance by Replacing Cursor with SET Based Approach

Your CTE-based approach with window functions is a very good start. There's another, even more suitable window function that you could use: LAG().

Here's how:

SELECT iVehicleMonitoringId AS CurrentID,
       LAG(iVehicleMonitoringId, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousID, 
       iAssetId AS CurrentAsset,
       LAG(iAssetId, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousAsset,
       dtUTCDateTime AS CurrentTime,
       LAG(dtUTCDateTime, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousTime,
       DATEDIFF(second,
           dtUTCDateTime,
           LAG(dtUTCDateTime, 1) OVER (ORDER BY dtUTCDateTime)
           ) AS DateDiffSeconds
FROM VehicleMonitoringLog
WHERE dtUTCDateTime > DATEADD(day, -1, SYSDATETIME());

Basically, LAG(column, n) OVER (ORDER BY x) returns the value of column, n row(s) back (so n=1 returns the previous row), ordered by x.

Your CTE solution will scan VehicleMonitoringLog twice, then join the two streams. This query will perform only a single scan, which is much, much more efficient. The LAG() function (and its cousin LEAD()) is available as of SQL Server 2012.

Partitioning

It looks like you've forgotten some type of partitioning term (iAssetID, pehaps?). The partitioning term serves to separate data points between different vehicles, in case two vehicles are out-and-about at the same time. Add this partition term to the query by changing the OVER() clause from OVER (ORDER BY dtUTCDateTime) to OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime).

Indexing

To make this solution really fly, I would create the following index on VehicleMonitoringLog:

CREATE INDEX... (iAssetID, dtUTCDateTime);   --- if you're using PARTITION BY

.. or

CREATE INDEX... (dtUTCDateTime);             --- without PARTITION BY

Why I can't just look at the source

Best Answer

Related Solutions

How to set the sequence to the last used id for all tables in Oracle

SQL Server – Improve Query Performance by Replacing Cursor with SET Based Approach

Related Question