Oracle Tips and Tricks — David Fitzjarrell

September 23, 2010

"Confidential" Sources

Filed under: General — dfitzjarrell @ 07:11
Tags:

As I’ve mentioned before some of the more interesting questions come out of the google.com search engine, like this one:


oracle user_source returns nothing

Okay, that’s really, really brief but there is a valid question therein — “What does ‘no rows selected’ mean when querying USER_SOURCE?’ To answer that one we need to discuss the _SOURCE views and what they contain.

There are three views in an Oracle database that list the source text of objects such as packages, procedures, types, triggers and functions: DBA_SOURCE, ALL_SOURCE and USER_SOURCE. These are listed in order of decreasing scope where DBA_SOURCE contains source for every procedure, etc. from every user and is accessible only to DBA-privileged accounts. ALL_SOURCE contains source for all objects the currently connected user can access, whether or not that user owns any of the objects. USER_SOURCE, the most restrictive of the three, lists source for only those objects the connected user owns. Describing ALL_SOURCE reveals the same view structure as DBA_SOURCE; the definition of the view makes the difference. USER_SOURCE looks almost like ALL_SOURCE; the one difference is that the OWNER column is missing. The listing for ALL_SOURCE is shown below:

SQL> desc all_source
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

Let’s now look at the output from USER_SOURCE for the connected user:

NAME            TYPE               LINE TEXT
--------------- ------------ ---------- --------------------------------------------------------------------------------
COND_INSRT      PROCEDURE             1 procedure cond_insrt(p_empno IN varchar2)
COND_INSRT      PROCEDURE             2 is
COND_INSRT      PROCEDURE             3         v_empname emp.empname%type;
COND_INSRT      PROCEDURE             4 begin
COND_INSRT      PROCEDURE             5         select empname into v_empname from table_1 where empno = p_empno;
COND_INSRT      PROCEDURE             6
COND_INSRT      PROCEDURE             7         if v_empname is not null then
COND_INSRT      PROCEDURE             8                 insert into table_2 (empname, empno)
COND_INSRT      PROCEDURE             9                 values (v_empname, p_empno);
COND_INSRT      PROCEDURE            10         end if;
COND_INSRT      PROCEDURE            11 exception

NAME            TYPE               LINE TEXT
--------------- ------------ ---------- --------------------------------------------------------------------------------
COND_INSRT      PROCEDURE            12         when no_data_found then
COND_INSRT      PROCEDURE            13                 insert into table_1 (empname, empno)
COND_INSRT      PROCEDURE            14                 values ('AAAA', p_empno);
COND_INSRT      PROCEDURE            15         when others then
COND_INSRT      PROCEDURE            16             dbms_output.put_line('Displaying the error stack:');
COND_INSRT      PROCEDURE            17             dbms_output.put(dbms_utility.format_error_stack);
COND_INSRT      PROCEDURE            18             dbms_output.put_line(dbms_utility.format_error_backtrace);
COND_INSRT      PROCEDURE            19 end;
SPELL_NUMBER    FUNCTION              1 function spell_number( p_number in number )
SPELL_NUMBER    FUNCTION              2 return varchar2
SPELL_NUMBER    FUNCTION              3 as

NAME            TYPE               LINE TEXT
--------------- ------------ ---------- --------------------------------------------------------------------------------
SPELL_NUMBER    FUNCTION              4     type myArray is table of varchar2(255);
SPELL_NUMBER    FUNCTION              5     l_str    myArray := myArray( '',
SPELL_NUMBER    FUNCTION              6                            ' thousand ', ' million ',
SPELL_NUMBER    FUNCTION              7                            ' billion ', ' trillion ',
SPELL_NUMBER    FUNCTION              8                            ' quadrillion ', ' quintillion ',
SPELL_NUMBER    FUNCTION              9                            ' sextillion ', ' septillion ',
SPELL_NUMBER    FUNCTION             10                            ' octillion ', ' nonillion ',
SPELL_NUMBER    FUNCTION             11                            ' decillion ', ' undecillion ',
SPELL_NUMBER    FUNCTION             12                            ' duodecillion ' );
SPELL_NUMBER    FUNCTION             13
SPELL_NUMBER    FUNCTION             14     l_num   varchar2(50) default trunc( p_number );

NAME            TYPE               LINE TEXT
--------------- ------------ ---------- --------------------------------------------------------------------------------
SPELL_NUMBER    FUNCTION             15     l_return varchar2(4000);
SPELL_NUMBER    FUNCTION             16 begin
SPELL_NUMBER    FUNCTION             17     for i in 1 .. l_str.count
SPELL_NUMBER    FUNCTION             18     loop
SPELL_NUMBER    FUNCTION             19         exit when l_num is null;
SPELL_NUMBER    FUNCTION             20
SPELL_NUMBER    FUNCTION             21         if ( substr(l_num, length(l_num)-2, 3)  0 )
SPELL_NUMBER    FUNCTION             22         then
SPELL_NUMBER    FUNCTION             23            l_return := to_char(
SPELL_NUMBER    FUNCTION             24                            to_date(
SPELL_NUMBER    FUNCTION             25                             substr(l_num, length(l_num)-2, 3),

NAME            TYPE               LINE TEXT
--------------- ------------ ---------- --------------------------------------------------------------------------------
SPELL_NUMBER    FUNCTION             26                               'J' ),
SPELL_NUMBER    FUNCTION             27                        'Jsp' ) || l_str(i) || l_return;
SPELL_NUMBER    FUNCTION             28         end if;
SPELL_NUMBER    FUNCTION             29         l_num := substr( l_num, 1, length(l_num)-3 );
SPELL_NUMBER    FUNCTION             30     end loop;
SPELL_NUMBER    FUNCTION             31
SPELL_NUMBER    FUNCTION             32     return l_return;
SPELL_NUMBER    FUNCTION             33 end;

52 rows selected.

This user owns two functions, COND_INSRT and SPELL_NUMBER; the name and type are repeated for each line of source code each distinct object has.

[As a side note I found this from google.com:

“oracle user can’t execute own function”

which can never be true for valid functions; if the user can successfully create a function without errors then he or she can execute it.]

It’s quite likely an application owner will have hundreds of distinct objects listed; it’s also quite likely that a user may own nothing, not even the tables he or she uses. In such cases a query against USER_SOURCE will return the glorious message indicating the view is empty:

SQL> select * from user_source;

no rows selected

SQL>

“But, I can execute procedures, functions and packages; don’t I own them?” In a word, no, which doesn’t mean you can’t execute other users procedures, packages and functions. Query ALL_SOURCE in place of USER_SOURCE and you’ll see whose code you can execute; note that you have been granted execute on such things by the owner else you’d not likely be able to access them much less execute them (DBA-privileged accounts are different as they have execute any procedure privilege, among others). And there are most likely synonyms created to ‘hide’ the ownership of those procedures, packages and functions so you can call them by name and not raise an error (query USER_SYNONYMS or ALL_SYNONYMS to verify that).

It’s not a problem to not own anything executable in a database, meaning it’s okay to get


no rows selected

when querying USER_SOURCE. But it’s also nice to know who owns those packages, procedures, triggers, functions, etc. you use on a daily basis. Since you now know how to find that information you can probably rest easier at night. I know that I do.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: