Which data dictionary views gives you the names and the source code of all the procedures that you have created?

This is an excerpt from the book PL/SQL: The Definitive Reference by Boobal Ganesan.

The Oracle database allows numerous objects like tables, views, procedures, functions, packages, and much more to be created in its database. The easiest way to find, manage and validate any object and its associated dependencies is through the Oracle supplied data dictionary objects. These objects acts as a repository for holding information about the database itself. The data dictionary objects are generally tables and views storing data like any other table and view in the database. These objects in a database are stored in the system tablespace in its database.

The data dictionary tables stores the information about the entire database and they should be accessed only by Oracle as these are normalized and the data are encrypted.

The data dictionary views are created with information in understandable format from the data dictionary tables. The users are advised to use these views rather than their underlying tables.

These objects are owned by the user SYS and no user should modify these objects in the SYS schema as any possible change in these objects could disrupt the normal functioning of the Oracle database.

The data dictionary views consists of sets of three views for each underlying base table with different scope. They are

1.       The total list of available data dictionary objects can be found by querying the below select statement.

Select * from dict[ionary];

This query results into information with two columns, one containing the table/ view name and the other column containing its comments.

2.       The total list of available objects in the current schema can be retrieved by querying the below query.

SELECT object_name,

  object_type,

  created,

  last_ddl_time,

  status

FROM user_objects

WHERE object_name IN ('EMP','FUNC_MATH','PROC_CUBE');

The information retrieved from the above query helps in understanding the type of the object we are dealing with, the date of creation of the object, the date in which the object is last modified and its status.

3.       To find the total list of arguments for a particular subprogram, the below statement can be queried for the desired result.

SELECT object_name,

  argument_name,

  data_type,

  defaulted,

  in_out,

  data_length

FROM user_arguments

WHERE object_name IN ('FUNC_MATH','PROC_CUBE');

The above query returns the total list of the arguments and its data type along with its precision the subprogram holds with other information like whether the argument is defaulted or not along with its mode. In the above result set table, for the function FUNC_MATH, one parameter with the data type varchar2 remains Null. The reason is this argument corresponds to the return statement. All the functions will have one argument with its name and its length Null in this data dictionary view.

The data dictionary view to list down the subprogram related features can be queried using the below select statement.

SELECT object_name,

  object_type,

  pipelined,

  parallel,

  interface,

  deterministic,

  authid

FROM user_procedures

WHERE object_name IN ('FUNC_MATH','PROC_CUBE');

The result set shows the attributes of the parameters in the form of flags. If the function's attributes like pipelining, deterministic and parallel execution are enabled, their flags turn YES in the data dictionary view. If the subprograms have any interfaces to external programs, the INTERFACE column flags them to the user. Finally, the authid of the subprogram is indicated in the AUTHID column.

4.       The source code of the subprograms can be retrieved by querying this data dictionary view as shown below.

SELECT * FROM user_source WHERE name = 'PROC_CUBE';

The result set shows the type of the object and its source code along with its line numbers from the data dictionary view.

5.       The dependencies of a subprogram can be retrieved by querying the below data dictionary view query.

SELECT name,

  type,

  referenced_owner,

  referenced_name,

  referenced_type,

  dependency_type

FROM user_dependencies

WHERE name ='PKG_EMP';

The above result set shows the objects which are dependent to the inquired object/ subprogram and its dependency type.

Oracle Training from Don Burleson  The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!