Home » SQL & PL/SQL » SQL & PL/SQL » Converting xmltype to varchar2(merged)
Converting xmltype to varchar2(merged) [message #402022] Thu, 07 May 2009 06:17 Go to next message
gmakinana
Messages: 10
Registered: May 2009
Location: South Africana
Junior Member

Hi there SQL-Masters...

I have written an oracle form to create an xmlfile to the local machine of a user using client_text_io... But as I gathered oracle form does not understand xml commands like xmltype datatype... So I resorted into creating a database stored proc to generate an xmltype and then used ...
HERE is the proc...The proc returns trancated data back...
----------------------------------------
DECLARE
xmltypefile XMLTYPE;
v_e clob;
BEGIN
OPEN mainc (from_dt, to_dt, user_cd);

FETCH mainc
INTO xmltypefile;

CLOSE mainc;

xoutput := XMLTYPE.getclobval (xmltypefile);
v_e := XMLTYPE.getclobval (xmltypefile);

insert into test values (33, v_e); commit;
EXCEPTION
WHEN OTHERS
THEN
xerr := 'ERROR : ' || SQLERRM;
END;
Re: Converting xmltype to varchar2(merged) [message #402033 is a reply to message #402022] Thu, 07 May 2009 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The proc returns trancated data back...

I think you meant in xoutput variable, how is it declared?

Regards
Michel
Re: Converting xmltype to varchar2(merged) [message #402035 is a reply to message #402033] Thu, 07 May 2009 06:42 Go to previous messageGo to next message
gmakinana
Messages: 10
Registered: May 2009
Location: South Africana
Junior Member

I have attached the proc,below....

CREATE OR REPLACE PROCEDURE CAI.gen_client_trans_xml (
from_dt IN DATE,
to_dt IN DATE,
user_cd IN VARCHAR2,
xoutput OUT clob,
xerr OUT VARCHAR2
)
IS
CURSOR mainc (fdate IN DATE, tdate IN DATE, usrcd IN VARCHAR2)
IS
SELECT XMLELEMENT
("ICM4032",
XMLELEMENT
("LIST_G_VALUEDATE",
XMLAGG
(XMLELEMENT
("G_VALUEDATE",
XMLFOREST
(valuedate AS valuedate,
transactionrefno AS transactionrefno,
fincurr AS fincurr,
finamount AS finamount,
beneficiary_name AS beneficiary_name,
pymtcurr AS pymtcurr,
usdamt AS usdamt,
randamt AS randamt,
bopcat1 AS bopcat1,
bopreason1 AS bopreason1,
bopamount1 AS bopamount1,
bop_exchangectrl1 AS bop_exchangectrl1,
bopexcondate1 AS bopexcondate1,
bopcat2 AS bopcat2,
bopreason2 AS bopreason2,
bopamount2 AS bopamount2,
bopexchangectrl2 AS bopexchangectrl2,
bopexcondate2 AS bopexcondate2
)
)
)
)
) AS "ICM4032_LIST"
FROM (SELECT d.value_date AS valuedate,
d.transaction_reference_number AS transactionrefno,
d.finance_currency AS fincurr,
d.finance_amount AS finamount,
m.ben_cust_name AS beneficiary_name,
i.payment_currency AS pymtcurr, d.usd_amount usdamt,
d.finance_amount randamt, i.bop_cat_1 bopcat1,
DECODE
(i.bop_cat_1,
NULL, NULL,
(SELECT c.bop_desc
FROM ci_bop_cat c
WHERE TO_CHAR (i.bop_cat_1) = c.bop_c
AND c.sale_purch_i = 'S')
) AS bopreason1,
i.bop_amt_1 AS bopamount1,
i.bop_excon_1 AS bop_exchangectrl1,
i.bop_excon_date_1 bopexcondate1, i.bop_cat_2 bopcat2,
DECODE (i.bop_cat_2,
NULL, NULL,
(SELECT c.bop_desc
FROM ci_bop_cat c
WHERE TO_CHAR (i.bop_cat_2) = c.bop_c
AND c.sale_purch_i = 'S')
) bopreason2,
i.bop_amt_2 bopamount2, i.bop_excon_2 bopexchangectrl2,
i.bop_excon_date_2 bopexcondate2
FROM icm_zar_deals d,
icm_zar_deal_instructions i,
ci_bop_cat c,
icm_zar_mt100 m
WHERE d.deal_number = i.deal_number
AND d.deal_number = m.deal_number
AND d.user_code = UPPER (usrcd) -- 'THE35'--
AND d.status_type IN ('WS', 'C4', 'CF', 'FC')
AND d.value_date BETWEEN TO_DATE (fdate, 'DD/MM/YYYY')
AND TO_DATE (tdate, 'DD/MM/YYYY')
-- '01-May-2008' and '31-May-2008' --
AND TO_CHAR (i.bop_cat_1) = c.bop_c
AND c.sale_purch_i = 'S'
UNION
SELECT dd.value_date, dd.transaction_reference_number,
dd.finance_currency, dd.finance_amount, mm.f59_line2,
ii.payment_currency, dd.usd_amount, dd.zar_amount,
ii.bop_cat_1,
DECODE (ii.bop_cat_1,
NULL, NULL,
(SELECT cc.bop_desc
FROM ci_bop_cat cc
WHERE TO_CHAR (ii.bop_cat_1) = cc.bop_c
AND cc.sale_purch_i = 'S')
),
ii.bop_amt_1, ii.bop_excon_1, ii.bop_excon_date_1,
ii.bop_cat_2,
DECODE (ii.bop_cat_2,
NULL, NULL,
(SELECT cc.bop_desc
FROM ci_bop_cat cc
WHERE TO_CHAR (ii.bop_cat_2) = cc.bop_c
AND cc.sale_purch_i = 'S')
),
ii.bop_amt_2, ii.bop_excon_2, ii.bop_excon_date_2
FROM icm_fn_cfca_deals dd,
icm_fn_cfca_deal_instructions ii,
ci_bop_cat cc,
icm_fn_cfca_mt103 mm
WHERE dd.deal_number = ii.deal_number
AND dd.deal_number = mm.deal_number
AND dd.user_code = UPPER (usrcd) --'THE35' --
AND dd.status_type IN ('WS', 'C4', 'CF')
AND dd.value_date BETWEEN TO_DATE (fdate, 'DD/MM/YYYY')
AND TO_DATE (tdate, 'DD/MM/YYYY')
--'01-May-2008' and '31-May-2008' --
AND TO_CHAR (ii.bop_cat_1) = cc.bop_c
AND cc.sale_purch_i = 'S'
ORDER BY valuedate);

---------------------------------------------------------------------------------
--Program Parameters
---------------------------------------------------------------------------------
xmltypefile XMLTYPE;
BEGIN
OPEN mainc (from_dt, to_dt, user_cd);

FETCH mainc
INTO xmltypefile;

CLOSE mainc;

xoutput := XMLTYPE.getclobval (xmltypefile);

EXCEPTION
WHEN OTHERS
THEN
xerr := 'ERROR : ' || SQLERRM;
END;
/
Re: Converting xmltype to varchar2(merged) [message #402037 is a reply to message #402035] Thu, 07 May 2009 06:47 Go to previous messageGo to next message
gmakinana
Messages: 10
Registered: May 2009
Location: South Africana
Junior Member

This is the output I am getting
================

<ICM4032>
<LIST_G_VALUEDATE>
<G_VALUEDATE></G_VALUEDATE>
</LIST_G_VALUEDATE>
</ICM4032>
===============
When I run the query I get this....This is what am suppose to be getting back
===============
<ICM4032>
<LIST_G_VALUEDATE>
<G_VALUEDATE>
<VALUEDATE>09-APR-09</VALUEDATE>
<TRANSACTIONREFNO>090409IC00448266</TRANSACTIONREFNO>
<FINCURR>USD</FINCURR>
<FINAMOUNT>1</FINAMOUNT>
<BENEFICIARY_NAME>EDWIN SONG</BENEFICIARY_NAME>
<PYMTCURR>USD</PYMTCURR>
<USDAMT>1</USDAMT>
<RANDAMT>9.13</RANDAMT>
<BOPCAT1>901</BOPCAT1>
<BOPREASON1>OTHER - DETAILS OF PAYMENTS NOT CLASSIFIED ABOVE</BOPREASON1>
<BOPAMOUNT1>1</BOPAMOUNT1>
</G_VALUEDATE>
<G_VALUEDATE>
<VALUEDATE>09-APR-09</VALUEDATE>
<TRANSACTIONREFNO>090409IC00448268</TRANSACTIONREFNO>
<FINCURR>USD</FINCURR>
<FINAMOUNT>1</FINAMOUNT>
<BENEFICIARY_NAME>EDWIN SONG</BENEFICIARY_NAME>
<PYMTCURR>USD</PYMTCURR>
<USDAMT>1</USDAMT>
<RANDAMT>9.13</RANDAMT>
<BOPCAT1>901</BOPCAT1>
<BOPREASON1>OTHER - DETAILS OF PAYMENTS NOT CLASSIFIED ABOVE</BOPREASON1>
<BOPAMOUNT1>1</BOPAMOUNT1>
</G_VALUEDATE>
<G_VALUEDATE>
<VALUEDATE>16-APR-09</VALUEDATE>
<TRANSACTIONREFNO>090416IC00448316</TRANSACTIONREFNO>
<FINCURR>USD</FINCURR>
<FINAMOUNT>1</FINAMOUNT>
<BENEFICIARY_NAME>EDWIN SONG</BENEFICIARY_NAME>
<PYMTCURR>USD</PYMTCURR>
<USDAMT>1</USDAMT>
<RANDAMT>9.03</RANDAMT>
<BOPCAT1>901</BOPCAT1>
<BOPREASON1>OTHER - DETAILS OF PAYMENTS NOT CLASSIFIED ABOVE</BOPREASON1>
<BOPAMOUNT1>1</BOPAMOUNT1>
</G_VALUEDATE>
<G_VALUEDATE>
<VALUEDATE>16-APR-09</VALUEDATE>
<TRANSACTIONREFNO>090416IC00448319</TRANSACTIONREFNO>
<FINCURR>USD</FINCURR>
<FINAMOUNT>1</FINAMOUNT>
<BENEFICIARY_NAME>EDWIN SONG</BENEFICIARY_NAME>
<PYMTCURR>USD</PYMTCURR>
<USDAMT>1</USDAMT>
<RANDAMT>9.03</RANDAMT>
<BOPCAT1>901</BOPCAT1>
<BOPREASON1>OTHER - DETAILS OF PAYMENTS NOT CLASSIFIED ABOVE</BOPREASON1>
<BOPAMOUNT1>1</BOPAMOUNT1>
</G_VALUEDATE>
</LIST_G_VALUEDATE>
</ICM4032>
Re: Converting xmltype to varchar2(merged) [message #402039 is a reply to message #402037] Thu, 07 May 2009 06:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

CREATE OR REPLACE PROCEDURE CAI.gen_client_trans_xml (
from_dt IN DATE,
to_dt IN DATE,
user_cd IN VARCHAR2,
xoutput OUT clob,
xerr OUT VARCHAR2
)
IS
CURSOR mainc (fdate IN DATE, tdate IN DATE, usrcd IN VARCHAR2)
IS
....
AND dd.value_date BETWEEN TO_DATE (fdate, 'DD/MM/YYYY')
AND TO_DATE (tdate, 'DD/MM/YYYY')


After reading this I give up.

If you don't mind could you please read the guidelines how to format your post and please align your code otherwise it is extremely difficult to read.

Regards

Raj
Re: Converting xmltype to varchar2(merged) [message #402040 is a reply to message #402039] Thu, 07 May 2009 06:58 Go to previous messageGo to next message
gmakinana
Messages: 10
Registered: May 2009
Location: South Africana
Junior Member

Hi there,
I have attached a formatted code...sorry for that!
Re: Converting xmltype to varchar2(merged) [message #402041 is a reply to message #402040] Thu, 07 May 2009 07:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You don't need to apologise for it. If you read the forum guidelines you will understand how to format your code inline. Some of us will be relucant to download file from public forums because of various reasons. This is my best guess for the cause of your problem. Execute the following code and try to fix your original query based on that.
select * from dual where trunc(sysdate) = trunc(sysdate);

select * from dual where trunc(sysdate) = to_date(trunc(sysdate),'DD/MM/YYYY');

alter session set nls_dateformat = 'DD/MM/YYYY';

select * from dual where trunc(sysdate) = to_date(trunc(sysdate),'DD/MM/YYYY');


Regards

Raj

P.S : I assume your nls_date_format is not 'DD/MM/YYYY'.
Re: Converting xmltype to varchar2(merged) [message #402047 is a reply to message #402041] Thu, 07 May 2009 07:22 Go to previous messageGo to next message
gmakinana
Messages: 10
Registered: May 2009
Location: South Africana
Junior Member

My problem here is converting the xmltype data to clob or varchar so that I can write it out as a xml document on the form
Re: Converting xmltype to varchar2(merged) [message #402059 is a reply to message #402022] Thu, 07 May 2009 07:55 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Judging from your sample output I'd say that your problem is that when you run the procedure from oracle forms the select statement fails to find any records.

In which case S.Rajaram's diagnosis is very likely correct, and even if it isn't to_dateing a date is always a bad idea and you should fix it.
Re: Converting xmltype to varchar2(merged) [message #402060 is a reply to message #402047] Thu, 07 May 2009 07:58 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Are you sure that you XMLFOREST column generate all columns given between

<G_VALUEDATE></G_VALUEDATE>.


Because there is no truncation of XML data, your output is Ok as it contains a valid XML structure. The only missing is the data is generated for <G_VALUEDATE></G_VALUEDATE>.

<ICM4032>
<LIST_G_VALUEDATE>
<G_VALUEDATE> </G_VALUEDATE>
</LIST_G_VALUEDATE>
</ICM4032>


Can you just run your query on SQLPLUS and see what your query is returning.

Thanks
Trivendra
Re: Converting xmltype to varchar2(merged) [message #402063 is a reply to message #402060] Thu, 07 May 2009 08:08 Go to previous message
gmakinana
Messages: 10
Registered: May 2009
Location: South Africana
Junior Member

Hi Masters...
The problem is resolved S.Rajaram's and cookiemonster was right the problem was the date..... And now that I get the data as varchar2 I use client_text_io to write the clob xml to the c:\drive\file.xml.... But when I open the xml file the formatting has been lost is there a way to save the text file in a hierarchical xml format...?
Previous Topic: how to pass multiple values to another procedure
Next Topic: [Help] Partially overlapped time calculation
Goto Forum:
  


Current Time: Sun Dec 04 12:34:39 CST 2016

Total time taken to generate the page: 0.15083 seconds