Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/
J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Wednesday April 11, 2007
12:14 PM

Oracle package ownership

[ #32966 ]

I need to figure out how to determine, from within code in a PL/SQL package, the owner of the package. The Oracle USER function returns the userid of the user running the session. Since Oracle uses a definer-rights system, where running code executes as the code's owner instead of the code's executer (unless otherwise specified), I thought I could just include a call to USER and would receive the name of the owner. This does not appear to work, at least for a standalone function on the server (although I'll be trying within the package itself in a minute).


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Oracle package ownership 6 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • All source code is stored, line by line, in a system table called all_source. Try

    select distinct owner from all_source where type='PACKAGE BODY' and name='PACKAGE_NAME'
    (where you replace PACKAGE_NAME by the real name of your package) and you'll probably be closer to home. If you're allowed to touch that source.

    Well, you probably can work something out from there.

    • But the problem I was trying to resolve involved an identical package in two different schemas, which in that solution would result in duplicate results from ALL_SOURCE, and leave me with the same conundrum.

      FWIW, there's a record in ALL_SOURCE for every line of code. A quicker query to get the same result would be FROM all_objects WHERE object_type = 'PACKAGE'

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
      • I knew you'd find a solution once you were pointed in the right direction! :)

        One uses what one has used before, and I had used ALL_SOURCE (and USER_SOURCE) but not the many other stuff in a similar vein. In the meantime, I've played a little with other options, and I found that

        • If you decide to use ALL_SOURCE, it's better to select on type='PACKAGE' instead of type='PACKAGE BODY', because the latter only shows items you have write access to (usually just your own packages), while the former shows you all

  • I wasn't able to reply earlier so I created a journal entry of my own with the same title: Oracle Package Ownership [perl.org].

    • Thank you!! I believe SYSCONTEXT is the solution I was looking for (although I was able to synthesize another workaround this afternoon, but it still didn't solve this problem in the general case).

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.