CREATE OR REPLACE FUNCTION      fetch_schema_metadata(in_owner IN varchar2,
  in_name_filter IN varchar2 DEFAULT '%')
  RETURN CLOB
  authid CURRENT_USER
IS
  dmh NUMBER;
  ddltext CLOB;
  TYPE ott IS varray(9) OF varchar2(32);
  ots ott := ott('table', 'view', 'package', 'type','sequence_disabled', 'materialized view', 'index', 'procedure', 'trigger');
BEGIN
  dbms_lob.createtemporary(ddltext, TRUE);
  dmh := dbms_metadata.OPEN('TABLE');
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', FALSE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', FALSE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', TRUE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', TRUE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'FORCE', FALSE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', TRUE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);

  FOR ot IN ots.FIRST..ots.LAST
  loop
    FOR t IN (
      SELECT object_name
      FROM all_objects
      WHERE LOWER(owner)=LOWER(in_owner)
        AND LOWER(object_name) LIKE LOWER(in_name_filter)
        AND LOWER(object_type) = LOWER(ots(ot))
      ORDER BY object_name
      )
    loop
      BEGIN
        dbms_lob.append(ddltext,
          dbms_metadata.get_ddl(
            object_type=>REPLACE(UPPER(ots(ot)), ' ', '_'),
            name=>UPPER(t.object_name),
            schema=>UPPER(in_owner)
          )
        );
      exception WHEN others THEN
        dbms_lob.append(ddltext, sqlerrm);
      END;
    END loop;
  END loop;
  dbms_metadata.close(dmh);
  RETURN ddltext;
END;