Error in DBMS_JOB.SUBMIT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Error in DBMS_JOB.SUBMIT

  1. #1
    Join Date
    Apr 2002
    Posts
    1

    Angry

    Why I'm getting following error when excuted following script.

    SCRIPT==================================
    DECLARE
    JobNo INTEGER;
    BEGIN

    DBMS_JOB.SUBMIT(:JobNo,
    'E:\SQL\DataFrom400.sql',
    SysDate,
    'SysDate + 1/24');
    END;
    /
    --Statement processed.
    print JobNo
    =====================================

    ERROR=================================

    SQL> @ e:\sql\x.sql
    DECLARE
    *
    ERROR at line 1:
    ORA-06550: line 1, column 94:
    PLS-00103: Encountered the symbol ":" when expecting one of the following:
    := . ( @ % ;
    ORA-06512: at "SYS.DBMS_JOB", line 71
    ORA-06512: at "SYS.DBMS_JOB", line 121
    ORA-06512: at line 4

    JOBNO
    ---------
    ======================================

    KED

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Because you are only allowed to use PL/SQL code for a job.
    Here you're trying to invoke a SQL*Plus script and the parser doesn't recognize the 'E:\ ... ' as a valid PL/SQL block.

    In addition to that:

    * You have to use either an SQL*Plus bind variable or a PL/SQL variable. Here you declare PL/SQL variable JobNo and use SQL*Plus bind variable. If you want to print jobno in this way you have to use bind variable.

    * You have to COMMIT after dbms_job.submit.
    Code:
    variable JobNo number
    begin
      DBMS_JOB.SUBMIT(:JobNo, 
      'BEGIN pl_sql_code; END;', 
      SysDate, 
      'SysDate + 1/24'); 
      commit;
    end;
    /
    print JobNo

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