Skip to main content

Get DDL statements for Objects in Oracle

Advanced SQL IDEs such as TOAD, SQL Developer provide ways to view DDL statements for objects. There is another way you can accomplish the task using GET_DDL method defined in DBMS_METADATA package. See usage below.


Usage : dbms_metadata.get_ddl('Object Type','Object Name','Owner')

SELECT dbms_metadata.get_ddl('FUNCTION','GEN_NUMBERS','SUSIL') FROM dual;

Output:

CREATE OR REPLACE
FUNCTION "SUSIL"."GEN_NUMBERS"(
n IN NUMBER DEFAULT NULL)
RETURN array PIPELINED
AS
BEGIN
FOR i IN 1 .. NVL(n,999999999)
LOOP
pipe row(i);
END LOOP;
RETURN;
END;



SELECT dbms_metadata.get_ddl('TABLE','EMP','SUSIL') FROM dual;

Output:

CREATE TABLE "SUSIL"."EMP"
(
"ENAME" VARCHAR2(10),
"SAL" NUMBER(7,2)
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USER_TS" ;


Note : You cannot view the output if the code is wrapped up.

Comments

Post a Comment

Popular posts from this blog

SQL to return records in m,n sequence

I want my SQL to return only records from 101 to 200. See below options which would do the required.. Select * from (Select rownum rownumber,employee.* from employee) Where rownumber between 101 and 200; -- Using rownum label in oracle Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between 101 and 200; --Using the analytical function row_number Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between :Fromnumber and :tonumber; -- lets user decide the recordset chunk... Hope this is helpful!!!!!!
Post a Comment
Read more

PLSQL programming - Part 1

Pre-requisite : Basic SQL Knowledge in Oracle and programming knowledge in any language Browse my blog to read the overview of PLSQL if you need it. Anonymous block ............. ........... This is where you declare Variables that you are going to use it in PLSQL block ............ Data types are chosen based on requirement. For example, if you want to process strings you go for data types of characters and for numeric operations Int, float , double or number. Begins ..... ......... ......... .......... This is where you implement programming logic and get the desired outcome. .......... .......... Ends..... We call it anonymous block because we don't name it or reference it. (Unlike Package, Stored procedure,& Other objects) Learn from examples Example 1: declare var1_num number; --Declaring number type variable var2_str varchar2; begin var1_num := 10; --Assigning 'Number' Value to the variable var2_str := 'I am a string value...
1 comment
Read more

What happens when you create tables with the existing SYNONYM names

See below snippets: Select * from dba_synonyms where synonym_name='DUAL'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK PUBLIC DUAL SYS DUAL (NULL) We know that DUAL table is accessible by users. Lets create a table named DUAL under our schema (USER). CREATE TABLE dual AS SELECT * FROM dual; Select * from dual; insert into dual values('Y'); commit; Select * from dual; DUMMY ---------- X Y The Synonym is not referred unless the Synonym name is not used by any object under the USER/SCHEMA... So , When creating tables in your schema, take a look at SYNONYMs declared as PUBLIC and try not to use them.
1 comment
Read more