Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor with schema name change
"Linda Lee" <goforticket_at_yahoo.com> wrote in message
news:f901fb80.0309152003.6fd9f659_at_posting.google.com...
> 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.
If you don't put the schema name and leave it alone then the data will
default to the schema name of the user. If that would work for you then I
would do that. Using dynamic sql is going to potentially get you in
trouble.
Jim
Received on Tue Sep 16 2003 - 00:27:20 CDT