Link to home
Create AccountLog in
Avatar of Sara_j_11
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(queryCtx);
  printClobOut(result);
  dbms_xmlquery.closeContext(queryCtx);
END;
/
ASKER CERTIFIED SOLUTION
Avatar of GGuzdziol
GGuzdziol
Flag of Luxembourg image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer