Unable to insert data into oracle EE stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 37
- Joined: Tue Jan 29, 2008 6:03 am
- Location: Guntur
- Contact:
Unable to insert data into oracle EE stage
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??
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??
Samratisking
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 37
- Joined: Tue Jan 29, 2008 6:03 am
- Location: Guntur
- Contact:
Write mode failure: SAM.SAMPLETABLE
Hi Priyadarshi,priyadarshikunal wrote:subject line says that you are not able to insert data to the table. What problem you are getting?
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
-
- Participant
- Posts: 37
- Joined: Tue Jan 29, 2008 6:03 am
- Location: Guntur
- Contact:
Re: Write mode failure: SAM.SAMPLETABLE
Please someone help me with this issue.samratisking wrote:Hi Priyadarshi,priyadarshikunal wrote:subject line says that you are not able to insert data to the table. What problem you are getting?
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
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.
use a transformation function timestamptodate() before inserting the data in the target table and change the corresponding column metadata as necessary.
Probal
-
- Participant
- Posts: 37
- Joined: Tue Jan 29, 2008 6:03 am
- Location: Guntur
- Contact:
I need the data without any changes
Hi Probal,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.
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
-
- Participant
- Posts: 37
- Joined: Tue Jan 29, 2008 6:03 am
- Location: Guntur
- Contact:
Re: I need the data without any changes
Somebody please help me with this issue... Thankyou..
Samratisking
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 37
- Joined: Tue Jan 29, 2008 6:03 am
- Location: Guntur
- Contact:
Sorry for the impatience.. Please help
Hi Ray,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.
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
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Sorry for the impatience.. Please help
Samratisking,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
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!
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Sorry for the impatience.. Please help
Samratisking,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
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!
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata