Delete and Insert Oracle OCI-Stage

[slk200]

Hi there,

i'm extracting data with the SAP BW Extract-Pack. One attribute is YEAR.
I like to write all the extracted data into an oracle table.
Problem is: i like to delete all the existing data of YEAR from the oracle table and then write all the new data into the oracle table.
e.g: extract pack comes with YEAR = 2005
in the oracle table, there are 500 data records with YEAR=2004 and 1000 data records with YEAR=2005 - so the job should delete all the 1000 data records with YEAR = 2005 and then write all the new records.
I tried it with the before-stage: delete from #table# where year:=40 --> problem: not all variables bound

any ideas
thx in advance
matthias

[Sainath.Srinivasan]

You need to say

delete from #table# where year=40

where 40 is value of field year in the row you expect to delete.

If you are using the OCI stage, you need to use the link variable you pass into it by using something like :1, :2 etc, where :1 stands for first link variable, :2 for second and so on.

[slk200]

i'm using the oci-stage and :40 is my link variable for YEAR....

[Sainath.Srinivasan]

Then it must be

delete from #table# where year = :40

[slk200]

thats what i tried:

**I tried it with the before-stage: delete from #table# where year :=40

Warning:ORA-01008: not all variables bound

[Sainath.Srinivasan]

Please notice that the colon resides on the right side of the equal-to operator and as a prefix to the number 40.

If this does not work, check whether all you incoming link variables are accounted for.

[slk200]

and tried it with year = :40 .... ora-01008

[slk200]

hmmmmm...okay...i have 40 incoming links...in my insert command, all these 40 links are defined, so how to define them in the delete command in the before-tab of the oci-stage, when the only key is YEAR ?

[Sainath.Srinivasan]

Test with a constant instead of :40. This will give you an idea of whether it is from the 'before stage' or from the insert SQL.

Alternatively re-use the parameter used for YEAR in the source.

[slk200]

works with a constant, e.g. year = 2005 ......???????

[slk200]

Problem solved with: delete from #table# where year = year

thx

[talk2shaanc]

Its an oracle error error code ORA-01008: not all variables bound
Cause: You tried to execute an SQL statement that contained substitution variables where all variables were not bound.
If you just want to delete 2005 records, you can give,
Delete from <tablename> where year=2005

[slk200]

sorry: problem NOT fixed...delete from #table# where year = year deletes ALL records...

i want to delete all records with year = 2005 but this value (2005) comes from the SAP extract an is not common in advance...

[Sainath.Srinivasan]

I believe you have defined a parameter, say param_YEAR, to represent the YEAR and used in the query from SAP to return for that YEAR.

You can use the same parameter in your before stage as
delete from #table_name# where year = #param_YEAR#

[chulett]

As an academic exercise, you can also do this with two links, ordered correctly.

Have your first output link from the transformer send only the YEAR column to an OCI stage. Constrain it so it only sends one row - the first row. Set the update action to 'delete' based on that single key field and set the transaction size to 1 so the deletes are immediately commited.

Your second link can do the inserts are you are doing them now.