PLSQL programming ( Learn by examples - Part 3 )

/**
In this section , lets learn the following.
How to use a for loop to process multiple records...
**/
set serveroutput on size 100000
begin
--Select objects of table type that are valid under the schema Susil
for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL')
loop
if var.object_type='TABLE'
then
/*
Dbms_metadata package provides us a function get_ddl to get the data definition on object.
For your understanding , i have just chosen two tables in this example
If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema
**/
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Table name =>'||var.object_name);
dbms_output.put_line('=====================================================================');
dbms_output.put_line(' Table definition is below ');
dbms_output.put_line('=====================================================================');
dbms_output.put_line(dbms_metadata.get_ddl(var.object_type,var.object_name,var.owner));
dbms_output.put_line('=====================================================================');
end if;
end loop;

end;
/

Output:

=====================================================================
Table name =>EMP
=====================================================================
Table definition is below
=====================================================================

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"

=====================================================================
=====================================================================
Table name =>EMPLOYEE
=====================================================================
Table definition is below
=====================================================================
CREATE TABLE "SUSIL"."EMPLOYEE"
( "EMP_ID" VARCHAR2(6),
"EMP_NAME" VARCHAR2(40),
"DEPT_NO" NUMBER,
CONSTRAINT "E_PK" PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_TS" ENABLE
) 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"

=====================================================================