Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Function to return a Ref Cursor from XML (NOT XML from a Ref Cursor)

Function to return a Ref Cursor from XML (NOT XML from a Ref Cursor)

From: ag1969 <monkey_puzzle_at_hotmail.com>
Date: Thu, 07 Jun 2007 04:34:59 -0700
Message-ID: <1181216099.799889.177470@p77g2000hsh.googlegroups.com>


Is it possible to generate a (weak) cursor from an XML document? There's plenty written about getting XML back from functions, but I already have XML (from various Web Services) and I want to cast it as a Ref Cursor to return to (say) Oracle Forms. The calling program/ client will always know what the cursor columns will be, as they will know what the called web service returns. Something like this:

CREATE OR REPLACE FUNCTION x RETURN SYS_REFCURSOR IS
  v_xml VARCHAR2(4000) :=

    '<?xml version = ''1.0''?>' ||
    '<ROWSET>' ||
    '   <ROW num="1">' ||

' <EMPNO>7369</EMPNO>' ||
' <ENAME>SMITH</ENAME>' ||
' <JOB>CLERK</JOB>' ||
' <MGR>7902</MGR>' ||
' <HIREDATE>12/17/1980 0:0:0</HIREDATE>' ||
' <SAL>800</SAL>' ||
' <DEPTNO>20</DEPTNO>' ||
' </ROW>' || ' <ROW num="2">' ||
' <EMPNO>7499</EMPNO>' ||
' <ENAME>ALLEN</ENAME>' ||
' <JOB>SALESMAN</JOB>' ||
' <MGR>7698</MGR>' ||
' <HIREDATE>2/20/1981 0:0:0</HIREDATE>' ||
' <SAL>1600</SAL>' ||
' <COMM>300</COMM>' ||
' <DEPTNO>30</DEPTNO>' ||
' </ROW>' || '</ROWSET>'; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT * FROM some_kind_of_cast_of_v_xml; RETURN cur; END; I know how this kind of approach works with a PL/SQL table type and use of CAST, but from an XML document? The XML doc can be XMLTYPE, CLOB, or VARCHAR2, I really don't care, it's just that I want a cursor showing the various fields as column, and not XML. Even the scalar types don't matter - they can all be string; let the client program worry about that.

Thanks,

Ant Received on Thu Jun 07 2007 - 06:34:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US