Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    7

    Unanswered: refresh Materialized view

    I have getting error while executing the following code

    begin
    dbms_scheduler.create_job
    (
    job_name => 'JB_REFRESH_MV',
    JOB_TYPE => 'STORED_PROCEDURE',
    JOB_ACTION => 'exec dbms_mview.refresh( 'MV_EMPLOYEE_NAME', ''C'' )',
    START_DATE => trunc(sysdate)+18/24,
    REPEAT_INTERVAL => 'FREQ=MINUTELY; interval=1',
    END_DATE => NULL,
    ENABLED => TRUE,
    COMMENTS => 'This jon runs every week on a saturday and would refresh the materialized view.'
    );
    end;

    I need to set the job action to execute dbms_mview.refresh. Coulld anybody point out the problem

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    And the error message is?

  3. #3
    Join Date
    Sep 2011
    Posts
    7
    PLS-00103: Encountered the symbol "MV_EMPLOYEE_NAME" when expecting one of the following

    ) , * & = - + < / > at in is mod remainder not rem
    <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_

    LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
    The symbol ", was inserted before "MV_EMPLOYEE_NAME" to continue.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >JOB_ACTION => 'exec dbms_mview.refresh( 'MV_EMPLOYEE_NAME', ''C'' )',
    what characters are before & after rightmost C above?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2011
    Posts
    7
    even if i remove the single quotes i get the error

    JOB_ACTION => 'exec dbms_mview.refresh( 'MV_EMPLOYEE_NAME', 'C' )',

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ranjit.menon View Post
    even if i remove the single quotes i get the error

    JOB_ACTION => 'exec dbms_mview.refresh( 'MV_EMPLOYEE_NAME', 'C' )',
    You need to use two quotes because they are embedded in another string:

    Code:
    JOB_ACTION => 'exec dbms_mview.refresh( ''MV_EMPLOYEE_NAME'', 'C'' )',
    Note those are two single quotes, not a double quote.

    (And please use [code] tags in the future when posting SQL statements)

Posting Permissions

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