oracle authid cursor

To Experts Exchange user "flow01" : the solution you posted did not used a CURSOR where the cursor selected data from any schema depending on the schema passed into the procedure as a variable.  We did figure out how to do it though and we are attempting to post it here in case anyone else has the same issue:

-- This procedure accepts any oracle schema (user) name as an input parameter
-- and then uses that parameter in the definition of a "sys refcursor".  
-- This allows developers to create a stored procedure that can be used to
-- pull data from many different schemas of similar structures.
-- It requires the user to have REFERENCE permission.
CREATE OR REPLACE PROCEDURE TEST (VSCHEMA_NAME IN VARCHAR2)
AUTHID CURRENT_USER
AS
   REF_CURSOR    SYS_REFCURSOR;
   TYPE          REC IS RECORD
     (FIELD1   CHAR(1),
      FIELD2   NUMBER
     )
   ;
   CURRENT_REC   REC;
BEGIN
   DBMS_OUTOUT.PUT_LINE('VSCHEMA_NAME = '||VSCHEMA_NAME);
   OPEN REF_CURSOR FOR 'SELECT FIELD1, FIELD2 FROM '||VSCHEMA_NAME||'.TEST_TABLE';
   LOOP
      FETCH REF_CURSOR INTO CURRENT_REC;
      EXIT WHEN REF_CURSOR%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (CURRENT_REC.FIELD1||'  '||TO_CHAR(CURRENT_REC.FIELD2));
   END LOOP;
END;
/
 

-- This procedure accepts any oracle schema (user) name as an input parameter
-- and then uses that parameter in the definition of a "sys refcursor".  
-- This allows developers to create a stored procedure that can be used to
-- pull data from many different schemas of similar structures.
-- It requires the user to have REFERENCE permission.
 
CREATE OR REPLACE PROCEDURE TEST (VSCHEMA_NAME IN VARCHAR2)
AUTHID CURRENT_USER
AS
 
   REF_CURSOR    SYS_REFCURSOR;
   TYPE          REC IS RECORD
     (FIELD1   CHAR(1),
      FIELD2   NUMBER
     )
   ;
   CURRENT_REC   REC;
 
BEGIN
   DBMS_OUTOUT.PUT_LINE('VSCHEMA_NAME = '||VSCHEMA_NAME);
   OPEN REF_CURSOR FOR 'SELECT FIELD1, FIELD2 FROM '||VSCHEMA_NAME||'.TEST_TABLE';
 
   LOOP
      FETCH REF_CURSOR INTO CURRENT_REC;
      EXIT WHEN REF_CURSOR%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (CURRENT_REC.FIELD1||'  '||TO_CHAR(CURRENT_REC.FIELD2));
   END LOOP;
END;
/

Select all Open in new window