CREATE OR REPLACE PROCEDURE PerXML
IS
INSERT INTO Personal VALUES (XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<Personal>
 <contacts>
<contact>
<name>alag</name>
 <phone>12345</phone>
 <address>Vasai</address>
 </contact>
 <contact>
<name>JINESH</name>
 <phone>42346</phone>
 <address>Andheri</address>
 </contact>
</contacts>
</Personal>
'));
CURSOR  XML_cur IS SELECT * FROM Personal;
XML_rec XML_cur%ROWTYPE;
BEGIN
FOR XML_cur IN XML_rec
LOOP 
INSERT INTO PersonalOut (name,phone,address)
 SELECT xt.*from Personal Pers,
XMLTable('/Personal/contacts/contact'
                  PASSING Pers.per
                  COLUMNS
                  name varchar2(10)        path 'name',
                  phone        number(10)        path 'phone',
                  address varchar2(500) path 'address') xt; 
END LOOP;
END;

SQL> CREATE OR REPLACE PROCEDURE PerXML
  2  IS
  3  INSERT INTO Personal VALUES (XMLTYPE('<?xml version="1.0" encoding="UTF-8"?
>
  4  <Personal>
  5   <contacts>
  6  <contact>
  7  <name>alag</name>
  8   <phone>12345</phone>
  9   <address>Vasai</address>
 10   </contact>
 11   <contact>
 12  <name>JINESH</name>
 13   <phone>42346</phone>
 14   <address>Andheri</address>
 15   </contact>
 16  </contacts>
 17  </Personal>
 18  '));
 19  CURSOR  XML_cur IS SELECT * FROM Personal;
 20  XML_rec XML_cur%ROWTYPE;
 21  BEGIN
 22  FOR XML_cur IN XML_rec
 23  LOOP
 24  INSERT INTO PersonalOut (name,phone,address)
 25   SELECT xt.*from Personal Pers,
 26  XMLTable('/Personal/contacts/contact'
 27                    PASSING Pers.per
 28                    COLUMNS
 29                    name varchar2(10)        path 'name',
 30                    phone        number(10)        path 'phone',
 31                    address varchar2(500) path 'address') xt;
 32  END LOOP;
 33  END;
 34  /

Warning: Procedure created with compilation errors.


SQL> execute PerXML;
BEGIN PerXML; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object ALAG.PERXML is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> show errors
Errors for PROCEDURE PERXML:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1      PLS-00103: Encountered the symbol "INSERT" when expecting one of
         the following:
         begin function pragma procedure subtype type <an identifier>
         <a double-quoted delimited-identifier> current cursor delete
         exists prior external language
         The symbol "begin" was substituted for "INSERT" to continue.

19/9     PLS-00103: Encountered the symbol "XML_CUR" when expecting one of
         the following:
         := . ( @ % ;

SQL>