ORA-01008: not all variables bound : error while deleting

[kirankumarreddydesireddy]

Hi

We are trying to execute a user defined sql using bind variable in "before" option in the SQL tab in OCI stage.

Our requirement was to delete all the rows in the target table that matches to the input "report_date" before loading into target table.

Hence,we are running user defined sql in the "before" option in SQl tab in target OCI stage.


DELETE FROM DW_TRAVEL.AMEX_ABA_CAK_AIRLINE WHERE TRUNC(REPORT_DATE) =TO_DATE(:5,'YYYY-MM-DD')

It was givng below error.

ORA-01008: not all variables bound


However,when I run the same statement in oracle.it deleted the rows.
For example : We had run the below statement in oracle.

DELETE FROM DW_TRAVEL.AMEX_ABA_CAK_AIRLINE WHERE TRUNC(REPORT_DATE) =TO_DATE('2011-07-01','YYYY-MM-DD')


However in the OCI stage,we have used bind variable :5 for the input report_date which has value stored as '2011-07-01' field which resulted in the error.


Can anyone has any idea why we are getting this error?



Thanks
Kiran

[ray.wurlod]

If you've got parameter marker :5 then you've got to do something with :1 through :4 (and, indeed, any beyond :5 that you may happen to have).

[kirankumarreddydesireddy]

Thanks Ray.

Can you suggest something that we can do here for (:1 to :4 and beyond)
as I have mentioned our requirement was to delete all the rows in the target table that matches to the input "report_date" before loading into target table.



Thanks
Kiran

[ray.wurlod]

Only supply to the delete the columns needed to identify the rows to be deleted. Chances are you only need one, and it will bind onto :1.

[chulett]

Exactly. All columns in the stage must be bound into the query, so only send in what you need.

[kirankumarreddydesireddy]

Hi Ray,

We have passed only the key coulmns on which we are deleting rows in target table and it worked.Thanks



Thanks
Kiran