Solved

oracle authid cursor

Posted on 2008-10-03
2
902 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
2 Comments
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 100 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now