Hi All,

I have a req. to write a store proc. (in oracle) which will fire a query and store the output
in a clob variable (don't know weather it is possible or not) and that “Clob“would be return as output of the procedure. Actually I need an XML string as output of the procedure can it be possible? Any kind of Help would be highly appreciable.

Regards
Alok

PROCEDURE sp_test_XML(plgcode IN VARCHAR2,result out clob) IS

    qryCtx DBMS_XMLGEN.ctxHandle;
    parent_id NUMBER;
    queryVal varchar2(20000);


BEGIN
            select id into parent_id from HRDW_ADMIN.BUDGET_CONTROL_DOLLAR where BUDGET_CONTROL_DOLLAR.PLG_CODE=plgcode;

                    queryVal:='SELECT * from HRDW_ADMIN.BUDGET_CONTROL_DOLLAR_AUDIT where PARENTID = '|| to_char(parent_id);

                    qryCtx := DBMS_XMLGEN.newContext (queryVal);

                    DBMS_XMLGEN.setRowTag(qryCtx, 'RECORDS');

                    result := DBMS_XMLGEN.getXML(qryCtx);

                    DBMS_XMLGEN.closeContext(qryCtx);

            EXCEPTION WHEN NO_DATA_FOUND THEN

                    queryVal:='SELECT ' || '''NA''' || ' as DATA from DUAL';

                    qryCtx := dbms_xmlgen.newContext (queryVal);

                    DBMS_XMLGEN.setRowTag(qryCtx, 'RECORDS');

                    result := DBMS_XMLGEN.getXML(qryCtx);

                    DBMS_XMLGEN.closeContext(qryCtx);
END sp_test_XML;

Alok