Home » SQL & PL/SQL » SQL & PL/SQL » XML Through PL/SQL (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
XML Through PL/SQL [message #673008] |
Thu, 01 November 2018 14:15 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
Hi Experts,
I'm trying to create XML file through below and place it in a path.
DECLARE
EDI UTL_FILE.FILE_TYPE;
MYCLOB CLOB;
BEGIN
SELECT
DBMS_XMLGEN.GETXML(
('SELECT DISTINCT
NULL 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.CREATION_DATE TAX_DATE,
HDR.INVOICE_CURRENCY DOC_CURRENCY_CODE,
trunc(to_date(HDR.INVOICE_DATE,'DD-MON-YY'),'MM') START_DATE,
last_day(to_date(HDR.INVOICE_DATE,'DD-MON-RRRR')) END_DATE,
HDR.PQ_ORDER_NUMBER CONTRACT_ID,
NULL DOCUMENT_TYPE_CODE,
HDR.INVOICE_CURRENCY CURRENCY_CODE,
NULL SUPPLIER_PARTY_ID,
NULL SUPPLIER_NAME,
NULL SUPPLIER_STREET_NAME,
NULL SUPPLIERADD_STREET_NAME,
NULL SUPPLIER_CITY_NAME,
NULL SUPPLIER_POSTAL_ZONE,
NULL SUPPLIER_COUNTRY,
HDR.PQ_TAX_IDENTIFIER SUPPLIER_PARTY_TAXID,
NULL SUPPLIERPARTY_LEGALREG_NAME,
NULL SUPPLIERPARTY_LEGAL_ID,
NULL SUPPLIERPARTY_LEGAL_COUNTRY,
NULL SUPPLIER_CONTACT_ID,
NULL SUPPLIER_CONTACT_NAME,
NULL SUPPLIER_CONTACT_PHONE,
NULL SUPPLIER_CONTACT_EMAIL,
IPA.PARTY_ID CUSTOMER_PARTY_ID,
IPA.PARTY_ORGANIZATION_NAME CUSTOMER_NAME,
IPA.ADDRESS_LINE_1 CUSTOMER_STREET_NAME,
IPA.ADDRESS_LINE_2 CUSTOMER_ADD_STREET_NAME,
IPA.CITY CUSTOMER_CITY_NAME,
IPA.POSTAL_CODE CUSTOMER_POSTAL_ZONE,
IPA.COUNTRY CUSTOMER_COUNTRY_CODE,
HDR.PQ_CUSTOMER_TAX_IDENTIFIER CUSTOMER_PARTY_TAX_ID,
'VAT' CUSTOMER_TAX_SCHEME_IDENTIFIER,
IPA.PARTY_ORGANIZATION_NAME CUSTOMER_PARTY_LEGALREG_NAME,
NULL CUSTOMERPARTY_LEGAL_ID,
IPA.CITY CUSTOMER_PARTY_LEGAL_CITYNAME,
IPA.COUNTRY CUSTOMER_PARTY_LEGAL_COUNTRY,
NULL CUSTOMER_CONTACT_ID,
NULL CUSTOMER_CONTACT_NAME,
NULL CUSTOMER_CONTACT_PHONE,
NULL CUSTOMER_CONTACT_MAIL,
RACTL.TAX_RATE VAT_RATE,
NULL TAX_BASE,
HDR.PQ_TOTAL_TAX TAX,
NULL NUMIMP,
HDR.PQ_TOTAL_TAX TOTAL_VAT_AMOUNT,
HDR.INVOICE_AMOUNT SUBTOTAL_TAXABLE_AMOUNT,
HDR.PQ_TOTAL_TAX SUBTOTAL_TAX_AMOUNT,
NULL TAX_CATID,
NULL TAX_CATPERCENT,
ZXL.EXEMPT_REASON TAX_EXEPMTION,
'VAT' TAX_SCHEMEID,
OEH.TRANSACTIONAL_CURR_CODE SOURCE_CURRENCY_CODE,
RACT.INVOICE_CURRENCY_CODE TARGET_CURRENCY_CODE,
RACT.EXCHANGE_RATE CALCULATION_RATE,
'MULTIPLY' MATHEMATIC_OPERATOR_CODE,
TO_DATE(RACT.EXCHANGE_DATE,'DD-MON-RRRR')"DATE",
NULL PAYMENT_MEANS_CODE,
HDR.PAYMENT_DUE_DATE DUE_DATE,
'IBAN' PAYMENT_CHANNEL_CODE,
NULL PAYMENT_ID,
NULL PAYMENT_INSTITUTION_BRANCHID,
HDR.PAYMENT_TERMS,
IPIL.LINE_ITEM_NUMBER LINE_NUM,
IPIL.QUANTITY QUANTITY,
'EA' MEASUNIT,
(IPIL.QUANTITY * IPIL.UNIT_PRICE) LINE_AMOUNT,
IPIL.PQ_ITEM_DESCRIPTION ITEMNAME,
IPIL.ITEM_NUMBER SELLER_IS_ITEMID,--- NOT ITEM_ID BUT ITEM_NUMBER
NULL ITEM_TAXID,
'VAT' TAXSCHEME_IDENTIFIER,
'PROQUEST' MANUFACTURER_NAME,-- NEED TO VERIFY
IPIL.LINE_TOTAL PRICE_AMOUNT,
NULL PRICEALLOW_CHARGEIND,
NULL PRICEALLOW_CHARGEREASON,
NULL ACCOUNTING_COST,
HDR.INVOICE_AMOUNT TOTAL_PRICE,
IPIL.LINE_ITEM_NUMBER ORDER_LINE_REFERENCE,
HDR.INVOICE_AMOUNT LINE_EXTENSION_AMOUNT,
HDR.INVOICE_AMOUNT TAX_EXCLUSIVE_AMOUNT,
HDR.TOTAL_INVOICE_AMOUNT TAX_INCLUSIVE_AMOUNT,
(HDR.TOTAL_INVOICE_AMOUNT-HDR.AMOUNT_PAID) AMOUNT_FOR_PAYMENT,
IPC.TOTAL_AMOUNT ALLOWANCE_TOTAL_AMOUNT,
IPC.TOTAL_AMOUNT CHARGE_TOTAL_AMOUNT,
'C' ALLOWANCE_CHARGE_INDICATOR,
'FREIGHT' REASON,
HDR.INVOICE_AMOUNT AMOUNT,
NULL VAT_CATEGORY,
NULL LINE_ALLOWANCE_CHARGE_INDIC,
NULL LINE_CHARGE_REASON,
NULL LINE_CHARGE_AMOUNT,
NULL TAX_AMOUNT
FROM
PQINF.XXPQ_IP_INVOICE_HEADER HDR,
PQINF.XXPQ_IP_INVOICE_LINES IPIL,
PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA,
PQINF.XXPQ_IP_INVOICE_CHARGES IPC,
APPS.ZX_LINES_V ZXL,
APPS.RA_CUSTOMER_TRX_ALL RACT,
APPS.RA_CUSTOMER_TRX_LINES_ALL RACTL,
APPS.OE_ORDER_HEADERS_ALL OEH
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID
AND HDR.CLIENT_INVOICE_ID=IPIL.CLIENT_INVOICE_ID
AND IPA.CLIENT_INVOICE_ID=IPC.CLIENT_INVOICE_ID(+)
AND HDR.INVOICE_NUMBER=RACT.TRX_NUMBER
AND HDR.INVOICE_NUMBER=ZXL.TRX_NUMBER
AND RACT.CUSTOMER_TRX_ID=RACTL.CUSTOMER_TRX_ID
AND RACT.INTERFACE_HEADER_ATTRIBUTE1=OEH.ORDER_NUMBER
AND IPA.ADDRESS_TYPE='B'
AND RACTL.LINE_TYPE='TAX'
--AND HDR.CLIENT_INVOICE_ID='EBS-1000482'
AND HDR.INVOICE_NUMBER='61510151'
ORDER BY HDR.INVOICE_NUMBER')
INTO MYCLOB
FROM DUAL;
EDI:= UTL_FILE.FOPEN('myxml','EDICOMM.XML','w',32767);
UTL_FILE.PUT(EDI,MYCLOB);
UTL_FILE.FCLOSE(EDI);
END;
/
Encountering below error:
ORA-06550: line 9, column 2:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
Please suggest how this can be achieved or any other way I can get this select query data as xml.
Thanks for your help in Advance.
|
|
|
|
|
Re: XML Through PL/SQL [message #673020 is a reply to message #673012] |
Fri, 02 November 2018 01:53 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
Yes, you can "Doubling-Up" of Single Quotes or use an alternative quote.
Compare:
--ok
SELECT DBMS_XMLGEN.GETXML
('SELECT 2 id FROM dual')
FROM dual;
--missing right parenthesis
SELECT DBMS_XMLGEN.GETXML
('SELECT '2' id FROM dual')
FROM dual;
--ok double single quotes
SELECT DBMS_XMLGEN.GETXML
('SELECT ''N2'' id FROM dual')
FROM dual;
--alternative quote !
SELECT DBMS_XMLGEN.GETXML
(q'!SELECT 'N2' id FROM dual!')
FROM dual;
|
|
|
|
|
Re: XML Through PL/SQL [message #673033 is a reply to message #673032] |
Fri, 02 November 2018 10:42 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
There is no mystery with trunc and dates:
--ok double single quotes
SELECT DBMS_XMLGEN.GETXML
('SELECT trunc(sysdate,''MM'') td FROM dual')
FROM dual;
--ok double single quotes
SELECT DBMS_XMLGEN.GETXML
('SELECT trunc(to_date(''02-NOV-18'',''DD-MON-YY''),''MM'') td FROM dual')
FROM dual;
--alternative quote !
SELECT DBMS_XMLGEN.GETXML
(q'!SELECT trunc(sysdate,'MM') td FROM dual!')
FROM dual;
BTW. which datatype has your: HDR.INVOICE_DATE ?
Using to_date for a DATE-column would be silly...
[Updated on: Fri, 02 November 2018 10:46] Report message to a moderator
|
|
|
Re: XML Through PL/SQL [message #673035 is a reply to message #673033] |
Fri, 02 November 2018 13:10 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
_jum wrote on Fri, 02 November 2018 11:42
BTW. which datatype has your: HDR.INVOICE_DATE ?
Using to_date for a DATE-column would be silly...
Not silly, just plain wrong.
Depending on NLS_DATE format, you can get an error or incorrect results.
SCOTT@orcl SQL> alter session set nls_date_format='YY/MM/DD';
Session altered.
SCOTT@orcl SQL> select to_date(sysdate,'DD-MON-YY') from dual;
select to_date(sysdate,'DD-MON-YY') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
or
SCOTT@orcl SQL> alter session set nls_date_format='YY/MON/DD';
Session altered.
SCOTT@orcl SQL> select to_date(sysdate,'DD-MON-YY') from dual;
TO_DATE(S
---------
02/NOV/18
Here, instead of November 2nd, you get the 18th of November in either they year 2002 or 0002. You can't tell with 2 digit years, so please stop using them OP.
[Updated on: Fri, 02 November 2018 13:10] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: XML Through PL/SQL [message #673094 is a reply to message #673093] |
Mon, 05 November 2018 13:30 |
|
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can split your query text in 2 parts and set a variable like:
DECLARE
F UTL_FILE.FILE_TYPE;
MYCLOB CLOB;
S VARCHAR2(32000);
BEGIN
S := '
SELECT DISTINCT
NULL 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 IPA
WHERE 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 IPA
WHERE 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 IPA
WHERE 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 IPA
WHERE 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 IPA
WHERE 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 IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''L'') SUPPLIER_CONTACT_NAME,
(SELECT IPA.PQ_ADDR_TELEPHONE FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''L'') SUPPLIER_CONTACT_PHONE,
''CUSTOMERSERVICE@PROQUEST.COM'' SUPPLIER_CONTACT_EMAIL,
NULL CUSTOMEREND_POINTID,
NULL CUSTOMERENDPOINTID_SCHEME,
(SELECT IPA.PARTY_ID FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
where HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''B'') CUSTOMER_PARTY_ID,';
S := S || '
(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_NAME,
(SELECT IPA.ADDRESS_LINE_1 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_STREETNAME,
(SELECT IPA.ADDRESS_LINE_2 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_ADD_STREETNAME,
(SELECT IPA.CITY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_CITYNAME,
(SELECT IPA.POSTAL_CODE FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_POSTALZONE,
(SELECT IPA.COUNTRY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_COUNTRYCODE,
HDR.PQ_CUSTOMER_TAX_IDENTIFIER CUSTOMER_PARTY_TAX_ID,
''VAT'' CUSTOMER_TAX_SCHEME_IDENTIFIER,
(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_PARTY_LEGALNAME,
(SELECT IPA.PARTY_ID FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_PARTY_LEGALID,
FROM PQINF.XXPQ_IP_INVOICE_HEADER HDR
WHERE HDR.INVOICE_NUMBER=''60000593''';
SELECT DBMS_XMLGEN.GETXML(S) INTO MYCLOB FROM DUAL;
COMMIT;
F := UTL_FILE.FOPEN('DATA_PUMP_DIR','EDI_INV.xml','w',32767);
UTL_FILE.PUT(F,MYCLOB);
UTL_FILE.FCLOSE(F);
END;
/
[Updated on: Mon, 05 November 2018 13:30] Report message to a moderator
|
|
|
|
Re: XML Through PL/SQL [message #673096 is a reply to message #673095] |
Mon, 05 November 2018 14:14 |
|
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
This most likely comes from your query.
Simplify it to (for instance):
'SELECT DISTINCT
NULL 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
FROM PQINF.XXPQ_IP_INVOICE_HEADER HDR
WHERE HDR.INVOICE_NUMBER=''60000593''';
then add bit per bit until you find the error.
|
|
|
Re: XML Through PL/SQL [message #673100 is a reply to message #673096] |
Mon, 05 November 2018 15:00 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
Hi Michel,
Thanks for the Response. Added bit by bit
Till "CUSTOMER_PARTY_LEGALNAME" Field it was fine and then it threw error.Hence I did the Break as you mentioned and it threw the error again.
DECLARE
F UTL_FILE.FILE_TYPE;
MYCLOB CLOB;
S VARCHAR2(32000);
BEGIN
S := '
SELECT DISTINCT
NULL 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 IPA
WHERE 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 IPA
WHERE 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 IPA
WHERE 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 IPA
WHERE 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 IPA
WHERE 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 IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''L'') SUPPLIER_CONTACT_NAME,
(SELECT IPA.PQ_ADDR_TELEPHONE FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''L'') SUPPLIER_CONTACT_PHONE,
''CUSTOMERSERVICE@PROQUEST.COM'' SUPPLIER_CONTACT_EMAIL,
NULL CUSTOMEREND_POINTID,
NULL CUSTOMERENDPOINTID_SCHEME,
(SELECT IPA.PARTY_ID FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
where HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''B'') CUSTOMER_PARTY_ID,
(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_NAME,
(SELECT IPA.ADDRESS_LINE_1 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_STREETNAME,
(SELECT IPA.ADDRESS_LINE_2 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_ADD_STREETNAME,
(SELECT IPA.CITY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_CITYNAME,
(SELECT IPA.POSTAL_CODE FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_POSTALZONE,
(SELECT IPA.COUNTRY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_COUNTRYCODE,
HDR.PQ_CUSTOMER_TAX_IDENTIFIER CUSTOMER_PARTY_TAX_ID,
''VAT'' CUSTOMER_TAX_SCHEME_IDENTIFIER,
(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_PARTY_LEGALNAME,';
S := S || '
(SELECT IPA.PARTY_ID FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_PARTY_LEGALID
FROM PQINF.XXPQ_IP_INVOICE_HEADER HDR
WHERE HDR.INVOICE_NUMBER=''60000593''';
SELECT DBMS_XMLGEN.GETXML(S) INTO MYCLOB FROM DUAL;
COMMIT;
F := UTL_FILE.FOPEN('DATA_PUMP_DIR','EDI_INV.xml','w',32767);
UTL_FILE.PUT(F,MYCLOB);
UTL_FILE.FCLOSE(F);
END;
/
|
|
|
Re: XML Through PL/SQL [message #673102 is a reply to message #673100] |
Tue, 06 November 2018 01:05 |
|
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I can't help more.
You have a clear test case, so you can ask Oracle why this error.
Maybe even if a SQL with more than 4K characters is syntactically valid, it is not supported by the function.
Try creating a view with the query (without the WHERE clause) and call the function with the view (adding the WHERE clause).
[Updated on: Fri, 09 November 2018 10:43] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Thu May 23 09:44:59 CDT 2024
|