Home » SQL & PL/SQL » SQL & PL/SQL » Need help in converting LOB to timestamp (Oracle 10G)
Need help in converting LOB to timestamp [message #388262] Tue, 24 February 2009 01:22 Go to next message
rnhullur
Messages: 3
Registered: February 2009
Location: Mumbai
Junior Member
Hi all,
I am having a problem in converting a LOB ( which will have date value) to timestamp.
My LOB value will be in the format "(CLOB) 19/Feb/09 07:25 PM".
And I need to convert it into timestamp format with format 'dd-MON-yy HH:MI:SS AM'.
When I convert the LOB to char using to_char(dbms_lob.substr(oldstring, 18, 1)) where oldstring is the column name, I will get the value in the format '19/Feb/09 07:25 PM'. But after that I was not able to convert it into timestamp as I am getting the message 'ORA-01858' related to non-numeric character when it is expecting a numeric character.

Does anyone had this problem or is anyone having solution to this?

Thanks in advance..

-Raghu
Re: Need help in converting LOB to timestamp [message #388265 is a reply to message #388262] Tue, 24 February 2009 01:28 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
@Rnhullur,
Hi check this link.
http://www.orafaq.com/wiki/ORA-01858



Regards,
Hammer
Re: Need help in converting LOB to timestamp [message #388268 is a reply to message #388262] Tue, 24 February 2009 01:34 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
rnhullur wrote on Tue, 24 February 2009 08:22

My LOB value will be in the format "(CLOB) 19/Feb/09 07:25 PM".



rnhullur wrote on Tue, 24 February 2009 08:22

And I need to convert it into timestamp format with format 'dd-MON-yy HH:MI:SS AM'.


Let's play "Spot the difference" Wink

Looking over and over your own code in search of an error can sometimes be hard.
So another pair of eyes to help you out can come in handy.
Just look closely at the portion of your post I quoted.
Also have a look at the link provided by aviva4500
Re: Need help in converting LOB to timestamp [message #388269 is a reply to message #388262] Tue, 24 February 2009 01:35 Go to previous messageGo to next message
rnhullur
Messages: 3
Registered: February 2009
Location: Mumbai
Junior Member
Thanks Hammer, I am aware of ORA-01858, I want to know whether is it possible to convert the value I get from to_char i.e. '19/Feb/09 07:25 PM' to timestamp.

Thanks again.

-Raghu
Re: Need help in converting LOB to timestamp [message #388270 is a reply to message #388269] Tue, 24 February 2009 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe using TO_TIMESTAMP function?

Regards
Michel
Re: Need help in converting LOB to timestamp [message #388272 is a reply to message #388270] Tue, 24 February 2009 01:44 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Tue, 24 February 2009 08:41
Maybe using TO_TIMESTAMP function?

Regards
Michel



I guess that'll result in the same ORA-01858 error Wink
Re: Need help in converting LOB to timestamp [message #388280 is a reply to message #388272] Tue, 24 February 2009 02:14 Go to previous messageGo to next message
rnhullur
Messages: 3
Registered: February 2009
Location: Mumbai
Junior Member
Yes, I tried that...
Re: Need help in converting LOB to timestamp [message #388282 is a reply to message #388272] Tue, 24 February 2009 02:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
MarcS wrote on Tue, 24 February 2009 08:44
Michel Cadot wrote on Tue, 24 February 2009 08:41
Maybe using TO_TIMESTAMP function?

Regards
Michel



I guess that'll result in the same ORA-01858 error Wink

I'd say it's a matter of seconds before the OP spots his error Wink
Re: Need help in converting LOB to timestamp [message #388283 is a reply to message #388282] Tue, 24 February 2009 02:18 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank wrote on Tue, 24 February 2009 09:17
MarcS wrote on Tue, 24 February 2009 08:44
Michel Cadot wrote on Tue, 24 February 2009 08:41
Maybe using TO_TIMESTAMP function?

Regards
Michel



I guess that'll result in the same ORA-01858 error Wink

I'd say it's a matter of seconds before the OP spots his error Wink


I was about to post something to make things clearer, but that wouldn't do your post justice Laughing
Re: Need help in converting LOB to timestamp [message #388289 is a reply to message #388280] Tue, 24 February 2009 02:34 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
rnhullur wrote on Tue, 24 February 2009 09:14
Yes, I tried that...

What? That?
SQL> to_timestamp
SP2-0734: unknown command beginning "to_timesta..." - rest of line ignored.

Or
SQL> SELECT to_timestamp('19/Feb/09 07:25 PM', 'DD/Mon/YY HH:MI AM',
  2                      'nls_date_language=czech' ) FROM dual;
SELECT to_timestamp('19/Feb/09 07:25 PM', 'DD/Mon/YY HH:MI AM',
                    *
ERROR at line 1:
ORA-01843: not a valid month

Or
SQL> SELECT to_timestamp('19/Feb/09 07:25 PM', 'DD/Mon/YY HH:MI AM',
  2                      'nls_date_language=english' ) FROM dual;

TO_TIMESTAMP('19/FEB/0907:25PM','DD/MON/YYHH:MIAM','NLS_DATE_LANGUAGE=ENGLI
---------------------------------------------------------------------------
19-FEB-09 07.25.00.000000000 PM

1 row selected.

Or ...?
Previous Topic: Avoid Parallel Execution of a Procedure and DBMS_JOB (merged)
Next Topic: A simple Query (merged)
Goto Forum:
  


Current Time: Sun Dec 04 06:13:04 CST 2016

Total time taken to generate the page: 0.17458 seconds