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
-~----------~----~----~----~------~----~------~--~---
