Tuesday 8 February 2011

Get database object creation script (DDL) from sqlplus

First method using DBMS_METADATA database package:
select DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME) from dual;

Example:

select DBMS_METADATA.GET_DDL('TABLE', 'EMP') from dual;
select DBMS_METADATA.GET_DDL('PROCEDURE', 'AP_CHECKS_ALL_ADP') from dual;
  Second method using database view called USER_SOURCE:
Note: This method just used for the code objects like procedure, package and so on.
select text from USER_SOURCE where name=OBJECT_NAME order by line asc;

Example:

select text from USER_SOURCE where upper(name)='AP_CHECKS_ALL_ADP' order by line asc;
 

No comments:

Post a Comment

Number of Visitors