Page 1 of 1

Unable to insert data into oracle EE stage

Posted: Thu May 20, 2010 12:32 am
by samratisking
Hi,

My job does a one to one Extraction & Loading from Oracle EE stage to another oracle EE stage.
The Source table has date fields, which are viewable only when I take the datatype as timestamp in the column metadata.

But, when loading this data into target, which has the same fields, how can I load this timestamp to a date field without any change in the data??

Posted: Thu May 20, 2010 3:13 am
by priyadarshikunal
Oracle Date stores Date part as well as time part, which is same as data stage timestamp without microsecond. Give it a shot with data type as timestamp.

Posted: Thu May 20, 2010 3:14 am
by priyadarshikunal
subject line says that you are not able to insert data to the table. What problem you are getting?

Write mode failure: SAM.SAMPLETABLE

Posted: Thu May 20, 2010 5:38 am
by samratisking
priyadarshikunal wrote:subject line says that you are not able to insert data to the table. What problem you are getting?
Hi Priyadarshi,

Getting the error "Write mode failure: SAM.SAMPLETABLE" when I am trying to use the load option. But the table is actually present the DB. I can view that from TOAD. It is a simple job. Source to target load, with a copy stage in the middle. Both the source & target are Oracle EE stages.

When using the Upsert option getting the error "The provided insert statement did not prepare correctly;
please verify that your statement is correct;
statement: INSERT
INTO
SAM.SAMPLETABLE
(G_TESTCAT, G_DESCRIPTION, G_INACTIVE, G_CREATEDBY, G_CREATEDDATE, G_MODIFIEDBY, G_MODIFIEDDATE)
VALUES
( :G_TESTCAT, :G_DESCRIPTION, :G_INACTIVE, :G_CREATEDBY, :G_CREATEDDATE, :G_MODIFIEDBY, :G_MODIFIEDDATE)." . Please help me with these issues. What could be wrong here. One more observation is that, If I load the data from sequential file to Oracle EE with a Modify stage in the middle, then the data is loaded correctly(Using Load option). Thanks in anticipation.[/img]

Re: Write mode failure: SAM.SAMPLETABLE

Posted: Thu May 20, 2010 6:16 am
by samratisking
samratisking wrote:
priyadarshikunal wrote:subject line says that you are not able to insert data to the table. What problem you are getting?
Hi Priyadarshi,

Getting the error "Write mode failure: SAM.SAMPLETABLE" when I am trying to use the load option. But the table is actually present the DB. I can view that from TOAD. It is a simple job. Source to target load, with a copy stage in the middle. Both the source & target are Oracle EE stages.

When using the Upsert option getting the error "The provided insert statement did not prepare correctly;
please verify that your statement is correct;
statement: INSERT
INTO
SAM.SAMPLETABLE
(G_TESTCAT, G_DESCRIPTION, G_INACTIVE, G_CREATEDBY, G_CREATEDDATE, G_MODIFIEDBY, G_MODIFIEDDATE)
VALUES
( :G_TESTCAT, :G_DESCRIPTION, :G_INACTIVE, :G_CREATEDBY, :G_CREATEDDATE, :G_MODIFIEDBY, :G_MODIFIEDDATE)." . Please help me with these issues. What could be wrong here. One more observation is that, If I load the data from sequential file to Oracle EE with a Modify stage in the middle, then the data is loaded correctly(Using Load option). Thanks in anticipation.[/img]
Please someone help me with this issue.

Posted: Thu May 20, 2010 6:24 am
by probal
As far as I know if your db table contains date type field ,then at the time of loading it to the oracle EE stage automatically convert it into the time stamp type in the column metadata.

use a transformation function timestamptodate() before inserting the data in the target table and change the corresponding column metadata as necessary.

I need the data without any changes

Posted: Thu May 20, 2010 6:29 am
by samratisking
probal wrote:As far as I know if your db table contains date type field ,then at the time of loading it to the oracle EE stage automatically convert it into the time stamp type in the column metadata.

use a transformation function timestamptodate() before inserting the data in the target table and change the corresponding column metadata as necessary.
Hi Probal,

The problem is, that I need the data without any changes. If I to a type conversion from timestamp to date, then the Time part is missing. So, while loading the data in the target stage, I am just selecting the datatype as timestamp instead of date(got date from metadata import).

please help me.. the issue is not yet resolved.

Re: I need the data without any changes

Posted: Thu May 20, 2010 6:59 am
by samratisking
Somebody please help me with this issue... Thankyou..

Posted: Thu May 20, 2010 7:30 am
by ray.wurlod
Learn some patience.

DSXchange is an all-volunteer site; members post as and when they can. Some of them are asleep at the moment.

If you want 30 minute response, sign up for premium services with your official support provider.

Sorry for the impatience.. Please help

Posted: Thu May 20, 2010 7:46 am
by samratisking
ray.wurlod wrote:Learn some patience.

DSXchange is an all-volunteer site; members post as and when they can. Some of them are asleep at the moment.

If you want 30 minute response, sign up for premium services with your official support provider.
Hi Ray,

I double checked that I am using the user/schema name with a '.' and then the table name. Getting the error "Write mode failure: SAM.SAMPLETABLE" when I am trying to use the load option. But the table is actually present the DB. I can view that from TOAD. It is a simple job. Source to target load, with a copy stage in the middle. Both the source & target are Oracle EE stages.

When using the Upsert option getting the error "The provided insert statement did not prepare correctly;
please verify that your statement is correct;
statement: INSERT
INTO
SAM.SAMPLETABLE
(G_TESTCAT, G_DESCRIPTION, G_INACTIVE, G_CREATEDBY, G_CREATEDDATE, G_MODIFIEDBY, G_MODIFIEDDATE)
VALUES
( :G_TESTCAT, :G_DESCRIPTION, :G_INACTIVE, :G_CREATEDBY, :G_CREATEDDATE, :G_MODIFIEDBY, :G_MODIFIEDDATE)." . Please help me with these issues. What could be wrong here. One more observation is that, If I load the data from sequential file to Oracle EE with a Modify stage in the middle, then the data is loaded correctly(Using Load option). Thanks in anticipation

Posted: Thu May 20, 2010 8:03 am
by probal
Asa far your question is considered you said that the source table has the datatype date.According was my answer

Re: Sorry for the impatience.. Please help

Posted: Thu May 20, 2010 8:10 am
by vivekgadwal
samratisking wrote: Hi Ray,

I double checked that I am using the user/schema name with a '.' and then the table name. Getting the error "Write mode failure: SAM.SAMPLETABLE" when I am trying to use the load option. But the table is actually present the DB. I can view that from TOAD. It is a simple job. Source to target load, with a copy stage in the middle. Both the source & target are Oracle EE stages.

When using the Upsert option getting the error "The provided insert statement did not prepare correctly;
please verify that your statement is correct;
statement: INSERT
INTO
SAM.SAMPLETABLE
(G_TESTCAT, G_DESCRIPTION, G_INACTIVE, G_CREATEDBY, G_CREATEDDATE, G_MODIFIEDBY, G_MODIFIEDDATE)
VALUES
( :G_TESTCAT, :G_DESCRIPTION, :G_INACTIVE, :G_CREATEDBY, :G_CREATEDDATE, :G_MODIFIEDBY, :G_MODIFIEDDATE)." . Please help me with these issues. What could be wrong here. One more observation is that, If I load the data from sequential file to Oracle EE with a Modify stage in the middle, then the data is loaded correctly(Using Load option). Thanks in anticipation
Samratisking,

Did you try executing this SQL statement from TOAD or CLI to see if it is working? Use the SQL generated from DataStage and of course, do pass the values appropriately. Let us know what transpires!

Re: Sorry for the impatience.. Please help

Posted: Thu May 20, 2010 8:13 am
by vivekgadwal
samratisking wrote: Hi Ray,

I double checked that I am using the user/schema name with a '.' and then the table name. Getting the error "Write mode failure: SAM.SAMPLETABLE" when I am trying to use the load option. But the table is actually present the DB. I can view that from TOAD. It is a simple job. Source to target load, with a copy stage in the middle. Both the source & target are Oracle EE stages.

When using the Upsert option getting the error "The provided insert statement did not prepare correctly;
please verify that your statement is correct;
statement: INSERT
INTO
SAM.SAMPLETABLE
(G_TESTCAT, G_DESCRIPTION, G_INACTIVE, G_CREATEDBY, G_CREATEDDATE, G_MODIFIEDBY, G_MODIFIEDDATE)
VALUES
( :G_TESTCAT, :G_DESCRIPTION, :G_INACTIVE, :G_CREATEDBY, :G_CREATEDDATE, :G_MODIFIEDBY, :G_MODIFIEDDATE)." . Please help me with these issues. What could be wrong here. One more observation is that, If I load the data from sequential file to Oracle EE with a Modify stage in the middle, then the data is loaded correctly(Using Load option). Thanks in anticipation
Samratisking,

Did you try executing this SQL statement from TOAD or CLI to see if it is working? Use the SQL generated from DataStage and of course, do pass the values appropriately. Let us know what transpires!

Posted: Thu May 20, 2010 8:19 am
by vinothkumar
Try to add a reject link in target table and get the error codes.. You can find why it is not gettting populated.

Posted: Thu May 20, 2010 9:24 am
by antonyraj.deva
How about trying the "DateTimeStampTo OraOciWithTime" or "DateTimeStampTo OraOci" routine? :idea: