Creating XML from Select is throwing Error
Hi All,
I'm trying to Create XML Based on Select Query.
Till the Field -CUSTOMER_PARTY_LEGALNAME it is fine but Once I add one more column CUSTOMER_PARTY_LEGALID it is throwing below error
ORA-06550: line 7, column 24:
PL/SQL: ORA-01704: string literal too long
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
Please suggest if there is any way to over come this. I have more fields to add in the query.
DECLAREF UTL_FILE.FILE_TYPE;MYCLOB VARCHAR2(32000);BEGINSELECT DBMS_XMLGEN.GETXML('SELECT DISTINCTNULL SUPPLIER_END_POINT_ID,''N0974767880'' CUSTOMER_END_POINT_ID,HDR.INVOICE_NUMBER NUM_DOC,HDR.PO_NUMBER ORDER_ID,HDR.INVOICE_DATE ISSUE_DATE,HDR.DOCUMENT_SUBTYPE INVOICE_TYPE_CODE,HDR.COMMENTS NOTE,HDR.INVOICE_DATE TAX_DATE,HDR.INVOICE_CURRENCY DOC_CURRENCY_CODE,HDR.PQ_ORDER_NUMBER CONTRACT_ID,NULL DOCUMENT_TYPE_CODE,HDR.INVOICE_CURRENCY CURRENCY_CODE,HDR.PQ_TAX_IDENTIFIER SUPPLIER_PARTY_ID,(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'')SUPPLIER_NAME,(SELECT IPA.ADDRESS_LINE_1 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIER_STREET_NAME,(SELECT IPA.ADDRESS_LINE_2 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIERADD_STREET_NAME,(SELECT IPA.CITY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPAWHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIER_CITY_NAME,(SELECT IPA.POSTAL_CODE FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPAWHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIER_POSTAL_ZONE,(SELECT IPA.COUNTRY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPAWHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIER_COUNTRY,HDR.PQ_TAX_IDENTIFIER SUPPLIER_PARTY_TAXID,NULL SUPPLIERENDPOINTID,(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIERPARTY_LEGALREG_NAME,HDR.PQ_TAX_IDENTIFIER SUPPLIERPARTY_LEGAL_ID,(SELECT IPA.COUNTRY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPAWHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIERPARTY_LEGAL_COUNTRY,(SELECT IPA.PQ_ADDR_ATTN FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPAWHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''L'') SUPPLIER_CONTACT_ID,(SELECT IPA.PQ_ADDR_ATTN FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPAWHERE