Delete statement in a job

[nkln@you]

I need to execute the following statement in a Datastage Job

delete from hist_bucs_shipment where db_upload_date> ADD_MONTHS(SYSDATE, -6);

Constraints:
1 It should not be in Before/After SQL
2 It should not done using a Database Procedure, i.e no procedure or function should be exceuted.

What is the best way of doing this.

[ray.wurlod]

Create a job that sends one row to Oracle, use user-defined SQL.
You may like to identify one key column and add :1 = :1 in the WHERE clause.

[nkln@you]

Hi Ray,

I have given following statement in User Defined SQL.

DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) and :1=:1

The records are not gettingd deleted as expected.

When I execute the comman in TOAD ( DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) ),2 records are deleted.

When i checked the log , the SQL appears as
DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) and :1=:1

i.e parameters are not replaced (:1=:1). Is this causing a problem

[ray.wurlod]

OK, try it without reference to the parameter markers. The markers are probably not being replaced because you did not declare a column as Key.

[chulett]

When i checked the log , the SQL appears as
DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) and :1=:1

i.e parameters are not replaced (:1=:1). Is this causing a problem

Nope, the log always shows the sql with the parameter markers. Make sure your single trigger column is marked as a key and has a 'real' value in it - i.e. something other than null.

[sachin1]

you can also use before subroutine in job properties


execDos sqlplus -s username/password @filename.sql,>file.log

in filename.sql you can write the delete statement.