Oracle PL/SQL procedure - To return CLOB variable with XML data as OUT parameter

Question

I have the following data in XML format

<OutputParameters xmlns=http://xmlns.oracle.com/cloud/adapter/ xmlns:xsi=http://www.test.org/1998/XMLSchema-instance>
  <Header>
   <Header_item>
      <TRANSACTION_NUMBER>YSCPQ_9876</SOURCE_TRANSACTION_NUMBER>
      <TRANSACTION_SYSTEM>OPS</SOURCE_TRANSACTION_SYSTEM>
   <Lines>
    <lines_item>
      <TRANSACTION_ID>YSCPQ_9876</SOURCE_TRANSACTION_ID>
      <TRANSACTION_LINEID>1</SOURCE_TRANSACTION_LINEID>
    </lines_item>
    <lines_item>
      <TRANSACTION_ID>YSCPQ_9876</SOURCE_TRANSACTION_ID>
      <TRANSACTION_LINEID>2</SOURCE_TRANSACTION_LINEID>
    </lines_item>
   </Lines>
  </Header_item>
 </Header>     
</OutputParameters>

I need a procedure to store this XML output in a CLOB variable and return it as OUT parameter.

Any help would be much appreciated.

Answer 1

Declare a procedure with an OUT variable and assign the XML to it in the body of the procedure:

CREATE PROCEDURE generate_data(
  o_data OUT CLOB
)
IS
BEGIN
  o_data := '<OutputParameters xmlns=http://xmlns.oracle.com/cloud/adapter/ xmlns:xsi=http://www.test.org/1998/XMLSchema-instance>
  <Header>
   <Header_item>
      <TRANSACTION_NUMBER>YSCPQ_9876</SOURCE_TRANSACTION_NUMBER>
      <TRANSACTION_SYSTEM>OPS</SOURCE_TRANSACTION_SYSTEM>
   <Lines>
    <lines_item>
      <TRANSACTION_ID>YSCPQ_9876</SOURCE_TRANSACTION_ID>
      <TRANSACTION_LINEID>1</SOURCE_TRANSACTION_LINEID>
    </lines_item>
    <lines_item>
      <TRANSACTION_ID>YSCPQ_9876</SOURCE_TRANSACTION_ID>
      <TRANSACTION_LINEID>2</SOURCE_TRANSACTION_LINEID>
    </lines_item>
   </Lines>
  </Header_item>
 </Header>     
</OutputParameters>';
END;
/

fiddle