Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Alper.

Asked: November 22, 2017 - 8:37 am UTC

Last updated: November 29, 2017 - 2:01 pm UTC

Version: 12C r2

Viewed 1000+ times

You Asked

How can i get value from sql using dbms_sql type of CURSOR ....COL_TYPE=102


DECLARE
   RUN_S         CLOB;
   IGNORE        NUMBER;
   SOURCE_CURSOR NUMBER;
   PWFIELD_COUNT NUMBER DEFAULT 0;
   L_DESCTBL     DBMS_SQL.DESC_TAB2;
   Z_NUMBER      NUMBER;
BEGIN
   RUN_S         := ' SELECT 1      AS VAL1,
                             2      AS VAL2,
                             CURSOR (SELECT 11 AS VAL11,
                                            12 AS VAL12
                                       FROM DUAL) AS CUR1,
                             CURSOR (SELECT 11 AS VAL11,
                                            12 AS VAL12
                                       FROM DUAL) AS CUR2
                        FROM DUAL';
   SOURCE_CURSOR := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(SOURCE_CURSOR, RUN_S, DBMS_SQL.NATIVE);
   DBMS_SQL.DESCRIBE_COLUMNS2(SOURCE_CURSOR, PWFIELD_COUNT, L_DESCTBL); -- get record structure
   FOR I IN 1 .. PWFIELD_COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Type:' || L_DESCTBL(I).COL_TYPE);
      IF L_DESCTBL(I).COL_TYPE = 2 THEN
         DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR, I, Z_NUMBER);
      elsiF L_DESCTBL(I).COL_TYPE = 102 THEN
      -- ?????
      --   DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR, I, Z_NUMBER);
      null;
      END IF;
      NULL;
   END LOOP;
   IGNORE := DBMS_SQL.EXECUTE(SOURCE_CURSOR);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(SOURCE_CURSOR) > 0 THEN
         FOR I IN 1 .. PWFIELD_COUNT LOOP
            IF L_DESCTBL(I).COL_TYPE IN (2) THEN
               DBMS_SQL.COLUMN_VALUE(SOURCE_CURSOR, I, Z_NUMBER);
               DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Value:' || Z_NUMBER);
            END IF;
         END LOOP;
      ELSE
         EXIT;
      END IF;
   END LOOP;
END;
/


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_FVTC3768NDW17ATLDUGVAGB1K.html

and Chris said...

So you have a cursor expression in a dynamic SQL statement. And you want to fetch the values from this?

Sadly DBMS_SQL doesn't support this.

As a workaround, you could:

- Convert the DBMS_SQL cursor to a ref cursor (to_refcursor)
- Use standard PL/SQL looping and fetching to extract the values

For example:

DECLARE  
   RUN_S         CLOB;  
   IGNORE        NUMBER;  
   SOURCE_CURSOR NUMBER;    
   
   cur sys_refcursor;
   v1 integer;
   v2 integer;
   c1 sys_refcursor;
   c2 sys_refcursor;
   c1v1 integer;
   c1v2 integer;
BEGIN  
   RUN_S         := ' SELECT 1      AS VAL1,  
                             2      AS VAL2,  
                             CURSOR (SELECT 11 AS VAL11,  
                                            12 AS VAL12  
                                       FROM DUAL) AS CUR1,  
                             CURSOR (SELECT 11 AS VAL11,  
                                            12 AS VAL12  
                                       FROM DUAL) AS CUR2  
                        FROM DUAL';  
   SOURCE_CURSOR := DBMS_SQL.OPEN_CURSOR;  
   DBMS_SQL.PARSE(SOURCE_CURSOR, RUN_S, DBMS_SQL.NATIVE);  
   IGNORE := DBMS_SQL.EXECUTE(SOURCE_CURSOR);  
   cur := dbms_sql.to_refcursor(SOURCE_CURSOR);
   
   loop
     fetch cur into v1, v2, c1, c2;
     exit when cur%notfound;

     loop
       fetch c1 into c1v1, c1v2;
       exit when c1%notfound;
       dbms_output.put_line('C1V1: ' || c1v1 || ' C1V2: ' || c1v2);
     end loop;
   end loop;
   close cur;
end;
/

C1V1: 11 C1V2: 12


Or, if you're feeling brave and like playing with XML, you can pass the cursor into an XMLtype. Then process the results as XML:

DECLARE  
   RUN_S         CLOB;  
   IGNORE        NUMBER;  
   SOURCE_CURSOR NUMBER;  
   PWFIELD_COUNT NUMBER DEFAULT 0;  
   L_DESCTBL     DBMS_SQL.DESC_TAB2;  
   Z_NUMBER      NUMBER;  
   xdoc          XMLTYPE;
BEGIN  
   RUN_S         := ' SELECT 1      AS VAL1,  
                             2      AS VAL2,  
                             XMLTYPE( CURSOR (SELECT 11 AS VAL11,  
                                            12 AS VAL12  
                                       FROM DUAL)
                             ) AS CUR1,  
                             XMLTYPE ( CURSOR (SELECT 11 AS VAL11,  
                                            12 AS VAL12  
                                       FROM DUAL)
                             ) AS CUR2  
                        FROM DUAL';  
   SOURCE_CURSOR := DBMS_SQL.OPEN_CURSOR;  
   DBMS_SQL.PARSE(SOURCE_CURSOR, RUN_S, DBMS_SQL.NATIVE);  
   DBMS_SQL.DESCRIBE_COLUMNS2(SOURCE_CURSOR, PWFIELD_COUNT, L_DESCTBL); -- get record structure  
   FOR I IN 1 .. PWFIELD_COUNT LOOP  
      DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Type:' || L_DESCTBL(I).COL_TYPE);  
      IF L_DESCTBL(I).COL_TYPE = 2 THEN  
         DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR, I, Z_NUMBER);  
      elsiF L_DESCTBL(I).COL_TYPE = 109 THEN  
         DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR, I, xdoc);  
      null;  
      END IF;  
      NULL;  
   END LOOP;  
   IGNORE := DBMS_SQL.EXECUTE(SOURCE_CURSOR);  
   LOOP  
      IF DBMS_SQL.FETCH_ROWS(SOURCE_CURSOR) > 0 THEN  
         FOR I IN 1 .. PWFIELD_COUNT LOOP  
            IF L_DESCTBL(I).COL_TYPE IN (2) THEN  
               DBMS_SQL.COLUMN_VALUE(SOURCE_CURSOR, I, Z_NUMBER);  
               DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Value:' || Z_NUMBER);  
            ELSIF L_DESCTBL(I).COL_TYPE IN (109) THEN
               DBMS_SQL.COLUMN_VALUE(SOURCE_CURSOR, I, xdoc);  
               DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Value:' || xdoc.getStringVal);  
            END IF;  
         END LOOP;  
      ELSE  
         EXIT;  
      END IF;  
   END LOOP;  
END;  
/

Col 1 Type:2
Col 2 Type:2
Col 3 Type:109
Col 4 Type:109
Col 1 Value:1
Col 2 Value:2
Col 3 Value:<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <VAL11>11</VAL11>
  <VAL12>12</VAL12>
 </ROW>
</ROWSET>

Col 4 Value:<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <VAL11>11</VAL11>
  <VAL12>12</VAL12>
 </ROW>
</ROWSET>

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good alternatives.

alper tandogan, November 27, 2017 - 5:22 pm UTC

I use TXL type.. Also i use this codes bottom below. For transfering sql to xml with in cursor type field defination.

But this dbms_xmlgen works very slow.
Insead of this i use my way dbms_sql .. With your 109 type XML return

Also must be return for type 102 (ref cursor etc.like your solution first but its good for fix coloumn in CURSOR type )

Also i know 12r2 has json return from sql but Its to compilated and slow with huge records according to dbms_sql or native way.


-------------sql to xml ----------
l_context_handle := dbms_xmlgen.newcontext(i_sql_string);
dbms_xmlgen.setnullhandling(l_context_handle, dbms_xmlgen.empty_tag);

l_xml := dbms_xmlgen.getxmltype(l_context_handle, dbms_xmlgen.none);
l_rows := dbms_xmlgen.getnumrowsprocessed(l_context_handle);
dbms_xmlgen.closecontext(l_context_handle);

Best regards.
Chris Saxon
November 28, 2017 - 2:01 pm UTC

Thanks... but I'm not sure if you've got another question or not!

Could you clarify please?

L_DESCTBL(I).COL_TYPE = 102 --Return ??? TYPE.

alper tandogan, November 28, 2017 - 2:49 pm UTC

There must be solution COL_TYPE=102 like COL_TYPE=109

All solution it works but..

My final question for this topic.

declare
xdoc XMLTYPE;
T102 ????TYPE;--sys_refcursor like but internal samething

BEGIN
...
elsiF L_DESCTBL(I).COL_TYPE = 109 THEN
DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR, I, xdoc);
elsiF L_DESCTBL(I).COL_TYPE = 102 THEN
DBMS_SQL.DEFINE_COLUMN??(SOURCE_CURSOR, I, T102 );
...

Best regards.

Chris Saxon
November 29, 2017 - 2:01 pm UTC

Why must there be a solution for cursors?

I don't see them in the list of supported data types for define_column:

BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NUMBER
TIME_UNCONSTRAINED
TIME_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
YMINTERVAL_UNCONSTRAINED
user-defined object types
collections (VARRAYs and nested tables)
REFs
Opaque types


https://docs.oracle.com/database/122/ARPLS/DBMS_SQL.htm#GUID-7D8E4D94-34D9-40E5-B404-D6201C9F0DE0
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library