Solved

oracle authid cursor

Posted on 2008-10-03
2
912 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 115
clob to char in oracle 3 61
PL SQL Search Across Columns 4 52
Error querying database link from Oracle 10g to Postgresql 7 63
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

791 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