PL/SQL (MOSC)

MOSC Banner

Creating XML from Select is throwing Error

edited Nov 29, 2018 2:58PM in PL/SQL (MOSC) 8 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center