oracle authid cursor

How can a CURSOR inside a stored procedure accept variables passed in as a parameter? We want the cursor to SELECT from any schema we have in the database so we would like to pass in a variable for the schema name and have the cursor use this schema_name when it is defined.   For example how can I get something like this to work:

CREATE PROCEDURE TEST (schema_name_parameter IN varchar2)
authid current_user
as

begin
declare
  variable_junk char(1);
  cursor a
  as
     select junk from schema_name_parameter.table_junk;  

  begin
    open a;
    fetch junk into variable_junk;
 end;

end;
end test;
/
ogmfAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
ogmfConnect With a Mentor Author Commented:
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;
/

Open in new window

0
 
flow01Connect With a Mentor Commented:
CREATE OR REPLACE PROCEDURE dynjunk (schema_name_parameter IN varchar2)
authid current_user
as
begin
  declare
    variable_junk char(1);
  begin
     execute immediate 'select junk from '  || schema_name_parameter || '.table_junk'
       into variable_junk;  
     dbms_output.put_line('variable_junk=' || variable_junk);
  end;
end dynjunk;
/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.