Which table or view keep function or procedure name of any package?

Which table or view keep function or procedure name of any package?

Example

SQL> desc dbms_alert
Element Type
------------ ---------
MAXWAIT CONSTANT
SET_DEFAULTS PROCEDURE
REGISTER PROCEDURE
REMOVE PROCEDURE
REMOVEALL PROCEDURE
WAITANY PROCEDURE
WAITONE PROCEDURE
SIGNAL PROCEDURE

the name of element i want

select object_name from obj
where object_type in('PROCEDURE','FUNCTION', 'PACKAGE');

Hope this resolve your problem.

:) ;) :)

thank you. Mr.Kumar_RP

but it 's not that i want.
which table can show subpackage to me.
such as i describe package. it show subpackage.

Hi,
Do you want to see the script of the Package / Function / Procedure ?.

thanks,
no,i don't.
I would like to select object_name,parameter name,data type and return datatype (if function) of package. which view can i select?

Code:

---

SELECT
  object_name AS element,
  DECODE(MIN(position), 0, 'FUNCTION', 'PROCEDURE') AS type
FROM all_arguments
WHERE package_name = 'DBMS_ALERT' AND owner = 'SYS'
GROUP BY object_name;

---

Code:

---

SELECT a.object_name, argument_name, data_type,
      CASE
          WHEN (object_type = 'FUNCTION' AND in_out = 'OUT') THEN 'RETURN'
          ELSE in_out
      END CASE
FROM user_arguments a, user_objects b
WHERE a.object_name = b.object_name;

---

Hi,
You can your details by DESCRIBE right?.

Ex.
My procedure name is WHO_CALLED_ME;

SQL> describe WHO_CALLED_ME;
PROCEDURE WHO_CALLED_ME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 OUT
NAME VARCHAR2 OUT
LINENO NUMBER OUT
CALLER_T VARCHAR2 OUT


Is it ok for you?>>

thank you. mr.jmodic and mr.Sameer, that i want.
and thank you mr.Kumar_RP for your suggest.

I have more question. Can i find constant or variable?

SQL> desc dbms_alert
Element Type
------------ ---------
MAXWAIT CONSTANT
SET_DEFAULTS PROCEDURE
REGISTER PROCEDURE
REMOVE PROCEDURE
REMOVEALL PROCEDURE
WAITANY PROCEDURE
WAITONE PROCEDURE
SIGNAL PROCEDURE

Not that I'm aware of. Which version of database and SQL*Plus (or whatever tool you are using for DESCRIBE command) are you using? At least on 8.1.7 and above, even SQL*Plus's DESCRIBE doesn't show any packaged variables or constants. I suspect you are using some very old release of SQL*Plus if you get the output of the DESCRIBE command in the format you provided.

AFAIK, only packaged procedures and functions (and their arguments) are included in ALL_/USER_ARGUMENTS - no global variables, constants, types etc... So I have no idea where could one get theose, appart from looking at the source (*_SOURCE view).

And BTW, Sameer, why do you join ALL_ARGUMENTS to ALL_OBJECTS? I think your query will not return anything, as OBJECT_NAME in ALL_ARGUMENTS holds packaged function/procedure names, which on the other hand do not exist in ALL_OBJECTS, because those are not standalone objects! So unless you have created standalone functions/procedures that have the same names as packaged functions/procedures your query will return nothing. Or am I wrong?

Quote:

---

Originally posted by jmodic

And BTW, Sameer, why do you join ALL_ARGUMENTS to ALL_OBJECTS? I think your query will not return anything, as OBJECT_NAME in ALL_ARGUMENTS holds packaged function/procedure names, which on the other hand do not exist in ALL_OBJECTS, because those are not standalone objects! So unless you have created standalone functions/procedures that have the same names as packaged functions/procedures your query will return nothing. Or am I wrong?

---

Thanks for correction. I joined USER_ARGUMENTS to USER_OBJECTS. It skip from my mind that it doesn't store information for procedure/functions inside the packages as those are not stand alone objects, so USER_OBJECTS won't get any record for that. Sorry about that :-(

How can you be wrong :)

Sameer