Re: PL/SQL Owner

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/01
Message-ID: <3369cb75.12824720@newshost>#1/1

The usernenv function can help you out here.

Try:

select username from all_users where user_id = userenv( 'SCHEMAID' );

If you run that in a compiled stored procedure, it will tell you who owns the procedure. For example:

SQL> create or replace procedure show_owner   2 as
  3 begin

  4     for x in ( select username from all_users
  5                 where user_id = userenv('schemaid') ) loop   
  6        dbms_output.put_line( x.username );
  7     end loop;

  8 end;
  9 /  

Procedure created.  

SQL> show user
user is "TKYTE"
SQL> exec show_owner;
TKYTE   PL/SQL procedure successfully completed.  

SQL> grant execute on show_owner to scott;  

Grant succeeded.  

SQL> connect scott/tiger
Connected.
SQL> set serveroutput on
SQL> exec tkyte.show_owner;
TKYTE   PL/SQL procedure successfully completed.  

SQL> userenv is documented. The 'schemaid' parameter is not. It is used heavily in the data dictionary so that when you query the data dictionary in a stored procedure, it'll return a consistent answer regardless of who executes the procedure.

On Wed, 30 Apr 1997 23:07:57 GMT, "Carolina Power & Light" <andrew.smalera_at_cplc.com> wrote:

>Hi, I'm trying to figure out how I can get a stored procedure to figure out
>who its owner is . I can't count on DBA_SOURCE because there might be
>(will be) more than one stored procedure with the same name in an instance.
> I'm looking through the V$ tables to possibly track it down through the
>process, but haven't figured it out yet. If anyone has any suggestions I
>would appreciate hearing of them. Thanks.
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 01 1997 - 00:00:00 CDT