HI EveryOne,

I'm new be to pl/sql. I have a functionality like, when i read the data from multiple tables as one xml file(each record as one xml), i have to update in other table.For that i have written procedure in that procedure i'm calling function which returns xml file and calling other stored procedure to update the table. Every thing is working fine. but when i'm executing this procedure i'm not getting the xml result.
Can you please suggest how can i get the result as xml file and need to update a table from procedure.

create or replace PROCEDURE USP_GET_VENDORS(result out clob)
is
begin
    select GET_TRINVENDORS_V1234 INTO result from dual;
    if result IS NOT NULL then
    UPD_Interface_Control_V1('71627654');
    end if;
    return;
end;

create or replace FUNCTION GET_TRINVENDORS_V1234
RETURN CLOB 
IS
BatchNo TRIN_INTERFACE_CONTROL.BATCH_NUMBER%TYPE;
retval CLOB;
BEGIN SELECT xmlagg(xmlelement("DataArea", 
                xmlelement("TrinVendor", 
               xmlforest(BATCH_NO as "BatchNo",
                LSI as "Lsi", 
                VENDOR_NAME as "VendorName",
                VENDOR_NAME_ALT as "VendorNameAlt",
                SEGMENT1 as "Segment1",
                VENDOR_TYPE_LOOKUP_CODE as "VendorTypeLookupCode",
                ONE_TIME_FLAG as "OneTimeFlag",
                NUM_1099 as "Num1099",
                TYPE_1099 as "Type1099",
                HOLD_FLAG as "HoldFlag",
                PURCHASING_HOLD_REASON as "PurchasingHoldReason",
                HOLD_DATE as "HoldDate",
                QTY_RCV_TOLERANCE as "QtyRcvTolerance",
                DAYS_EARLY_RECEIPT_ALLOWED as "DaysEarlyReceiptAllowed",
                DAYS_LATE_RECEIPT_ALLOWED as "DaysLateReceiptAllowed",
                STATE_REPORTABLE_FLAG as "StateReportableFlag",
                FEDERAL_REPORTABLE_FLAG as "FederalReportableFlag",
                VENDOR_SITE_CODE as "VendorSiteCode",
                PURCHASING_SITE_FLAG as "PurchasingSiteFlag",
                PAY_SITE_FLAG as "PaySiteFlag",
                ADDRESS_LINE1 as "AddressLine1",
                ADDRESS_LINE2 as "AddressLine2",
                ADDRESS_LINE3 as "AddressLine3",
                ADDRESS_LINE4 as "AddressLine4",
                CITY as "City",
                STATE as "State",
                ZIP as "Zip",
                COUNTRY as "Country",
                SHIP_VIA_LOOKUP_CODE as "ShipViaLookupCode",
                FREIGHT_TERMS_LOOKUP_CODE as "FreightTermsLookupCode",
                FOB_LOOKUP_CODE as "FobLookupCode",
                ENABLED_FLAG as "EnabledFlag",
                FAX_AREA_CODE as "FaxAreaCode",
                FAX as "Fax",
                PAYMENT_METHOD_LOOKUP_CODE as "PaymentMethodLookupCode",
                BANK_ACCOUNT_NAME as "BankAccountName",
                BANK_ACCOUNT_NUM as "BankAccountNum",
                BANK_OR_BRANCH_NUMBER as "BankOrBranchName",
                PAYMENT_PRIORITY as "PaymentPriority",
                NAME as "Name",
                INVOICE_CURRENCY_CODE as "InvoiceCurrencyCode",
                PAYMENT_CURRENCY_CODE as "PaymentCurrencyCode",
                EMAIL_ADDRESS as "EmailAddress",
                PERSON_FIRST_NAME as "PersonFirstName",
                PERSON_MIDDLE_NAME as "PersonMiddleName",
                PRIMARY_PHONE_AREA_CODE as "PrimaryPhoneAreaCode",
    TAX_EXEMPT_NUM as "TaxExemptNum"
                ))as DATAAREA)) .getClobVal()AS xml_frag INTO retval FROM trin_vendor_outbound WHERE BATCH_NO = '71627654';
                DBMS_OUTPUT.put_line(retval);
                RETURN retval;
               /*  EXEC UPD_TRININTERFACECONTROL_V1(BatchNo); */
                END;

SQL> create or replace FUNCTION GET_TRINVENDORS_V1234 RETURN CLOB  IS
  2    retval CLOB;
  3  BEGIN
  4  with trin_vendor_outbound as
  5   ( select 123 batch_no, 'lll' lsi, 'vn' as vendor_name, 's1' as segment1, 1099 as num_1099 from dual )
  6  SELECT xmlagg(xmlelement("DataArea",
  7                  xmlelement("TrinVendor",
  8                 xmlforest(BATCH_NO as "BatchNo",
  9                  LSI as "Lsi",
 10                  VENDOR_NAME as "VendorName",
 11                  SEGMENT1 as "Segment1",
 12                  NUM_1099 as "Num1099"
 13                  ))as DATAAREA)) .getClobVal()AS xml_frag INTO retval FROM trin_vendor_outbound;
 14                  DBMS_OUTPUT.put_line(retval);
 15  RETURN retval;
 16  END;
 17  /

Function created.

SQL>
SQL>
SQL> create or replace PROCEDURE USP_GET_VENDORS(result out clob) is
  2  begin
  3      select GET_TRINVENDORS_V1234 INTO result from dual;
  4  end;
  5  /

Procedure created.

SQL>
SQL> variable c clob
SQL> exec USP_GET_VENDORS(:c);

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------------------------------------------------------
<DataArea><TrinVendor><BatchNo>123</BatchNo><Lsi>lll</Lsi><VendorName>vn</Vendor

SQL>
SQL>