Pass variable value to stored procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Pass variable value to stored procedure

  1. #1
    Join Date
    Jan 2006
    Posts
    39

    Pass variable value to stored procedure

    I have created some materialized views in DB. Now I want to use stored procedures to refresh MVS. I use:

    CREATE OR REPLACE PROCEDURE UPDT_MVS
    IS
    v_schema varchar2(30);
    v_mvname_one varchar2(50);
    v_mvname_two varchar2(50);
    BEGIN
    DBMS_MVIEW.REFRESH('v_schema.mv_name', 'C', '',TRUE,FALSE,0,0,0,FALSE);
    END;

    However, due to the web application needs. I have to dynamically insert mv_name into above string. So I use variables to
    get hold of schema name, materialized view name as:

    " select distinct username into v_schema from v$session;", "select vlue into v_mvname_one from reference;".

    When I compile the procedure, it parse successfully. When I run procedure, it always come out ORA-23401 error that syas
    v_schema.vmvname_one does not exist.

    It seems to me that variables value couldn't be passed into executing string. Please help with your advice. Thanks.

  2. #2
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi traveller,

    Your procedure should be written ,as the following:

    Code:
    CREATE OR REPLACE PROCEDURE UPDT_MVS (v_schema in varchar2,v_mvname in varchar2)
    IS
    BEGIN
      DBMS_MVIEW.REFRESH(v_schema||'.'||v_mvname, 'C', '',TRUE,FALSE,0,0,0,FALSE);
    END;
    /
    You should execute the procedure with two paramters : v_schema and v_mvname.
    For example:

    Code:
    exec UPDT_MVS('SCOTT','EMP_MV');
    Regards,
    Nir

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