DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: dbms_job

  1. #1
    Join Date
    Jun 2001
    Posts
    316

    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

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    variable jobno number;

    begin
    dbms_job.submit ...
    end;

    print jobno

    PS : absolutely no need to commit !!

  3. #3
    Join Date
    Jun 2001
    Posts
    316
    No still gives the same error!!!!

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  5. #5
    Join Date
    Jul 2001
    Location
    Dubai
    Posts
    3
    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


  6. #6
    Join Date
    Jun 2001
    Posts
    316
    Thanx guys
    it worked.....
    thanx a lot........

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width