Problem with Date format in oracOCI

sheema · Post by **sheema** » Fri Jan 26, 2007 11:55 am

i have updated it with a space as below
OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00".
But it not solve my problem,the data is getting loaded but i cannot view the data through oraOCI stage.

I_Server_Whale · Post by **I_Server_Whale** » Fri Jan 26, 2007 12:06 pm

Try running the job only for 5 records. And can you please look at the data using sqlplus and post the input and output fields. And what are the datatypes, lengths and scales of these date fields in the target OCI stage as well in the target table. That would help.

chulett · Post by **chulett** » Fri Jan 26, 2007 12:14 pm

sheema wrote:i have updated it with a space as below
OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00".
But it not solve my problem,the data is getting loaded but i cannot view the data through oraOCI stage.

What does this mean? Why "cannot view", what happens when you click View Data?

Please verify that you are using generated sql and the field in question is wrapped in a TO_DATE() function with the mask I specified earlier.

sheema · Post by **sheema** » Fri Jan 26, 2007 12:18 pm

The source data which i am getting is of datatype Varchar(10) and it is in the format mm/dd/yy.
I am converting it in transformer using
OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00"
Then i have datatype of the date fields in the oraOCI set to Timestamp(19).
The date datatype in the target oracle table is Date.The date fields are getting stored in the target table as DD-MON-YY.

sheema · Post by **sheema** » Fri Jan 26, 2007 12:21 pm

chulett,

The sql is Generated SQL and it has To_Date().
I am getting the error when i click view data

ocixyz: ORA-01821: date format not recognized

ocixyz.DSLink1: DSP.Open GCI $DSP.Open error -100.

I_Server_Whale · Post by **I_Server_Whale** » Fri Jan 26, 2007 12:22 pm

sheema wrote:The source data which i am getting is of datatype Varchar(10) and it is in the format mm/dd/yy.
I am converting it in transformer using
OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00"

If your source data has the year in a two digit format.
Then you can't use

Code: Select all

OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00"

, instead you should be using,

Code: Select all

OCONV(ICONV(Date,"D/MDY[2,2,2]"),"D-YMD[4,2,2]"):" 00:00:00"

Notice the change in the Iconv statement, [2,2,2] instead of [2,2,4]. Change it and let us know the result.

HTH,
Whale.

chulett · Post by **chulett** » Fri Jan 26, 2007 12:22 pm

sheema wrote:The source data which i am getting is of datatype Varchar(10) and it is in the format mm/dd/yy.
I am converting it in transformer using
OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00"
Then i have datatype of the date fields in the oraOCI set to Timestamp(19).
The date datatype in the target oracle table is Date.The date fields are getting stored in the target table as DD-MON-YY.

So, in other words you've got absolutely no problem. Dates are stored in Oracle in internal format, not in any particular flavor of ''MM' or 'DD' or 'YYYY'. You can select them and get them back out in any format under the sun, hence my Toad comment earlier.

sheema · Post by **sheema** » Fri Jan 26, 2007 12:25 pm

whale i am sorry that was a typo error the data is coming in mm/dd/yyyy format.

chulett · Post by **chulett** » Fri Jan 26, 2007 12:25 pm

sheema wrote:The sql is Generated SQL and it has To_Date().
I am getting the error when i click view data

ocixyz: ORA-01821: date format not recognized

ocixyz.DSLink1: DSP.Open GCI $DSP.Open error -100.

Post the SQL so we can see what is actually being generated.

sheema · Post by **sheema** » Fri Jan 26, 2007 12:27 pm

my problem is i am not able to view the data in oraOCI stage.

I_Server_Whale · Post by **I_Server_Whale** » Fri Jan 26, 2007 12:29 pm

chulett wrote:Post the SQL so we can see what is actually being generated.

And that problem could be your sql. So post it.

sheema · Post by **sheema** » Fri Jan 26, 2007 12:30 pm

The generated sql is

INSERT INTO XYZ (No,Date) VALUES (:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'))

chulett · Post by **chulett** » Fri Jan 26, 2007 12:36 pm

Nothing wrong with your SQL and the data is obviously getting loaded fine, so you've got some odd issue with View Data. Report it as a bug to your Support Provider, I *think* there may be a patch available.

sheema · Post by **sheema** » Fri Jan 26, 2007 12:38 pm

ok,Thanks for all the help.

Thanks
sheema

chulett · Post by **chulett** » Fri Jan 26, 2007 1:14 pm

I was thinking of a different patch. Let us know what you find out.

eSupport wrote:Description:
Oracle OCI stage (ORAOCI9) incorrectly binds the lookup column values to the SQL statement which is being executed. This results in the Oracle database error "ORA-1008: Not all variables bound" [ECASE 73423].

Also included in this patch is a fix for the SQL Builder in the Oracle OCI stage, which incorrectly converted a "WHERE" clause to a "HAVING" clause [ECASE 74270].