-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|