What SQL would I need to use to list all the stored procedures on an Oracle database?

query to view stored procedure in oracle
find stored procedure containing text in oracle
find all stored procedures that reference a column in table in oracle
how to get list of stored procedures in oracle
list all procedures in oracle
oracle list all functions in a package
sql query to find table used in stored procedures in oracle
how to view function in oracle

What SQL would I need to use to list all the stored procedures on an Oracle database?

If possible I'd like two queries:

  1. list all stored procedures by name
  2. list the code of a stored procedure, given a name

The DBA_OBJECTS view will list the procedures (as well as almost any other object):

SELECT owner, object_name
FROM dba_objects 
WHERE object_type = 'PROCEDURE'

The DBA_SOURCE view will list the lines of source code for a procedure in question:

SELECT line, text
FROM dba_source
WHERE owner = ?
  AND name = ?
  AND type = 'PROCEDURE'

Note: Depending on your privileges, you may not be able to query the DBA_OBJECTS and DBA_SOURCE views. In this case, you can use ALL_OBJECTS and ALL_SOURCE instead. The DBA_ views contain all objects in the database, whereas the ALL_ views contain only those objects that you may access.

List stored procedures in Oracle database, Useful SQL queries for Oracle to explore database schema. Query below lists: ( A) all Procedures accessible to the current user in Oracle database B. If you have privilege on dba_procedures and dba_arguments You can get it in a few minutes and enable your team make better use of your data. Procedures can be stored in the database using Oracle tools such as SQL*Plus. You create the source for the procedure using your text editor, and execute the source using SQL*Plus (for example, with the @ operator). When you input the source, use the CREATE PROCEDURE command.

If you want to get all the calls to introspect stored procedures (parameters, etc), you can pull it out of this open source package:


OraPIG is the Oracle Python Interface Generator. It introspects oracle packages and generates python wrappers for them.

ALL_PROCEDURES, Database Reference. Contents ALL_PROCEDURES. ALL_PROCEDURES lists all functions and procedures, along with associated properties. For example � To list all packages from Oracle database you can query: USER_PROCEDURES, ALL_PROCEDURES, DBA_PROCEDURES, USER_OBJECTS. The most used to show the packages in the database are USER_OBJECTS and USER_PROCEDURES. Privileges may be required to query certain tables or views.

I think enumerating from DBA_OBJECTS may have missed out a lot of procedures: (I am on Oracle 12c, login as SYS)

select count(*) from dba_objects where object_type = 'PROCEDURE';

It really looked impossible that the entire ORACLE database can have only 202 procedures.

And querying from DBA_PROCEDURES:

select owner||'-'||object_name || '-'||procedure_name from

26539 rows selected.

Now focusing on SYS schema, which comes default for every database (not unique to mine):

Querying for ANONYMOUS stored procedures (http://www.praetoriate.com/t_high_perform_calling_procedures.htm) belonging to SYS:

select owner||'-'||object_name || '-'||procedure_name from dba_procedures WHERE PROCEDURE_NAME IS NULL and owner = 'SYS';
994 rows selected.

And SYS's non-anonymous stored procedures have 15K:

select owner||'-'||object_name || '-'||procedure_name from dba_procedures WHERE PROCEDURE_NAME IS NOT NULL and owner = 'SYS';
15408 rows

Accessing Stored Procedures, The procedure declaration adds a parameter list to the PL/SQL block. Only one copy of the procedure needs to be maintained, it is in the database, and it can But, to gain the full power of stored procedures, you should use packages. The Oracle Server always translates to uppercase the names of database objects as� However, with a stored procedure, the definition is on the database. To store a version of the stored procedure, you would need to use a .sql file with the CREATE statement stored inside it, and include that in your version control. You would need to remember to keep that file up to date.

Here is a simpler SQL SELECT * FROM User_Procedures;

5 Using Procedures, Functions, and Packages, You can create, modify, run, and drop stored See "CREATE PROCEDURE" in Oracle Database SQL Reference the name of the saved PL/SQL code that you want to access. Sql Server List Stored Procedures With Schema Frees you can be defined function that sql server list stored procedures with schema view the case it identified several

Coding PL/SQL Procedures and Packages, You can even use PL/SQL for some database applications in place of 3GL The following short example of a PL/SQL anonymous block prints the names of A stored procedure, function, or package is a PL/SQL program unit that: The formal parameters of a procedure have three major attributes, described in Table 7-1. What SQL would I need to use to list all the stored procedures on an Oracle database? If possible I'd like two queries: list all stored procedures by name; list the code of a stored procedure, given a name

3 Ways to List All Stored Procedures in a SQL Server Database , Oracle � PostgreSQL 3 Ways to List All Stored Procedures in a SQL Server Database You can use the ROUTINES information schema view to get a list of all user-defined stored procedures in If you want to return each stored procedure's definition, you can join it with the sys.sql_modules system view. I need to write some sql that will allow me to query all objects in our Oracle database. Unfortunately the tools we are allowed to use don't have this built in. Basically, I need to search all tables, procedures, triggers, views, everything. I know how to search for object names.

View the Dependencies of a Stored Procedure, Learn how to view stored procedure dependencies in SQL Server 2019 (15.x) by to another user, the grantee can view all dependencies in the database. How to View the Dependencies of a Stored Procedure. You can use one of the following: View the list of objects on which the procedure depends. SQL Server 2005. SELECT *FROM sysobjectsWHERE type = 'P'. Note that the output contains many columns that will never relate to a stored procedure. The next set of queries will return all Stored Procedures in the database that include the string 'SearchTerm': SQL Server 2005.