sqloracleplsqlprocedure

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


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.


Solution

  • 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