PL/SQL procedure or package owner ? 
Author Message
 PL/SQL procedure or package owner ?
Hi !

Is there a way to determine a package (or procedure) owner from the package
?
I need to write procedure that works by following way: it should always
return it owner (it scheme name by other words).
There is a built-in 'user' procedure but it returns name of user that called
it, not owner name.
Any suggestions ?


--
Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)




Wed, 18 Jun 1902 08:00:00 GMT
 PL/SQL procedure or package owner ?

Why don't you simply hardcode the schema name in the package/procedure
source code ?


Wed, 18 Jun 1902 08:00:00 GMT
 PL/SQL procedure or package owner ?

Quote:
> Why don't you simply hardcode the schema name in the package/procedure
> source code ?

I do this now. But I've got quite much problems distributing my package to
our customers becouse they are using different schema names for our
database. And some of them have several database versions in different
schemas. My package should work in each.

--
Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)




Wed, 18 Jun 1902 08:00:00 GMT
 PL/SQL procedure or package owner ?
[[ This message was both posted and mailed: see
   the 'To' and 'Newsgroups' headers for details. ]]

On Thu, 13 Jan 2000 12:55:27 +0100, Igor V. Podolsky wrote

Quote:
> Hi !

> Is there a way to determine a package (or procedure) owner from the package
> ?
> I need to write procedure that works by following way: it should always
> return it owner (it scheme name by other words).
> There is a built-in 'user' procedure but it returns name of user that called
> it, not owner name.
> Any suggestions ?

How about the callstack ? It has the names of the routine that called and
also the schema that routine belongs to.

--
Ronald.
---------------------------
http://home.wxs.nl/~ronr/RonR.html
The best way to accellerate a computer 'running' windows is by gravitation.



Wed, 18 Jun 1902 08:00:00 GMT
 PL/SQL procedure or package owner ?
Try putting the following query in your PROCEDURE/PACKAGE source code, for
example :

SELECT OWNER INTO VarOwner FROM ALL_OBJECTS WHERE
OBJECT_NAME='YourProcedureName'
AND OBJECT_TYPE='PROCEDURE';

and return the value of VarOwner.



Quote:


> > Why don't you simply hardcode the schema name in the package/procedure
> > source code ?

> I do this now. But I've got quite much problems distributing my package
to
> our customers becouse they are using different schema names for our
> database. And some of them have several database versions in different
> schemas. My package should work in each.

> --
> Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)





Wed, 18 Jun 1902 08:00:00 GMT
 PL/SQL procedure or package owner ?
You can use the userenv function and the all_users view
to dynamicaly know the owner of a procedure:

v734>create or replace procedure p is
  2  ownername all_users.username%type;
  3  begin
  4    dbms_output.enable;
  5    select username into ownername from all_users where
user_id=userenv('SCHEMAID');
  6    dbms_output.put_line('owner is '||ownername);
  7  end;
  8  /

Procedure created.

v734> select user, uid from dual;

USER                                  UID
------------------------------ ----------
MMA$EP34087                            79

v734> set serveroutput on
v734> exec p;
owner is MMA$EP34087

PL/SQL procedure successfully completed.


Enter password: ********
Connected.

v734> select user, uid from dual;

USER                                  UID
------------------------------ ----------
SYSTEM                                  5

v734> set serveroutput on
v734>exec mma$ep34087.p;
owner is MMA$EP34087

PL/SQL procedure successfully completed.

--
Have a nice day
Michel



Quote:
> Hi !

> Is there a way to determine a package (or procedure) owner from the package
> ?
> I need to write procedure that works by following way: it should always
> return it owner (it scheme name by other words).
> There is a built-in 'user' procedure but it returns name of user that called
> it, not owner name.
> Any suggestions ?


> --
> Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)





Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. PL/SQL constants in packages/stored packages

2. OH-Fairlawn-101453--PowerBuilder-PL/SQL-Stored Procedures-Triggers-ORACLE-SQL*Pl

3. OH-Fairlawn-101453--PowerBuilder-PL/SQL-Stored Procedures-Triggers-ORACLE-SQL*Pl

4. OH-Fairlawn-101453--PowerBuilder-PL/SQL-Stored Procedures-Triggers-ORACLE-SQL*Pl

5. DTS Package Owner on SQL Server 7.0 (Urgent)

6. PL/SQL:Efficiency of inline SQL vs use of Dynamic SQL Package

7. PL/SQL Owner

8. PL/SQL vs Generic Dynamic SQL packages

9. Problem: Executing a procedure under a not owner (of procedure) user

10. SQL-92, Migrate PL/SQL at ORACLE into PL/SQL at SQL Server 7.0

11. Forgot DTS Package Owner Password

12. Changing owner of a DTS package


 
Powered by phpBB® Forum Software