Results 1 to 9 of 9

Thread: dbms_job

  1. #1
    Join Date
    Jan 2002
    Posts
    77

    Unanswered: dbms_job

    I am trying to run a PL/SQL script at particular intervals, I submitted the following code

    SQL> variable jobno number;
    SQL> begin
    2 dbms_job.submit(:jobno,'C:\auto.sql',sysdate,sysda te+1);
    3 commit;
    4 end;
    5 /

    I get the following error:

    ERROR at line 1:
    ORA-23319: parameter value "21-AUG-04" is not appropriate
    ORA-06512: at "SYS.DBMS_JOB", line 57
    ORA-06512: at "SYS.DBMS_JOB", line 134
    ORA-06512: at line 2

    can't understand how I can correct this. Any help is much appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    I beleive you are missing some quotes:
    Code:
    dbms_job.submit(:jobno,'C:\auto.sql',sysdate,'sysdate+1');
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2002
    Posts
    77
    Thank you I changed my code to below:

    SQL> variable jobno number;
    SQL> begin
    2 dbms_job.submit(:jobno,'C:\auto.sql',sysdate,'sysd ate+1');
    3 commit;
    4 end;
    5 /

    Now I get the following error

    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 79
    ORA-06512: at "SYS.DBMS_JOB", line 136
    ORA-06512: at line 2

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by vld
    Thank you I changed my code to below:

    SQL> variable jobno number;
    SQL> begin
    2 dbms_job.submit(:jobno,'C:\auto.sql',sysdate,'sysd ate+1');
    3 commit;
    4 end;
    5 /

    Now I get the following error

    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 79
    ORA-06512: at "SYS.DBMS_JOB", line 136
    ORA-06512: at line 2
    The first parameter is an out variable where the assigned job number would be returned. use:

    declare
    jobno number;
    begin
    dbms_job.submit(jobno,'C:\auto.sql',sysdate,'sysda te+1');
    commit;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try
    declare
    JOBX NUMBER;
    ...
    ...
    dbms_job.submit(JOBx,'begin procedure; end;',sysdate + 3/(60*60*24));
    ...
    ...

    HTH
    Gregg

  6. #6
    Join Date
    Jan 2002
    Posts
    77
    I guess I can only run procuderes using DBMS_Job and not SQL scripts. My next question is can I run SQL scripts within a PL/SQL procedure.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    That's why Crontab and Windows Task Manager exists.

  8. #8
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Quote Originally Posted by vld
    I guess I can only run procuderes using DBMS_Job and not SQL scripts. My next question is can I run SQL scripts within a PL/SQL procedure.

    You have to wrap the SQL code into PL/SQL procedure:

    CREATE OR REPLACE PROCEDURE .........

    Then you can use it in the DBMS_JOB. If you need help with this - post the 'C:\auto.sql' file.


    Hope that helps,

    clio_usa - OCP DBA 8/8i/9i

  9. #9
    Join Date
    Jan 2002
    Posts
    77
    Quote Originally Posted by clio_usa
    You have to wrap the SQL code into PL/SQL procedure:

    CREATE OR REPLACE PROCEDURE .........

    Then you can use it in the DBMS_JOB. If you need help with this - post the 'C:\auto.sql' file.


    Hope that helps,

    clio_usa - OCP DBA 8/8i/9i
    Thats what I ended up doing Clio, thanks.

Posting Permissions

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