Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Location
    Chiang Mai Thailand
    Posts
    14

    Post Unanswered: ORACLE Job Scheduler

    Hi everybody I try to submit job on oracle9i.
    From job submit standard statement

    DBMS_JOB.SUBMIT(
    Job
    What
    Next_date
    Interval
    No_parse )

    and my's What in statement are
    update t_date_ms

    1:set I_TODAY = (select to_char(sysdate,'yyyymmdd') from dual),
    2:I_UPDATE_DATE = (select to_char(sysdate,'yyyymmdd') from dual);
    From the second line in 'YYYYMMDD' it must have single quotes.
    Then Oracle look at that single quotes are wrong.

    And this is my syntax to submit job

    SQL> VARIABLE JOB NUMBER;
    SQL> BEGIN
    2 DBMS_JOB.SUBMIT(
    3 :JOB,
    4 'UPDATE T_DATE_MS SET I_TODAY =
    5 (SELECT TO_CHAR(SYSDATE,'YYYYMMDD') from dual),
    6 I_UPDATE_DATE = (SELECT TO_CHAR(SYSDATE,'YYYYMMDD') from dual);',
    7 sysdate,
    8 'sysdate+1';
    9 commit;
    10 end;
    11 /
    (SELECT TO_CHAR(SYSDATE,'YYYYMMDD') from dual),
    *
    Error on line 5:
    ORA-06550: line 5, column 27:
    PLS-00103:Found symbol "YYYYMMDD" ,expect following values:
    . ( ) , * @
    % & | = - + < / > at in is mod not range rem => ..
    <an exponent (**)> <> or != or ~= >= <= <> and or like
    between ||

    How can I fix my statement to submit job
    Kindly regards,
    Thana p.
    Chiangmai Thailand
    Yim_cm@hotmail.com

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try replacing ' (single quotes) in your update statement with '' (two single quotes NOT double quotes). If you put ' in your update it terminates the string at that point.

    Alan

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi tanaka,

    I think the problem is the ' that goes around the date formatting so it thinks it has come to the end of the quote from 'UPDATE .

    I could be wrong but I think it should look like this where '' are two single quotes.

    'UPDATE T_DATE_MS SET I_TODAY =
    (SELECT TO_CHAR(SYSDATE,''YYYYMMDD'') from dual),
    I_UPDATE_DATE = (SELECT TO_CHAR(SYSDATE,''YYYYMMDD'') from dual);',

    Let me double check by trying run it here.

    Rgs,
    Breen

  4. #4
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Yep, it runs fine with the '' (two single quotes).

    Rgs,
    Breen.

  5. #5
    Join Date
    Sep 2003
    Location
    Chiang Mai Thailand
    Posts
    14

    Unhappy Can not use Double qoute

    Hi
    When I submit

    VARIABLE JOB NUMBER;
    BEGIN
    DBMS_JOB.SUBMIT(
    :JOB,
    'UPDATE T_DATE_MS SET I_TODAY =
    (SELECT TO_CHAR(SYSDATE,"YYYYMMDD") from dual);',
    SYSDATE,'SYSDATE + 1');
    commit;
    end;

    it still return error
    ORA-06550: line 2, column 25:
    PL/SQL: ORA-00904: "YYYYMMDD": invalid identifier
    ORA-06550: line 1, column 93:
    PL/SQL: SQL Statement ignored
    ORA-06512: at "SYS.DBMS_JOB", line 79
    ORA-06512: at "SYS.DBMS_JOB", line 136
    ORA-06512: at line 2

    You do have any idea. May be I setting somethings wrong.
    Kindly regards,
    Thana p.
    Chiangmai Thailand
    Yim_cm@hotmail.com

  6. #6
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi tanaka,

    Are you sure you are using two single quotes as apposed to double quotes?

    Rgs,
    Breen.

  7. #7
    Join Date
    Sep 2003
    Location
    Chiang Mai Thailand
    Posts
    14

    Question Yes

    Yes I use double quote " not single quote to define date format.

    From your kindness. I would like to send some post card from Thailand.
    Please send me your address to my email.
    Kindly regards,
    Thana p.
    Chiangmai Thailand
    Yim_cm@hotmail.com

  8. #8
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Thana,

    Try it with 2 single quotes ' ' NOT double quotes ".

    ' x 2 not "

    It's difficult to show here but you need the 2 singe ' quotes.

    Hope that makes sense.

    Rgs,
    Breen.

  9. #9
    Join Date
    Sep 2003
    Location
    Chiang Mai Thailand
    Posts
    14

    Post Please

    Please show me your complete sql command.
    Then I can try your command in sqlplus worksheet.
    Kindly regards,
    Thana p.
    Chiangmai Thailand
    Yim_cm@hotmail.com

  10. #10
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Thana,

    Ok, I'm using the procedure iSubmit instead of Submit (so I can

    SQL> create table test_table(test_col date);

    Table created.

    SQL> exec dbms_job.isubmit(101,'update test_table set test_col = (select TO_CHAR
    (SYSDATE,"YYYYMMDD") from dual);',sysdate,'sysdate+1');
    BEGIN dbms_job.isubmit(101,'update test_table set test_col = (select TO_CHAR(SYS
    DATE,"YYYYMMDD") from dual);',sysdate,'sysdate+1'); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 150:
    PL/SQL: ORA-00904: "YYYYMMDD": invalid identifier
    ORA-06550: line 1, column 93:
    PL/SQL: SQL Statement ignored
    ORA-06512: at "SYS.DBMS_JOB", line 79
    ORA-06512: at "SYS.DBMS_JOB", line 136
    ORA-06512: at "SYS.DBMS_JOB", line 96
    ORA-06512: at line 1

    ** This is the error you get using double quotes "

    Now change to 2 single quotes (' x 2)


    SQL> exec dbms_job.isubmit(101,'update test_table set test_col = (select TO_CHAR
    (SYSDATE,''YYYYMMDD'') from dual);',sysdate,'sysdate+1');

    PL/SQL procedure successfully completed.

    SQL>

    It will work (I assume your keyboard is different to mine but you should be using the [@'] key and NOT the ["2] key.)

    Rgs,
    Breen

  11. #11
    Join Date
    Sep 2003
    Location
    Chiang Mai Thailand
    Posts
    14

    Smile Sorry

    Sorry
    It my faults. I so confuse. Then I mis understand about two single qoute and Double qoute.
    Sorry
    Kindly regards,
    Thana p.
    Chiangmai Thailand
    Yim_cm@hotmail.com

  12. #12
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    No problem.

    Good luck,
    Breen.

Posting Permissions

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