How to view object definition statements such as table store trigger functions in Oracle

Sometimes, you can view the attribute statements of objects such as tables, storage, and triggers in the following two ways:

1. query the all_source table

2. Use the DBMS_METADATA Package.

1. Use the 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

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

PROCEDURE

PACKAGE

PACKAGE BODY

LIBRARY

TYPE BODY

TRIGGER

FUNCTION

JAVA SOURCE

TYPE

From the above results, we can see that we can query objects through this table.

View the Stored Procedure Definition Statement:

SQL> SELECT text FROM ALL_SOURCE where TYPE = 'processed' AND NAME = 'addcustbuss ';

TEXT

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

PROCEDURE addcustbuss (

Acustid IN custbuss. custid % TYPE,

Bussname IN custbuss. businessname % TYPE,

Aopid IN custbuss. opid % TYPE,

Acreatetime IN custbuss. createtime % TYPE,

ACustTel IN custbuss. CustTel % TYPE, -- customer phone number

AContact IN custbuss. Contact % TYPE, -- Contact

AFeedback IN custbuss. Feedback % TYPE, -- customer Feedback

Asid OUT custbuss. ID % TYPE,

RESULT OUT INTEGER

)

IS

BEGIN

RESULT: =-1;

SELECT getarea | TO_CHAR (idseq. NEXTVAL, 'fm099999999999 ')

INTO asid

From dual;

Insert into custbuss

(ID, custid, businessname, opid, createtime, CustTel, Contact, Feedback

)

VALUES (asid, acustid, bussname, aopid, acreatetime, aCustTel, aContact, aFeedback

);

RESULT: = 0;

EXCEPTION

WHEN OTHERS

THEN

RESULT: =-1;

END addcustbuss;

32 rows have been selected.

SQL>

View trigger definition statements

SQL> SELECT text FROM ALL_SOURCE where TYPE = 'trigger' AND NAME = 'trdb _ team ';

TEXT

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

TRIGGER "NEWCCS". trdb_team

BEFORE DELETE

ON team

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

BEGIN

Delete from team_teamgroup_map

WHERE teamid =: OLD. teamid;

END trdb_team;

10 rows have been selected.

The method is also relatively simple. You can modify the TYPE and NAME. Note that uppercase is required.

1 2 Next Page