Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle authid cursor

Posted on 2008-10-03
2
Medium Priority
?
920 Views
Last Modified: 2013-12-18
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
Comment
Question by:ogmf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 400 total points
ID: 22638488
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
 

Accepted Solution

by:
ogmf earned 0 total points
ID: 22691176
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question