Pass variable value to stored procedure

[traveller]

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.

[nir_s]

Hi traveller,

Your procedure should be written ,as the following:

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:

exec UPDT_MVS('SCOTT','EMP_MV');

Regards,
Nir