asked on

Utilize DBMS_JOB.SUBMIT to schedule daily job.

Hi,

I have following SQL statement that I need to run every day at midnight. What is the best way of scheduling it with DBMS_JOB.SUBMIT?

UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = '5'
WHERE APPLICANT_ID IN
(SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));'

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alexk23

ASKER

You can tell I am doing it first time...
Here is what I put together, as recommended by examples provided.

SQL> !cat abc.sql
variable Jobno number;
exec DBMS_JOB.SUBMIT(:Jobno, 'UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = '5'
WHERE APPLICANT_ID IN
(SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',
trunc(sysdate),trunc(sysdate)+1);');
commit;
end;
/

SQL> @abc.sql
ERROR:
ORA-01756: quoted string not properly terminated
SP2-0734: unknown command beginning "WHERE APPL..." - rest of line ignored.
ERROR:
ORA-01756: quoted string not properly terminated

Please advise.

Guy Hengel [angelIII / a3]

please try this:

exec DBMS_JOB.SUBMIT(:Jobno, 'UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = "5" WHERE APPLICANT_ID IN
(SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',
trunc(sysdate),trunc(sysdate)+1);');
commit;
end;

Naveen Kumar

you should use two single quotes '' whenever a single quote ' appears inside a string.

Use as angelIII has given.

alexk23

ASKER

Something is still not right when query ran:
variable Jobno number;
exec DBMS_JOB.SUBMIT(:Jobno, 'UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = "5" WHERE APPLICANT_ID IN
(SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',
trunc(sysdate),trunc(sysdate)+1);');
commit;
end;

ERROR:
ORA-01756: quoted string not properly terminated

(SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',
*
ERROR at line 1:
ORA-00911: invalid character

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

variable Jobno number;
exec DBMS_JOB.SUBMIT(:Jobno,'UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = "5" WHERE APPLICANT_ID IN (SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',trunc(sysdate),trunc(sysdate)+1);
commit;

Try this out.

Naveen Kumar

variable Jobno number;
exec DBMS_JOB.SUBMIT(:Jobno,'UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = ''5'' WHERE APPLICANT_ID IN (SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',trunc(sysdate),trunc(sysdate)+1);
commit;

Try this out.

alexk23

ASKER

Re-ran recommended statement. What does ORA-23319: parameter value "13-DEC-06" is not appropriate error mean?

SQL> !cat abc.sql
variable Jobno number;
exec DBMS_JOB.SUBMIT(:Jobno,'UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = ''5'' WHERE APPLICANT_ID IN (SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',trunc(sysdate),trunc(sysdate)+1);
commit;
end;
/

SQL> @abc.sql
BEGIN DBMS_JOB.SUBMIT(:Jobno,'UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = ''5'' WHERE APPLICANT_ID IN (SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',trunc(sysdate),trunc(sysdate)+1); END;

*
ERROR at line 1:
ORA-23319: parameter value "13-DEC-06" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at line 1

Naveen Kumar

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_job2.htm#1001490

use the above url for your reference.

variable Jobno number;
exec DBMS_JOB.SUBMIT(:Jobno,'UPDATE SYS.TEST_IUB_CD_APPLICATION SET STATUS = ''5'' WHERE APPLICANT_ID IN (SELECT distinct APPLICANT_ID FROM SYS.TEST_IUB_CD_APPLICATION WHERE PURGE_DATE < TRUNC(SYSDATE));',trunc(sysdate),'sysdate+1');
commit;

The 4th argument should be varchar2 not date data type.