dbms_job.submit vs. too much red wine
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: dbms_job.submit vs. too much red wine

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    dbms_job.submit vs. too much red wine

    Still recovering from last night........
    Why doesn't this work....

    VAR jobno NUMBER
    BEGIN
    dbms_job.submit(:jobno,
    'TOOLS.HOUSEKEEP',
    'NEXT_DAY(TRUNC(SYSDATE), ''SATURDAY'')'
    );
    END;
    /

    ERROR at line 6:
    ORA-01858: a non-numeric character was found where a numeric was expected
    ORA-06512: at line 2

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    next_date is DATE, doesnt need ''

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    The day doesn't need double quotes, try single quotes instead

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    jovery you dont need quotes at all for DATE datatype....

  5. #5
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    I need coffee............
    and further help I'm afraid

    --------------------------------------------------------------------

    Wrote file afiedt.buf

    1 BEGIN
    2 dbms_job.submit(:jobno,
    3 'TOOLS.HOUSEKEEP',
    4 'NEXT_DAY(TRUNC(SYSDATE), 'SATURDAY')'
    5 );
    6* END;

    'NEXT_DAY(TRUNC(SYSDATE), 'SATURDAY')'
    *
    ERROR at line 4:
    ORA-06550: line 4, column 43:
    PLS-00103: Encountered the symbol "SATURDAY" when expecting one of the following:
    . ( ) , * @ % & | = - + < / > at in is mod not range rem =>
    .. <> or != or ~= >= <= <> and or like
    between ||
    The symbol ". was inserted before "SATURDAY" to continue.

    ------------------------------------------------------------------

    Wrote file afiedt.buf

    1 BEGIN
    2 dbms_job.submit(:jobno,
    3 'TOOLS.HOUSEKEEP',
    4 NEXT_DAY(TRUNC(SYSDATE), "SATURDAY")
    5 );
    6* END;

    NEXT_DAY(TRUNC(SYSDATE), "SATURDAY")
    *
    ERROR at line 4:
    ORA-06550: line 4, column 41:
    PLS-00201: identifier 'SATURDAY' must be declared
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored

    ---------------------------------------------------------------

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    use single quotes for DAY

    I missread your post jovery, make a pardon!

  7. #7
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    I did, I think, look at the first error message in the 2 above =[

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    I dont know why it works for me?

    Code:
    SQL> BEGIN
      2   dbms_job.submit(:jobno,
      3   'ANALIZAR_BBDD;',
      4   NEXT_DAY(TRUNC(SYSDATE), 'SATURDAY')
      5   );
      6   END;
      7  /
    
    PL/SQL procedure successfully completed.

  9. #9
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by Horace
    Wrote file afiedt.buf

    1 BEGIN
    2 dbms_job.submit(:jobno,
    3 'TOOLS.HOUSEKEEP',
    4 'NEXT_DAY(TRUNC(SYSDATE), 'SATURDAY')'
    5 );
    6* END;
    'NEXT_DAY(TRUNC(SYSDATE), 'SATURDAY')'

    Why do you need these quotes marked as BOLD??? remove those..

  10. #10
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    1 BEGIN
    2 dbms_job.submit(:jobno,
    3 'TOOLS.HOUSEKEEP(30);',
    4 NEXT_DAY(TRUNC(SYSDATE), 'SATURDAY')
    5 );
    6* END;


    PL/SQL procedure successfully completed.


    I think it was the missing parameter for the housekeep function.
    Thanks for the input guys.

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