Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor with schema name change
Linda Lee wrote:
> I have two procedures that use a cursor to get data from two tables of
> same structure, but different accounts:
>
> create or replace procedure test1 as
> cursor c1
> select firstname from USER1.emp
> where dept = '01';
> begin
> for l in c1 loop
> ...
> end loop;
> end;
>
> create or replace procedure test2 as
> cursor c1
> select firstname from USER2.emp
> where dept = '01';
> begin
> for l in c1 loop
> ...
> end loop;
> end;
>
> How can I write one procedure to do the same thing, and pass the
> schema name(USER1 or USER2) as a variable to call the procedure?
>
> Thank you very much for your help.
A possible solution is to use dynamic sql using the dbms_sql package
and sys_context('userenv', 'session_user').
l_cur# := dbms_sql.open_cursor ;
l_statement := ... bild the statement...
dbms_sql.parse(l_cur#, l_statement...);
.....
rgds
/Svend Jensen Received on Tue Sep 16 2003 - 15:52:37 CDT