ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Eror Inserting current_timestamp into Oracle

Post new topic  Reply to topic
 Eror Inserting current_timestamp into Oracle « View previous topic :: View next topic » 
Author Message
LH33
PostPosted: Mon Sep 08, 2003 11:47 am    Post subject: Eror Inserting current_timestamp into Oracle Reply with quote

Master

Joined: 21 Nov 2002
Posts: 200
HI! I have a message flow that needs to insert the current date and time into an Oracle table. I set a variable equal to current_timestamp and try to insert that value. I get an Oracle error stating that the value is too large for the column. The reason is that the value of the variable looks like the following after assigning it to current_timestamp:

SET MODDATETIME = current_timestamp;

<MODDATETIME>TIMESTAMP &apos;2003-09-08 15:15:55.265&apos;</MODDATETIME>

Is there a way to strip off the word 'TIMESTAMP' and the escape sequence for the apostrophy so that the MODDATETIME tag contains just the date and time before inserting into Oracle?

Thanks!! Lisa
Back to top
View user's profile Send private message
Craig B
PostPosted: Mon Sep 08, 2003 12:00 pm    Post subject: Reply with quote

Partisan

Joined: 18 Jun 2003
Posts: 316
Location: UK
Hi,

Unfortunately there is no built-in functionality to do this for you. You will need to CAST your resultant timeStamp to a CHARACTER variable, and then use the SUBSTRING function to get the portion of the TIMESTAMP you require. I have seen other solutions where the database manager in question supports functions that can also give the current date/time, and the PASSTHRU function has been called, and these functions have been embedded in the Database call. This approach also works, but can limit your ESQL to communicating with one type of database manager that has to support these SQL functions.
_________________
Regards
Craig
Back to top
View user's profile Send private message
LH33
PostPosted: Mon Sep 08, 2003 12:36 pm    Post subject: Reply with quote

Master

Joined: 21 Nov 2002
Posts: 200
Craig,

Thanks for your help!! That works!

Have a good one! Lisa
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Eror Inserting current_timestamp into Oracle
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
  
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.