Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: creating a 10g oracle job using a PLSQL block

    Hi

    I'm trying to specify the job_action using a PLSQL_BLOCK (for the first time) when creating a DBMS.SCHEDULER job.

    But get an error for the quotes around the argument for pid (and sid for that matter).

    I can see why (because there's single quotes for the PLSQL block but if I put double quotes around the argument for pid, the whole block turns red. Whereas with single quotes it doesn't.

    Could someone please advise?

    Code:
    'BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
     job_name                    => '"JOB1"',
     schedule_name            => 'SCHEDULE1',
     job_type                      => 'PLSQL_BLOCK',
     job_action       => 'BEGIN update Schema.Table set nextdate=trunc(sysdate)-1 where pid='ABC01' sid='100'; END;'
    );
    END;'
    /
    Last edited by shajju; 04-09-14 at 05:13.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You need to use two single quotes (not a double quote), or Q-quote mechanism. Here are both examples:

    Code:
    SELECT 'BEGIN update Schema.Table set nextdate=trunc(sysdate)-1 where pid=''ABC01'' sid=''100''; END;'
              ex_1,
           q'[BEGIN update Schema.Table set nextdate=trunc(sysdate)-1 where pid='ABC01' sid='100'; END;]'
              ex_2
      FROM DUAL

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thank you so much.

Posting Permissions

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