CREATE TABLE MY_SCHEMAMETA4 (Objtype VARCHAR2(50), Objname VARCHAR2(1000), MD XMLTYPE);

 1  CREATE OR REPLACE procedure Get_schema_XML2
 2  --return xmltype
 3  authid current_user
 4  IS
 5  -- Define local variables.
 6  h1      NUMBER;         -- handle returned by OPEN for tables
 7  th1     NUMBER;         -- handle returned by ADD_TRANSFORM for tables
 8  doc     sys.XMLtype;   -- metadata is returned in sys.ku$_ddls,
 9  --DDL     xmltype;           -- creation DDL for an object
10  pi      parsed_items;   -- parse items are returned in this object
11  Obj_type varchar2(500);
12  obj_name varchar2(10000);
13  SQL_ERROR     NUMBER := SQLCODE;
14  SQL_ERRMESS   VARCHAR2 (250) := SUBSTR (SQLERRM, 1, 200);
15  BEGIN
16   h1 := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
17   DBMS_METADATA.SET_FILTER(H1,'SCHEMA','EMACH');
18   DBMS_metadata.set_filter(h1,'INCLUDE_PATH_EXPR','IN''TABLE''');
19   DBMS_METADATA.SET_FILTER(H1,'NAME_EXPR','LIKE''PMN_ORG_T''','TABLE');
20   DBMS_METADATA.SET_PARSE_ITEM(h1,'NAME');
21  DBms_output.put_line(3);
22   LOOP
23     doc := dbms_metadata.fetch_XML(h1);
24     EXIT WHEN doc IS NULL;
25   SELECT   Nodes into obj_type FROM   (SELECT   t2.COLUMN_VALUE.GETROOTELEMENT () Nodes, Xml
26                                  FROM   (SELECT   EXTRACT (doc,
27                                                '/ROWSET/ROW') Xml FROM   dual) t,
28                                  TABLE (XMLSEQUENCE (t.xml.EXTRACT ('//node()'))) t2)
29                                  WHERE   ROWNUM = 1 AND Nodes NOT IN ('ROWSET', 'ROW');    
                                    -- SELECT USED TO GET OBJECT TYPE
30  DBms_output.put_line(OBJ_TYPE);
31    select DOC.extract('/ROWSET/ROW//NAME/text()').getstringval() INTO OBJ_NAME from DUAL ;    
             -- NEED TO APPLY FILTER 
32    dbms_output.put_line(' The srting value is '|| Obj_Name);
33       INSERT INTO MY_SCHEMAMETA4(Objtype, Objname, md)
34         VALUES (Obj_Type, obj_name,DOC);
35       COMMIT;
36     END LOOP;
37   DBMS_METADATA.CLOSE(h1);
38    EXCEPTION
39   WHEN OTHERS THEN
40     DBMS_OUTPUT.PUT_LINE(OBJ_NAME);
41     DBMS_OUTPUT.PUT_LINE(OBJ_type);
42* END;
43  /

Procedure created.

OBJTYPE                                 OBJNAME
---------------------------------------------------------------
TRIGGER_T
    G_PMN_ORG_HISTPMN_ORG_TORG_IDORG_IDORG_TECH_IDORG_TECH_IDORG_TECH_IDID_BYID_BYMAIN_TAP_CDMAIN_TAP_
CDALT_TAP_CDALT_TAP_CDTAPE_CDTAPE_CDORG_NMORG_NM

SELECT SUBSTR(ABC, (INSTR(ABC,'<NAME>',1,1)+6),(INSTR(ABC,'</NAME>',1,1)-7)) INTO OBJ_NAME FROM (
  select DOC.extract('/ROWSET/ROW//NAME').getstringval() ABC from DUAL) ;