| PL/SQL XML Date Time omits Time part [message #556475] |
Mon, 04 June 2012 08:54  |
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
|
Member |
|
|
Hi There,
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format.
The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
table(xmlsequence(extract(x.xml_message, '/RECORD'))) d;
BEGIN
OPEN c_xml_record;
FETCH c_xml_record INTO
p_omful_rec.activation_ts --- omits timepart
CLOSE c_xml_record;
Please help
Regs
|
|
|
|
|
|
| Re: PL/SQL XML Date Time omits Time part [message #556479 is a reply to message #556476] |
Mon, 04 June 2012 09:43  |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
extract(value(d), '//ACTIVATIONTS/text()').getStringVal() must fail because the node in your little XML is //REGTIMESTAMP.
Try:
WITH xdata AS
(SELECT XMLType(
'<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>') xd FROM dual)
SELECT extract(value(d), '//REGTIMESTAMP/text()').getStringVal() AS REGTIMESTAMP
FROM xdata x,
TABLE(xmlsequence(extract(x.xd, '/RECORD'))) d;
REGTIMESTAMP
--------------------------------------------------------------------------------
20120601130010
1 row selected.
[Updated on: Mon, 04 June 2012 09:44] Report message to a moderator |
|
|
|
| Re: PL/SQL XML Date Time omits Time part [message #556844 is a reply to message #556475] |
Wed, 06 June 2012 23:32 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You haven't shown where or how you are using to_date or demonstrated the failure. The 20120601130010 is a character string and must be either be fetched into a character data type like varchar2 or rely on implicit conversion, which can cause problems. Then you can apply to_date to it. Then you can use to_char to display the stored date and time in any format you want, with or without the time. When you do not specify a date format, you get whatever the default nls_date_format is currently set to. Please see the demonstration below, noting the different code that produces the different date formats.
-- test data:
SCOTT@orcl_11gR2> create table t_xml_data
2 (xml_message xmltype)
3 /
Table created.
SCOTT@orcl_11gR2> insert into t_xml_data values (xmltype (
2 '<?xml version="1.0"?>
3 <RECORD>
4 <REGTIMESTAMP>20120601130010</REGTIMESTAMP>
5 </RECORD>'))
6 /
1 row created.
SCOTT@orcl_11gR2> select * from t_xml_data
2 /
XML_MESSAGE
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
1 row selected.
-- SQL with default date format:
SCOTT@orcl_11gR2> SELECT TO_DATE
2 (extract(value(d), '//REGTIMESTAMP/text()').getStringVal(),
3 'YYYYMMDDHH24MISS') AS REGTIMESTAMP
4 FROM t_xml_data x,
5 table(xmlsequence(extract(x.xml_message, '/RECORD'))) d
6 /
REGTIMEST
---------
01-JUN-12
1 row selected.
-- SQL using to_char to specify date format:
SCOTT@orcl_11gR2> SELECT TO_CHAR
2 (TO_DATE
3 (extract(value(d), '//REGTIMESTAMP/text()').getStringVal(),
4 'YYYYMMDDHH24MISS'),
5 'fmDay fmDD-Mon-YYYY HH24:MI:SS') AS REGTIMESTAMP
6 FROM t_xml_data x,
7 table(xmlsequence(extract(x.xml_message, '/RECORD'))) d
8 /
REGTIMESTAMP
------------------------------------------------------------------
Friday 01-Jun-2012 13:00:10
1 row selected.
-- PL/SQL showing first deafult, then explicit date format:
SCOTT@orcl_11gR2> DECLARE
2 CURSOR c_xml_record
3 IS
4 SELECT extract(value(d), '//REGTIMESTAMP/text()').getStringVal() AS REGTIMESTAMP
5 FROM t_xml_data x,
6 table(xmlsequence(extract(x.xml_message, '/RECORD'))) d;
7 regtimestamp VARCHAR2(14);
8 activation_ts DATE;
9 BEGIN
10 OPEN c_xml_record;
11 LOOP
12 FETCH c_xml_record INTO regtimestamp;
13 EXIT WHEN c_xml_record%NOTFOUND;
14 activation_ts := TO_DATE (regtimestamp, 'YYYYMMDDHH24MISS');
15 DBMS_OUTPUT.PUT_LINE (activation_ts);
16 DBMS_OUTPUT.PUT_LINE (TO_CHAR (activation_ts, 'fmDay fmDD-Mon-YYYY HH24:MI:SS'));
17 END LOOP;
18 CLOSE c_xml_record;
19 END;
20 /
01-JUN-12
Friday 01-Jun-2012 13:00:10
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
