Home » SQL & PL/SQL » SQL & PL/SQL » Select private procedures from data dictionary (Oracle 10.2g)
Select private procedures from data dictionary [message #331203] Wed, 02 July 2008 10:11 Go to next message
shadbell
Messages: 2
Registered: July 2008
Location: CT
Junior Member
I was wondering if there is a way to select private procedure names from a data dictionary view? Currently I am using the following query to get the public procedures.

-------
SQL> select lower(procedure_name) procedure_name
2 from all_procedures
3 where owner = upper('cadec')
4 and object_name = upper('p_dot_editor')
5 and procedure_name is not null
6 order by subprogram_id;

PROCEDURE_NAME
------------------------------
insert_status
delete_status
merge_status
edit_status
get_neighboring_statuses
get_current_next_status
get_34_days
get_34_days_exclude_keys
get_driver_key_from_ds
apply_arr_violations
delete_per_diem_data
insert_per_diem_data
edit_per_diem_data

13 rows selected
-----

I am also querying for the arguments to those procedures as well. This also only allows me to select public procedures.

-----
SQL> select lower(argument_name) argument_name,
2 upper(data_type) data_type
3 from all_arguments
4 where owner = upper('cadec')
5 and package_name = upper('p_dot_editor')
6 and object_name = upper('insert_status')
7 and in_out like 'IN%'
8 order by position;

ARGUMENT_NAME DATA_TYPE
------------------------------ ------------------------------
i_driver_key NUMBER
i_company_key NUMBER
i_duty_status_type_id NUMBER
i_dot_location_key NUMBER
i_start_datetime TIMESTAMP WITH LOCAL TIME ZONE
i_end_datetime TIMESTAMP WITH LOCAL TIME ZONE
i_remarks VARCHAR2
i_edit_reason VARCHAR2

8 rows selected
-----

I have been searching for quite some time now and would really appreciate it if someone could point me in the right direction if it is possible.

Thanks,
Shad
Re: Select private procedures from data dictionary [message #331206 is a reply to message #331203] Wed, 02 July 2008 10:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since private objects need never to be called from outside the package, Oracle can do with pointers within the package itself when compiling.
There simply is no need for storing them in any data-dictionary. I did some research myself for this (back on release 9) to see if there was a way anyway to get them, but to no avail.
Re: Select private procedures from data dictionary [message #331207 is a reply to message #331203] Wed, 02 July 2008 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is none.
Private procedures (I think you mean private procedures in package) are not visible outside the package.

Regards
Michel
Re: Select private procedures from data dictionary [message #331209 is a reply to message #331203] Wed, 02 July 2008 10:19 Go to previous messageGo to next message
shadbell
Messages: 2
Registered: July 2008
Location: CT
Junior Member
Thanks for the quick responses. I guess I can end my quest.
Re: Select private procedures from data dictionary [message #331215 is a reply to message #331209] Wed, 02 July 2008 11:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a piece of SQL I knocked up to extract the names of all the procedures and functions specified in a package body.
It would be relatively simple to remove publically declared procedures from this list.

select package_name,proc_name
from (
select name package_name 
      ,line
      ,substr(text,1,instr(text,' '))
      ,substr(ltrim(replace(text,substr(text,1,instr(text,' ')),' ')),1,instr(ltrim(replace(text,substr(text,1,instr(text,' ')),' ')),' ')) proc_name
from (
select name,line,ltrim(replace(upper(text),'(',' ')) text
from   user_source 
where  type = 'PACKAGE BODY' 
and   (upper(ltrim(text)) like 'PROCEDURE%' or upper(ltrim(text)) like 'FUNCTION%')));
Previous Topic: Advance Integration Implementation
Next Topic: help with query
Goto Forum:
  


Current Time: Sun Dec 04 00:35:27 CST 2016

Total time taken to generate the page: 0.05746 seconds