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 Go to next message
vharish006
Messages: 54
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 #673010 is a reply to message #673008] Thu, 01 November 2018 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First count your parentheses.

Re: XML Through PL/SQL [message #673012 is a reply to message #673010] Thu, 01 November 2018 15:59 Go to previous messageGo to next message
vharish006
Messages: 54
Registered: August 2015
Location: Chicago
Member
I think We need to put the Select Query between '' but as there are some hardcoded values in query which seems is the issue.
Re: XML Through PL/SQL [message #673020 is a reply to message #673012] Fri, 02 November 2018 01:53 Go to previous messageGo to next message
_jum
Messages: 533
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 #673023 is a reply to message #673020] Fri, 02 November 2018 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 65946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

--... and
--missing right parenthesis
SELECT DBMS_XMLGEN.GETXML(
 ('SELECT 2 id FROM dual') 
  FROM dual;
Re: XML Through PL/SQL [message #673032 is a reply to message #673020] Fri, 02 November 2018 09:54 Go to previous messageGo to next message
vharish006
Messages: 54
Registered: August 2015
Location: Chicago
Member
Hi Jum,

Thanks. Using the Double Single Quotes is working Smile

But I have 2 date columns in my select query where I'm pulling start date of the month and Last Date of the month like below
how can I accomodate below as they alreay have quote and trying to put double quotes is still throwing the parenthesis again.

Can you please suggest on this

trunc(to_date(HDR.INVOICE_DATE,'DD-MON-YY'),'MM') START_DATE,
last_day(to_date(HDR.INVOICE_DATE,'DD-MON-RRRR')) END_DATE,
Re: XML Through PL/SQL [message #673033 is a reply to message #673032] Fri, 02 November 2018 10:42 Go to previous messageGo to next message
_jum
Messages: 533
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 Go to previous messageGo to next message
joy_division
Messages: 4908
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 #673040 is a reply to message #673033] Fri, 02 November 2018 15:24 Go to previous messageGo to next message
vharish006
Messages: 54
Registered: August 2015
Location: Chicago
Member
Thanks for your help Jum.
Re: XML Through PL/SQL [message #673041 is a reply to message #673035] Fri, 02 November 2018 15:26 Go to previous messageGo to next message
vharish006
Messages: 54
Registered: August 2015
Location: Chicago
Member
Thanks for the Suggestion @joy_division
Re: XML Through PL/SQL [message #673048 is a reply to message #673033] Sat, 03 November 2018 11:23 Go to previous messageGo to next message
vharish006
Messages: 54
Registered: August 2015
Location: Chicago
Member
Hi Jum,

I have a huge Select Query consisting around 90 columns for which I'm trying to Create XML but after a certain addition of Fields it is now throwing

ORA-06550: line 6, column 20:
PL/SQL: ORA-01704: string literal too long
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored


Looks like there is a limitation .I created a View from the Select and used it but still looking if I can use the query itself

Is there any way I can over come this limitation to include all the required fields??

Re: XML Through PL/SQL [message #673049 is a reply to message #673048] Sat, 03 November 2018 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe, it depends where the error comes from.
It can't come from DBMS_XMLGEN.GETXML as this one returns a CLOB.
Maybe it comes from the query you pass to this function which is limited to 32K.
Anyway you have to show us.

Re: XML Through PL/SQL [message #673092 is a reply to message #673049] Mon, 05 November 2018 13:03 Go to previous messageGo to next message
vharish006
Messages: 54
Registered: August 2015
Location: Chicago
Member
Hi Michel,

I know you will not be able run this query but still attaching it to show you as this is huge query.

[Updated on: Mon, 05 November 2018 13:04]

Report message to a moderator

Re: XML Through PL/SQL [message #673093 is a reply to message #673092] Mon, 05 November 2018 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
single quoted string can NOT exceed 4000 characters.
Re: XML Through PL/SQL [message #673094 is a reply to message #673093] Mon, 05 November 2018 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65946
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 #673095 is a reply to message #673094] Mon, 05 November 2018 14:10 Go to previous messageGo to next message
vharish006
Messages: 54
Registered: August 2015
Location: Chicago
Member
Hi Michel,

Thanks for quick Response. This however gives below error probably due to Conversion I guess

ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 74
Re: XML Through PL/SQL [message #673096 is a reply to message #673095] Mon, 05 November 2018 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65946
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 Go to previous messageGo to next message
vharish006
Messages: 54
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. Sad

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 Go to previous messageGo to next message
Michel Cadot
Messages: 65946
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

Re: XML Through PL/SQL [message #673168 is a reply to message #673102] Fri, 09 November 2018 10:44 Go to previous message
Michel Cadot
Messages: 65946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any news about this problem?

Previous Topic: List of Months between 2 dates
Next Topic: Last and Sum in group by
Goto Forum:
  


Current Time: Tue Nov 13 22:24:30 CST 2018