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.
0 comments:
Post a Comment