Thursday 20 September 2018

How to take script for schema of the tables,View,Package,Type,Trigger and stored procedures of Oracle through SQL Developer tool (SQLPLUS command line interface)?

How to take script for schema of the tables,View,Package,Type,Trigger and stored procedures of Oracle through SQL Developer tool (SQLPLUS command line interface)?

Generate DDL scripts using DBMS_METADATA.GET_DDL

First Set the SQL Developer Output Format of the Script Using Following Command.

SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SET PAGESIZE 205
SET LONG 1000000

SET SQLFORMAT ANSICONSOLE

Then Write the DBMS_METADATA.GET_DDL script like this.

SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;

Following example to get table structure of 
Object Type   :  TABLE 
Object Name :  XX_CAT_ERROR 
Object Owner: APPS

Spool  is set the local Directory of the PC/Laptop to stored the Written Script locally.  


spool C:\DANISH\TABLE\XX_CAT_ERROR.sql;
select dbms_metadata.get_ddl('TABLE','XX_CAT_ERROR','APPS')XX_CAT_ERROR from dual;

Following example to get Synonym structure of 
spool C:\DANISH\SYNONYM\XXBEX_PRO_BUDGET_MINOR_SEQ.sql;
select dbms_metadata.get_ddl('SYNONYM','XXBEX_PRO_BUDGET_MINOR_SEQ','PUBLIC')XXBEX_PRO_BUDGET_MINOR_SEQ from dual;

Following example to get Package structure of 
spool C:\DANISH\PACKAGE\XXBEX_UPDATE_FSD_FORM.sql;
select dbms_metadata.get_ddl('PACKAGE','XXBEX_UPDATE_FSD_FORM','APPS')XXBEX_UPDATE_FSD_FORM from dual;

like this we can generate the 
Procedure, View and Trigger.

No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)

When attempting to apply for a job in iRecruitment, the following error occurs: ERROR: You must enter an Application Number. Solution: How t...