ok, great. Thanks!
On Wed, May 13, 2009 at 3:58 PM, ddf <orat...@msn.com> wrote:
>
>
>
> On May 13, 2:40 pm, Joel <johow...@gmail.com> wrote:
> > (oracle 10.2.0.4) is it possible for a stored procedure to dynamically
> > determine what schema it lives in for use with qualifying objects in
> > the current schema?
> >
> > e.g. if the stored procedure SUPER_STORED_PROC lives in the DEV_DATA
> > schema, it would read DEV_DATA into the variable CURRENT_SCHEMA ... if
> > SUPER_STORED_PROC lives in PROD_DATA then it would read PROD_DATA into
> > the variable CURRENT_SCHEMA ...
>
> Believe it or not it can:
>
> SQL> create or replace procedure self_aware(curr_schema in out
> varchar2)
> 2 as
> 3 begin
> 4
> 5 curr_schema:=sys_context('USERENV','CURRENT_SCHEMA');
> 6
> 7 end;
> 8 /
>
> Procedure created.
>
> SQL>
> SQL> grant execute on self_aware to bong;
>
> Grant succeeded.
>
> SQL>
> SQL> declare
> 2 currsch varchar2(80);
> 3 begin
> 4 self_aware(currsch);
> 5
> 6 dbms_output.put_line(currsch);
> 7
> 8 end;
> 9 /
> BING
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> connect *****/#######
> Connected.
> SQL>
> SQL> declare
> 2 currsch varchar2(80);
> 3 begin
> 4 bing.self_aware(currsch);
> 5
> 6 dbms_output.put_line(currsch);
> 7
> 8 end;
> 9 /
> BING
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> Note that CURRENT_SCHEMA is an attribute of the USERENV context, so I
> wouldn't use that as a variable name.
>
>
> David Fitzjarrell
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
