Return A Clob in a Ref Cursor

PaulS · #1 (**permalink**) 10-29-03, 14:04

Is there anyway you can select a clob datatype from dual in Oracle?

The reason i want to do this is that I have a Store Proc that is using the dbms_xmlgen package to generate XML. The generated XML is then stored in a CLOB which i then want to pass out of the Stored Proc as part of a Ref Cursor. This is what i have tried:

Code:

  PROCEDURE spGetXML( pID IN NUMBER,  				                     pRefCur OUT TypeRefCur) 
  IS
      qryCtx DBMS_XMLGEN.ctxHandle;
      CLOB_XML CLOB;
 BEGIN
      qryCtx := dbms_xmlgen.newContext( SELECT STATEMENT);
      CLOB_XML := DBMS_XMLGEN.getXML(qryCtx);

      OPEN pRefCur FOR
               SELECT CLOB_XML FROM dual;
 END

If i insert the genrated XML clob into a table and then SELECT * FROM table as the pRefCur then this works but i don't want to create a new table just to deal with this.

Any suggestions or alternative solutions would be much appreciated.

Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech