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
DBMS_MVIEW.REFRESH('v_schema.mv_name', 'C', '',TRUE,FALSE,0,0,0,FALSE);
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.
Your procedure should be written ,as the following:
You should execute the procedure with two paramters : v_schema and v_mvname.
CREATE OR REPLACE PROCEDURE UPDT_MVS (v_schema in varchar2,v_mvname in varchar2)
DBMS_MVIEW.REFRESH(v_schema||'.'||v_mvname, 'C', '',TRUE,FALSE,0,0,0,FALSE);
Click Here to Expand Forum to Full Width