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;
/
-- 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('VSCH
OPEN REF_CURSOR FOR 'SELECT FIELD1, FIELD2 FROM '||VSCHEMA_NAME||'.TEST_TA
LOOP
FETCH REF_CURSOR INTO CURRENT_REC;
EXIT WHEN REF_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (CURRENT_REC.FIELD1||' '||TO_CHAR(CURRENT_REC.FIE
END LOOP;
END;
/
Open in new window