Procedure for generating the DDL statements of the database objects.

There are two parameters used in the specified object,
 
1. Object Type        ==> eg: FUNCTION,PROCEDURE,PACKAGE,TABLE,VIEW etc.,
2. Directory Name  ==> Specify the directory name where the output to be stored.

Usage -  DS_GET_DDL_STATEMENT(<Object type>,<Directory Name>)

Objects to be created:
----------------------
 CREATE TABLE "SMF_APEXRPS"."DS_ERROR_TABLE"
   (    "COMNAME" VARCHAR2(200 BYTE),
    "COMDDDL" LONG
   );


Create or Replace
PROCEDURE DS_GET_DDL_STATEMENT(P_OBJECTTYPE VARCHAR2,PDIR VARCHAR2)
AS
V_DDL LONG;
V_ERRMSG VARCHAR2(500);
vInHandle  utl_file.file_type;
CURSOR C1 IS
SELECT * FROM USER_OBJECTS WHERE object_type=P_OBJECTTYPE;
BEGIN
FOR I IN C1 LOOP
select
dbms_metadata.get_ddl(P_OBJECTTYPE,I.OBJECT_NAME)  INTO V_DDL
from dual;
  vInHandle := utl_file.fopen(PDIR, I.OBJECT_NAME||'.txt', 'W');
  IF utl_file.is_open(vInHandle) THEN
   utl_file.put_line(vInHandle, V_DDL, FALSE);
    utl_file.fflush(vInHandle);
    utl_file.fclose_all;
 END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
V_ERRMSG:=SQLERRM;
INSERT INTO DS_ERROR_TABLE VALUES('ERROR',V_ERRMSG);
END;