Pl/sql block to access v$ views
Hi All,
i am doing this on both 10 g and 11 g but having same issue. I am trying to run below procedure and i am getting the error ORA-00942: table or view does not exist
CREATE OR REPLACE PROCEDURE DB_INFO(V_DB VARCHAR2, CUR1 OUT SYS_REFCURSOR)
AS
v1 varchar2(2000);
BEGIN
v1='SELECT A.DBID, A.NAME, B.VERSION, B.HOST_NAME FROM V$DATABASE@'||V_DB||' A, V$INSTANCE@'||V_DB||' B WHERE A.NAME=B.INSTANCE_NAME';
open cur1 for v1;
end db_info;
Assume this procedure is present in X DB. I have two confusions regarding this:
1) When i run that select statement seprately in sql. It runs fine and give me result if i use any value for v_db.
SELECT A.DBID, A.NAME, B.VERSION, B.HOST_NAME FROM V$DATABASE@X A, V$INSTANCE@X b WHERE A.NAME=B.INSTANCE_NAME;
2) This procedure executes successfully and returned me cursor if i pass any value for v_db except X (DB in which this procedure is present).
If I pass value of v_db as X, it gives me error ORA-00942: table or view does not exist . It shows v$database and v$instance does not exist for same database X in which this procedure is present.
This seems to be strange. I am not sure about what is causing this issue. Please provide me some information regarding that and lemme know on how i can fix this.
All your comments and responses will be greatly appreciated.
Regards
Dev