Home » SQL & PL/SQL » SQL & PL/SQL » list stored procedures
list stored procedures [message #122232] Sat, 04 June 2005 02:28 Go to next message
sneha_84
Messages: 21
Registered: May 2005
Junior Member
How to know available stored procedures list in oracle DB

like select * from all_triggers;
Re: list stored procedures [message #122236 is a reply to message #122232] Sat, 04 June 2005 03:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
select * from all_procedures;
Re: list stored procedures [message #122237 is a reply to message #122236] Sat, 04 June 2005 03:38 Go to previous messageGo to next message
sneha_84
Messages: 21
Registered: May 2005
Junior Member
select * from all_procedures;

It is wrong such table is not exists in sys. or any users context.

Please comment with correct answer
Re: list stored procedures [message #122240 is a reply to message #122236] Sat, 04 June 2005 04:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You did not say what version of Oracle you are using. If you are using a currently supported version as you should be, then there is an all_procedures. If you are using some outdated, desupported version, then you can query from all_source.

The following is a link to a section of the online documentation about all_procedures:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2109.htm#1299488

And, the following shows that such all_procedures and others exist:

scott@ORA92> SELECT banner FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

scott@ORA92> DESCRIBE all_procedures
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 OBJECT_NAME                                           NOT NULL VARCHAR2(30)
 PROCEDURE_NAME                                                 VARCHAR2(30)
 AGGREGATE                                                      VARCHAR2(3)
 PIPELINED                                                      VARCHAR2(3)
 IMPLTYPEOWNER                                                  VARCHAR2(30)
 IMPLTYPENAME                                                   VARCHAR2(30)
 PARALLEL                                                       VARCHAR2(3)
 INTERFACE                                                      VARCHAR2(3)
 DETERMINISTIC                                                  VARCHAR2(3)
 AUTHID                                                         VARCHAR2(12)

scott@ORA92> DESCRIBE dba_procedures
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 OBJECT_NAME                                           NOT NULL VARCHAR2(30)
 PROCEDURE_NAME                                                 VARCHAR2(30)
 AGGREGATE                                                      VARCHAR2(3)
 PIPELINED                                                      VARCHAR2(3)
 IMPLTYPEOWNER                                                  VARCHAR2(30)
 IMPLTYPENAME                                                   VARCHAR2(30)
 PARALLEL                                                       VARCHAR2(3)
 INTERFACE                                                      VARCHAR2(3)
 DETERMINISTIC                                                  VARCHAR2(3)
 AUTHID                                                         VARCHAR2(12)

scott@ORA92> DESCRIBE user_procedures
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OBJECT_NAME                                           NOT NULL VARCHAR2(30)
 PROCEDURE_NAME                                                 VARCHAR2(30)
 AGGREGATE                                                      VARCHAR2(3)
 PIPELINED                                                      VARCHAR2(3)
 IMPLTYPEOWNER                                                  VARCHAR2(30)
 IMPLTYPENAME                                                   VARCHAR2(30)
 PARALLEL                                                       VARCHAR2(3)
 INTERFACE                                                      VARCHAR2(3)
 DETERMINISTIC                                                  VARCHAR2(3)
 AUTHID                                                         VARCHAR2(12)

scott@ORA92> DESCRIBE all_source
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                          VARCHAR2(30)
 NAME                                                           VARCHAR2(30)
 TYPE                                                           VARCHAR2(12)
 LINE                                                           NUMBER
 TEXT                                                           VARCHAR2(4000)

scott@ORA92> DESCRIBE dba_source
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                          VARCHAR2(30)
 NAME                                                           VARCHAR2(30)
 TYPE                                                           VARCHAR2(12)
 LINE                                                           NUMBER
 TEXT                                                           VARCHAR2(4000)

scott@ORA92> DESCRIBE user_source
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 NAME                                                           VARCHAR2(30)
 TYPE                                                           VARCHAR2(12)
 LINE                                                           NUMBER
 TEXT                                                           VARCHAR2(4000)

scott@ORA92>



Re: list stored procedures [message #122255 is a reply to message #122240] Sat, 04 June 2005 07:02 Go to previous messageGo to next message
sneha_84
Messages: 21
Registered: May 2005
Junior Member
I am using Oracle8i It does not have the above said tables.
Re: list stored procedures [message #122259 is a reply to message #122255] Sat, 04 June 2005 08:09 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
This is a documentation question. The 8i data dictionary tables are documented in the online Oracle8i Reference. The USER/DBA/ALL_SOURCE views have existed since version 7. USER_PROCEDURES was added in 9i. If you use a version of Oracle from 6 years ago, you should bear in mind that there might possibly have been changes in the two major releases since then.

USER_ARGUMENTS was also available back in 8i.
Re: list stored procedures [message #233670 is a reply to message #122240] Fri, 27 April 2007 02:36 Go to previous message
amarlow
Messages: 3
Registered: April 2007
Junior Member
Barbara Boehmer wrote on Sat, 04 June 2005 04:16
You did not say what version of Oracle you are using. If you are using a currently supported version as you should be, then there is an all_procedures. If you are using some outdated, desupported version, then you can query from all_source.

I am using Oracle8I also. This is not out of choice. I know that Oracle 8 is no longer supported but you would be suprised (nay, horrified) how many commercial development environments use obsolete/unsupported tools. So I am trying to track down this information as well.

Previous Topic: Row as Column
Next Topic: How to tune the query with BETWEEN but no index
Goto Forum:
  


Current Time: Thu Dec 08 06:40:31 CST 2016

Total time taken to generate the page: 0.10688 seconds