Oracle: procedures+functions code base not overlapping package body code base

oracle

I intend to find all code in my database containing some string.
I run a query such as:

SELECT DISTINCT name, type FROM all_source WHERE UPPER(text) LIKE UPPER(‘%string%’);

and a lot of results, let's say 100 results, contain 'PACKAGE BODY' on the TYPE column. Because
a package body is too large, I want to get only the name of the procedure/function containing that text.

So I add the following clause

AND TYPE IN ('PROCEDURE', 'FUNCTION')

now i get only a few results, let's say 10 results, and I am confused.

Since a package body can contain only functions and procedures (apart from package constants), if 100 packages contain the text 'abc', how can only 10 procedures contain the same text? Text 'abc' withing a package can only fit within a procedure or a function, so if there are 100 occurences withing packages, there must be at least 100 occurences within functions/procedures.

I would need some explanation of this phenomena and, more importantly,I would like to know how can I narrow the search of text, to get the name of the containing procedure/function, instead of the parent package?

(a colleague suggested that some procedures are only imported, but not compiled; could this be a cause?)

Best Answer

Your search will have to be more complicated. Stored code can be in the package header which is not done that often. Most often it is in PACKAGE BODY. If you modified your query to read

SELECT DISTINCT name, type FROM all_source WHERE UPPER(text) 
LIKE UPPER(‘%string%’) and type ='PACKAGE BODY';

This gives all the code that meets your criteria in package bodies. The line number is all you can get out of all_source. The fields available in all_source are

OWNER VARCHAR2(30)     Owner of the object 
NAME  VARCHAR2(30)     Name of the object 
TYPE  VARCHAR2(12)     Type of object: FUNCTION, JAVA SOURCE, PACKAGE,PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY 
LINE  NUMBER           Line number of this line of source 
TEXT  VARCHAR2(4000)   Text source of the stored object

When you search for type PROCEDURE or FUNCTION you are looking at the stand alone code blocks that are not part of a package.

If the style of coding includes local procedures or functions within procedures or functions in a package body this could get complicated fast. You could look for the mandatory syntax that precedes a code block: PROCEDURE xxx or FUNCTION xx is and the code that ends it END ;

So your search would be:

find all procedure or function names in a package or package body and the line number
find the line number of the text you are looking for
write some logic to find the name of the code block that contains the text

Or you could use a GUI tool like TOAD or SQL Developer which will do this for you.

Related Solutions

Is it possible to define package procedure/function outside of package body block (for Oracle)

You always need to compile the entire package body -- it is one item of code. You can create functions and procedures inside other procedures, but that does not change the need to recompile the package body.

What problem are you trying to solve here?

Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

According to the question, all you need to do is attach a predicate on the query to customer table as shown below.

select * from customer where company_id in(select company_id from user_info where Admin='admin_user');

Right? If yes, you have to create a function which should return a predicate as:

company_id in(select company_id from user_info where Admin='admin_user')

Well, I have done a small demo for your requirement. I have created doramail as an ADMIN user who owns the tables user_info, customer and a function called Hello_Comp_ID.

SQL> conn / as sysdba
SQL> create user doramail identified by doramail;

User Created.

SQL> grant connect, resource to doramail;

Grant succeeded.

SQL> conn doramail/doramail

Connected.

SQL> create table user_info(Company_Id number, Admin varchar2(20));

Table created.

SQL> create table customer(company_id number, name varchar2(20));

Table created.

SQL> insert into user_info values(100, 'MKarki');

1 row created.

SQL> insert into user_info values(200,'VKarki');

1 row created.

SQL> insert into user_info values(300,'CKarki');

1 row created.

SQL> insert into customer values(100,'Joe');

1 row created.

SQL> insert into customer values(200,'Jimmy');

1 row created.

SQL> insert into customer values(300,'Cashy');

1 row created.

SQL> insert into customer values(300,'Tim');

1 row created.

SQL> Commit;

Commit complete.

SQL> select * from user_info;

COMPANY_ID ADMIN                                                                
---------- --------------------                                                 
       100 MKarki                                                               
       200 VKarki                                                               
       300 CKarki                                                               

SQL> select * from customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Ricky                                                                
       100 Joe                                                                  
       200 Jimmy                                                                
       300 Cashy                                                                
       300 Tim  
SQL> Create or Replace FUNCTION Hello_Comp_ID( P_SCHEMA IN varchar2, P_OBJECT IN varchar2) Return varchar2 IS
  2    V_User Varchar2(20);
  3    V_Predicate Varchar2(200);
  4  BEGIN
  5   V_User := SYS_CONTEXT('USERENV','SESSION_USER');
  6    If V_User = 'DORAMAIL' then
  7      V_Predicate := '';
  8    Else
  9     V_Predicate := 'Company_Id in(select company_id from user_info where admin='||q'[']' || V_User || q'[']'||')';
 10    END IF;
 11    RETURN  V_Predicate;
 12  END;
 13  /

Function created.

SQL> exec DBMS_RLS.ADD_POLICY('Doramail','customer','secure_select','doramail','Hello_Comp_ID','SELECT,INDEX');

PL/SQL procedure successfully completed.

SQL> select * from customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Ricky                                                                
       100 Joe                                                                  
       200 Jimmy                                                                
       300 Cashy                                                                
       300 Tim   
SQL> conn / as sysdba
Connected.    
SQL> create user "MKarki" identified by mkarki;

User created.

SQL> grant connect, resource to "MKarki";

Grant succeeded.

SQL> conn doramail/doramail
Connected.
SQL> grant select on customer to "MKarki";

Grant succeeded.

SQL> conn "MKarki"/mkarki
Connected.
SQL> select * from doramail.customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Joe                                                                  
       100 Ricky

Since the user MKarki belongs to company_id 100 he only can see records where company_id is 100.

In your case there might be problem in returned predicate. You can change:

V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']';

To this:

V_Hello:='SYS_CONTEXT('||q'[']' || CompAdmin || q'[']' ||
','|| q'[']' || Company_ID || q'[']' ||')
='||q'[']' || Company_ID || q'[']';

If you are still having trouble then you can view the predicate being attached to your query using explain plan command. And then you can change the predicate(if there are any logical/syntactical errors in the predicate) in the function.

SQL> explain plan for select * from doramail.customer;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2021122684

--------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     2 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |           |     2 |   100 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER  |     5 |   125 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| USER_INFO |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COMPANY_ID"="COMPANY_ID")
   3 - filter("ADMIN"='MKarki')

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

Best Answer

Related Solutions

Is it possible to define package procedure/function outside of package body block (for Oracle)

Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

Related Question