dbms_job

[helpme]

1 declare
2 var jobno number;
3 begin
4 dbms_job.submit(:jobno,'test.add_data',sysdate,'sysdate+1/288');
5 commit;
6* end;
SQL> /
SP2-0552: Bind variable "JOBNO" not declared.
SQL>

Can any1 tell me what mite be the problem


here i am logged on as SYS user and adding a proc in user test into the job queue


Thanx

[pipo]

variable jobno number;

begin
dbms_job.submit ...
end;

print jobno

PS : absolutely no need to commit !!

[helpme]

No still gives the same error!!!!

[ales]

You are calling dbms_job.submit from PL/SQL block, thus you can't use SQL*Plus bind variable.
You can either stop using jobno as SQL*Plus bind variable:
dbms_job.submit(jobno,'test.add_data;',sysdate,'sysdate+1/288');
or use SQL*Plus:
var jobno number
exec dbms_job.submit(:jobno,'test.add_data;',sysdate,'sysdate+1/288');
commit;

Do not forget the semicolon in the end of call of your procedure!

P.S. I'd recommend you to commit ...

Regards,
Ales

[Valsaraj]

hi

lets' track ur errors. I refer to ur code only.

1- U r defining a SQL*PLUS bind variable, when u use

Variable jobno number;

take out the declare ( here u start a PL/SQL !).

2. U havn't put a ';' (semi colon) with 'test.add_data'

it should be 'test.add_data;'

line wise i will code the thing.
your code should be like this

1.variable jobno number;

2.begin

3.dbms_job.submit(:jobno,'test.add_data;',sysdate,'sysdate+1/288');

4.commit;

5. end;


the need of commit ----
to commit the job submission as a transaction, u should issue the COMMIT, just after the dbms_job.submit(.......);

hope this will clear ur problem.

Valsaraj

[helpme]

Thanx guys
it worked.....
thanx a lot........