| Author |
Message
|
| biswagourab |
 Posted: Mon Jun 26, 2006 4:43 pm Post subject: Inserting BLOB DATE value to Oracle Problem |
 |
|
Apprentice
Joined: 31 May 2005
Posts: 47
Location: London
|
Hi
I am trying to SUBSTRING the value '20060626' (date) from a BLOB data that I am picking up using FileInput Node and trying to insert it to Oracle DB having a date field
I am using
PASSTHRU('INSERT INTO GE.TBL_CUSTOMER(CUSTOMER_ID,EXTRACTED_DATE,CUSTOMER_FAX_NUMBER) VALUES(?,to_date(?),?)', SUBSTRING(InputRoot.BLOB.BLOB FROM 1 FOR 10),SUBSTRING(InputRoot.BLOB.BLOB FROM 11 FOR 8 ),SUBSTRING(InputRoot.BLOB.BLOB FROM 19 FOR 10) );
But it gives me the error that
"literal does not match format string"
when I try casting this blob to CHAR I get
"year must be between -4713 and +9999, and not be 0 ''
Please help. |
|
| Back to top |
|
 |
| shalabh1976 |
| Posted: Mon Jun 26, 2006 9:12 pm Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Biswagourab,
When you do : SUBSTRING(InputRoot.BLOB.BLOB FROM 11 FOR 8 ) what is the value returned ?
A cast might be required.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| biswagourab |
| Posted: Tue Jun 27, 2006 5:08 am Post subject: |
|
|
Apprentice
Joined: 31 May 2005
Posts: 47
Location: London
|
Hi Shalabh
I am pretty sure that the value that is returned is a hexadecimal value of the input date that I am sending.
e.g I am sending 20060613 then I get X'3230303630363133
I am trying to cast it to CHAR but Oracle still throws the error saying
"year must be between -4713 and +9999, and not be 0 ''
Regards |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Tue Jun 27, 2006 5:48 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Can you post your PASSTHRU code with the CAST code inside ?
Please specify the CCSID parameter as well.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| biswagourab |
| Posted: Tue Jun 27, 2006 6:01 am Post subject: |
|
|
Apprentice
Joined: 31 May 2005
Posts: 47
Location: London
|
Hi
The code I am using is...
PASSTHRU('INSERT INTO GE.TBL_CUSTOMER(CUSTOMER_ID,EXTRACTED_DATE,CUSTOMER_FAX_NUMBER) VALUES(?,to_date(?),?)', SUBSTRING(InputRoot.BLOB.BLOB FROM 1 FOR 10),CAST(SUBSTRING(InputRoot.BLOB.BLOB FROM 11 FOR 8 )as CHAR CCSID 37),SUBSTRING(InputRoot.BLOB.BLOB FROM 19 FOR 10) );
the input data contains the value 20060613 for the positions 11 for 8
Regards |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Wed Jun 28, 2006 11:23 pm Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Try using the format in the to_date function :
for e.g.:
to_date('20020315', 'yyyymmdd');
If this works then replace the date with your code.
It may be that the Oracle date format wants the date in some different format hence you may need to explicitly specify the format.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| biswagourab |
| Posted: Wed Jul 05, 2006 5:28 am Post subject: |
|
|
Apprentice
Joined: 31 May 2005
Posts: 47
Location: London
|
Hi Shalabh,
I have got the problem resolved, I used Encoding to resolve it. And the code that you have suggested.... I have tried it before, but the editor does not validate the format and throws an error at the 'YYYYDDMM' location.
dont know why it does so, but eventually I found a way round it by saying to_date('20060607') and not specifing the format.
cheers
Biswa |
|
| Back to top |
|
|
|
|
