CREATE TABLE scott.DDL_SAVE_TBL
(
  LOAD_DT  CHAR(8 BYTE),
  OWNER        VARCHAR2(30 BYTE),
  OBJECT_NAME  VARCHAR2(30 BYTE),
  DDL          CLOB,
  OBJECT_TYPE  VARCHAR2(30 BYTE)
);

CREATE TABLE scott.load_DATES
(
  LOAD_DT            CHAR(8 BYTE)           NOT NULL,
)

create or replace procedure scott.save_objects_ddls
is
v_ddl          scott.ddl_save_tbl.ddl%type;

begin

for rec in (select object_type ,object_name, owner, load_dt from all_objects, scott.load_dates where owner in ('SCOTT')
and object_name  in ('EMP', 'DEPT'))

loop 

dbms_output.put_line(rec.object_type || rec.object_name || rec.owner);

select dbms_metadata.get_ddl(rec.object_type,rec.object_name,rec.owner) into v_ddl from dba_objects;

insert into scott.ddl_save_tbl values (rec.object_name, rec.owner, rec.object_type, rec.load_dt, v_ddl );

end loop;

exception
when others then
dbms_output.put_line (SQLCODE|| SQLERRM|| 'review the logs');

end;

-31603ORA-31603: object "EMP" of type TABLE not found in schema
"SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 2625
ORA-06512: at
"SYS.DBMS_METADATA", line 2668
ORA-06512: at "SYS.DBMS_METADATA", line
2983
ORA-06512: at "SYS.DBMS_METADATA", line 3897
ORA-06512: at
"SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1review the logs

SCOTT@orcl > select dbms_metadata.get_ddl('TABLE','TBL','U1') from dual;

DBMS_METADATA.GET_DDL('TABLE','TBL','U1')
--------------------------------------------------------------------------------

  CREATE TABLE "U1"."TBL"
   (    "N" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"


SCOTT@orcl > create or replace
  2    procedure save_objects_ddls
  3    is
  4      v_ddl clob;
  5    begin
  6        select dbms_metadata.get_ddl('TABLE','TBL','U1') into v_ddl from dual;
  7        dbms_output.put_line (v_ddl);
  8  end;
  9  /

Procedure created.

SCOTT@orcl > exec save_objects_ddls
BEGIN save_objects_ddls; END;

*
ERROR at line 1:
ORA-31603: object "TBL" of type TABLE not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1
ORA-06512: at "SCOTT.SAVE_OBJECTS_DDLS", line 5
ORA-06512: at line 1


SCOTT@orcl > create or replace
  2    procedure save_objects_ddls
  3    authid current_user
  4    is
  5      v_ddl clob;
  6    begin
  7        select dbms_metadata.get_ddl('TABLE','TBL','U1') into v_ddl from dual;
  8        dbms_output.put_line (v_ddl);
  9  end;
 10  /

Procedure created.

SCOTT@orcl > exec save_objects_ddls

  CREATE TABLE "U1"."TBL"
   (    "N" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING
  TABLESPACE "USERS"

PL/SQL procedure successfully completed.

SCOTT@orcl >

-31600ORA-31600: invalid input value PACKAGE BODY for parameter OBJECT_TYPE in
function GET_DDL

CASE rec.object_type
  WHEN 'PACKAGE' THEN 'PACKAGE_SPEC'
  ELSE REPLACE(rec.object_type,' ','_')
END