How to view object definition statements such as tables, stored procedures, triggers, and functions in Oracle.

You can use either of the following methods to view definition statements of objects such as tables, stored procedures, and triggers:

1. query the all_source table

2. Use the DBMS_METADATA Package

1. Use all_source
Table

First, let's check which types of objects can be viewed through the all_source table:

SQL> select distinct type from all_source;

Type

------------

Type Body

Procedure

Type

Function

Trigger

Package body

Package

View the Stored Procedure Definition Statement:

SQL>

Select owner, name, type, text

From all_source

Where type = 'processed'

And owner = 'edw'

And name = 'P _ t01_loan_due_bill ';

View trigger definition statements

SQL> select text from all_source where type = 'trigger' and name = 'trdb _ team ';

The method is also relatively simple, you can modify the type and name, pay attention to uppercase.

Ii. Pass
DBMS_METADATA Package

The Oracle online documentation provides a detailed description of this package:

DBMS_METADATA

Through the get_ddl () method of the DBMS_METADATA package, we can view definition statements such as tables, indexes, views, and stored procedures.

Usage:

SQL> select dbms_metadata.get_ddl ('object type', 'name', 'username ')
From dual;

1.
View table definition statements

SQL> set long 9999999

SQL>

Select dbms_metadata.get_ddl ('table', 'edw _ t01_loan_due_bill ', 'edw ')

From dual;

The returned results may contain some storage attributes, which seem uncomfortable. We can set the session level to not display these storage attributes.

SQL> set long 2000000

SQL> set pagesize 0

SQL>Execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'store', false );

PL/SQL procedure successfully completed

SQL>

Select dbms_metadata.get_ddl ('table', 'edw _ t01_loan_due_bill ', 'edw ')

From dual;

SQL>Execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'default ');

PL/SQL procedure successfully completed

SQL>

2.
View Stored Procedure Definition statements

SQL> set long 99999

SQL>

Select dbms_metadata.get_ddl ('Procedure ', 'P _ t01_loan_due_bill', 'edw ')

From dual;

Other queries are similar to this. You only need to modify the object type.

You can use either of the following methods to view definition statements of objects such as tables, stored procedures, and triggers:

1. query the all_source table

2. Use the DBMS_METADATA Package

1. Use all_source
Table

First, let's check which types of objects can be viewed through the all_source table:

SQL> select distinct type from all_source;

Type

------------

Type Body

Procedure

Type

Function

Trigger

Package body

Package

View the Stored Procedure Definition Statement:

SQL>

Select owner, name, type, text

From all_source

Where type = 'processed'

And owner = 'edw'

And name = 'P _ t01_loan_due_bill ';

View trigger definition statements

SQL> select text from all_source where type = 'trigger' and name = 'trdb _ team ';

The method is also relatively simple, you can modify the type and name, pay attention to uppercase.

Ii. Pass
DBMS_METADATA Package

The Oracle online documentation provides a detailed description of this package:

DBMS_METADATA

Through the get_ddl () method of the DBMS_METADATA package, we can view definition statements such as tables, indexes, views, and stored procedures.

Usage:

SQL> select dbms_metadata.get_ddl ('object type', 'name', 'username ')
From dual;

1.
View table definition statements

SQL> set long 9999999

SQL>

Select dbms_metadata.get_ddl ('table', 'edw _ t01_loan_due_bill ', 'edw ')

From dual;

The returned results may contain some storage attributes, which seem uncomfortable. We can set the session level to not display these storage attributes.

SQL> set long 2000000

SQL> set pagesize 0

SQL>Execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'store', false );

PL/SQL procedure successfully completed

SQL>

Select dbms_metadata.get_ddl ('table', 'edw _ t01_loan_due_bill ', 'edw ')

From dual;

SQL>Execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'default ');

PL/SQL procedure successfully completed

SQL>

2.
View Stored Procedure Definition statements

SQL> set long 99999

SQL>

Select dbms_metadata.get_ddl ('Procedure ', 'P _ t01_loan_due_bill', 'edw ')

From dual;

Other queries are similar to this. You only need to modify the object type.