Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    22

    Question Unanswered: DBMS_JOB related

    Hi,

    I am facing issue in two areas.

    1. Compiling a procedure which has dbms_job.submit within it.

    2. Executing a procedure withing the job - Procedure having inputs as strings.

    Below is the code.
    ---------------------------------------------
    CREATE OR REPLACE PROCEDURE SP_CREATE_JOB AS
    v_job number;
    Begin
    dbms_job.submit(v_job,
    sp_write_cronfile('E:\temp','abc1.txt'), sysdate,
    'sysdate+2/24');
    End;
    ---------------------------------------------

    If you notice, I am trying to call a procedure which inside the job submit. It is not compiling. Error given is

    5/2 PL/SQL: Statement ignored

    6/2 PLS-00222: no function with name 'SP_WRITE_CRONFILE' exists in this scope

    HOWEVER when I change the line as below, then it compiles
    'sp_write_cronfile(''E:\temp'',''abc1.txt'')' --Note the quotes.

    But then when run it gives the error as

    ERROR at line 1:
    ORA-06550: line 1, column 133:
    PLS-00103: Encountered the symbol "END" when expecting one of the following:
    := . ( % ;
    The symbol ";" was substituted for "END" to continue.
    ORA-06512: at "SYS.DBMS_JOB", line 79
    ORA-06512: at "SYS.DBMS_JOB", line 136
    ORA-06512: at "DEV1.SP_CREATE_JOB", line 5
    ORA-06512: at line 1

    Any help is greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I would create a procedure in the DB which does
    sp_write_cronfile('E:\temp','abc1.txt')
    and then just call the procedure within the DBMS_JOB line.
    This approach should eliminate the confusion WRT single & double quote marks.

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    'sp_write_cronfile(''E:\temp'',''abc1.txt'');'

    put ; after the proc name

  4. #4
    Join Date
    Feb 2004
    Posts
    22
    But I still get

    PLS-00222: no function with name 'SP_WRITE_CRONFILE' exists in this scope

    That means the job is not recongizing the procedure.

  5. #5
    Join Date
    Feb 2004
    Posts
    22
    THANKS much fellas!

    I used

    'sp_write_cronfile(''E:\temp'',''abc1.txt'');'

    It worked. It also identified the procedure.

Posting Permissions

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