Home » SQL & PL/SQL » SQL & PL/SQL » Converting xmltype to varchar2(merged)
|
|
|
|
| Re: Converting xmltype to varchar2(merged) [message #402035 is a reply to message #402033] |
Thu, 07 May 2009 06:42   |
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 #402039 is a reply to message #402037] |
Thu, 07 May 2009 06:53 |
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 #402041 is a reply to message #402040] |
Thu, 07 May 2009 07:04 |
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 #402059 is a reply to message #402022] |
Thu, 07 May 2009 07:55 |
cookiemonster
Messages: 13966
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 |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 25 13:03:41 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
