I would bet that's because you are not letting the stage generate the sql, which would let it automaticallt 'adjust' to your changes. You are using 'user-defined sql' which is causing your problem.sheema wrote:ok,but when just select key1 as key in oraOCI and run the job i am getting the warning
ORA-01008: not all variables bound
Replace existing rows
Moderators: chulett, rschirm, roy
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
In that case, it sounds like a bug. There is a patch on the IBM Information Integration Solutions Portal that may be what you need:
Or call your support provider and request it.User-defined SQL in Oracle OCI Stage ALL DataStage 7.5.1 05/26/2005 e73423_ALL_751.zip
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].
Compatibility Notes:
This zip archive contains the DataStage Client patch for the SQL Builder, and the server plug in packages for all DataStage 7.5.1 server platforms.
Install Notes:
Follow the instructions in the readme contained within this patch to complete the installation. The client patch installation must be completed on every DataStage client computer.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks.
Now i see that Replace Existing rows completely does not serve my purpose.For example if i have 3 rows as below already existing in the Oracle table
1001,abc
1001,def
1001,rst
where as i have below 2 rows in the source file
1001,abc
1001,def
I should design a job where i should just have the records
1001,abc
1001,def in the oracle table finally.The row 1001,rst should be deleted.
How do i do this in a single job.
Now i see that Replace Existing rows completely does not serve my purpose.For example if i have 3 rows as below already existing in the Oracle table
1001,abc
1001,def
1001,rst
where as i have below 2 rows in the source file
1001,abc
1001,def
I should design a job where i should just have the records
1001,abc
1001,def in the oracle table finally.The row 1001,rst should be deleted.
How do i do this in a single job.
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ
Please correct me if I am wrong but this could work :
Seqfile-----------TX1------------Temp_Table----------TX2-------Target_table
In Temp_table do insert and in Target_table do Delete existing rows WHERE temp_table.column1=target_table.column1 and temp_table.column2 != target_table.column2
Seqfile-----------TX1------------Temp_Table----------TX2-------Target_table
In Temp_table do insert and in Target_table do Delete existing rows WHERE temp_table.column1=target_table.column1 and temp_table.column2 != target_table.column2
"Nobody is expert in Everything,
But Everybody is expert in Something."
But Everybody is expert in Something."
Hi Sheema,sheema wrote:Thanks.
Now i see that Replace Existing rows completely does not serve my purpose.For example if i have 3 rows as below already existing in the Oracle table
1001,abc
1001,def
1001,rst
where as i have below 2 rows in the source file
1001,abc
1001,def
I should design a job where i should just have the records
1001,abc
1001,def in the oracle table finally.The row 1001,rst should be deleted.
How do i do this in a single job.
It seems that you need only current source file data in target table. Try with the following option, hope this works according to your requirement.
First truncate target table, then insert rows from source table.
implement this in datastage , by giving two links to target odbc
one: to truncate target table
two: to insert current rows from source file.
Truncating table wont work, becasue you ll end up in loosing other key which are not in you current list. Now its time to accept the existing design. One improvement that you can make is, no need to aggregate to get the distinct list of keys, anyways you can use "Delete exisintg rows only" option with single key marked in it.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
I think you can do by making multiple jobs.
1. Create a dataset as the target.
2. Get the distinct first column values from dataset and delete records from target table by using using criteria (where targettable.column=datasetfirstcolumn).
3. Insert the values from dataset into target table.
- Pavan Batchu
1. Create a dataset as the target.
2. Get the distinct first column values from dataset and delete records from target table by using using criteria (where targettable.column=datasetfirstcolumn).
3. Insert the values from dataset into target table.
- Pavan Batchu