Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

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;
/
0
ogmf
Asked:
ogmf
2 Solutions
 
flow01Commented:
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
 
ogmfAuthor 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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now