Sara_j_11
asked on
plsql
I have a procedure such as that converts the output of a sql to xml format.
Now I generally run it in toad and see the output in dbms_output window. But I need to automate this so can you please tell me how to run it in unix and save the output of this procedure to a file? I am not allowed to use utl_file.
Please make neccesaary changes to code and post it and tell me how to call procedure from unix etc step by step
DECLARE
queryCtx dbms_xmlquery.ctxType;
result CLOB;
BEGIN
-- set up the query context
queryCtx := dbms_xmlquery.newContext(' SELECT *
FROM test'
);
--If the DTD or XML schema definition has explicitly defined tag names others
--than the column names then you can change the ROW and ROWSET tag names easily:
dbms_xmlquery.setRowTag(
queryCtx
, 'ROW'
);
dbms_xmlquery.setRowSetTag (
queryCtx
, 'PARTNERSALES'
);
--Before executing the query you must bind the values to the SQL statement.
--The named bind variables have to start with a : in front.
-- dbms_xmlquery.setBindValue (
-- queryCtx
-- , 'DEPTNO'
-- , 10
-- );
--Ok, now you are ready to run the query and generate the XML result as CLOB.
--A simple procedure printClobOut() supports printing out a CLOB to screen.
--Finally the query handle must be closed to free the resources.
result := dbms_xmlquery.getXml(query Ctx);
printClobOut(result);
dbms_xmlquery.closeContext (queryCtx) ;
END;
/
Now I generally run it in toad and see the output in dbms_output window. But I need to automate this so can you please tell me how to run it in unix and save the output of this procedure to a file? I am not allowed to use utl_file.
Please make neccesaary changes to code and post it and tell me how to call procedure from unix etc step by step
DECLARE
queryCtx dbms_xmlquery.ctxType;
result CLOB;
BEGIN
-- set up the query context
queryCtx := dbms_xmlquery.newContext('
FROM test'
);
--If the DTD or XML schema definition has explicitly defined tag names others
--than the column names then you can change the ROW and ROWSET tag names easily:
dbms_xmlquery.setRowTag(
queryCtx
, 'ROW'
);
dbms_xmlquery.setRowSetTag
queryCtx
, 'PARTNERSALES'
);
--Before executing the query you must bind the values to the SQL statement.
--The named bind variables have to start with a : in front.
-- dbms_xmlquery.setBindValue
-- queryCtx
-- , 'DEPTNO'
-- , 10
-- );
--Ok, now you are ready to run the query and generate the XML result as CLOB.
--A simple procedure printClobOut() supports printing out a CLOB to screen.
--Finally the query handle must be closed to free the resources.
result := dbms_xmlquery.getXml(query
printClobOut(result);
dbms_xmlquery.closeContext
END;
/
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.