Unable to insert data into oracle EE stage

[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??

[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.

[priyadarshikunal]

subject line says that you are not able to insert data to the table. What problem you are getting?

[samratisking]

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]

[samratisking]

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.

[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.

[samratisking]

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.

[samratisking]

Somebody please help me with this issue... Thankyou..

[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.

[samratisking]

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

[probal]

Asa far your question is considered you said that the source table has the datatype date.According was my answer

[vivekgadwal]

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!

[vivekgadwal]

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!

[vinothkumar]

Try to add a reject link in target table and get the error codes.. You can find why it is not gettting populated.

[antonyraj.deva]

How about trying the "DateTimeStampTo OraOciWithTime" or "DateTimeStampTo OraOci" routine?