We help IT Professionals succeed at work.

PLSQL Table of XMLType

Wasim Akram Shaik
Wasim Akram Shaik used Ask the Experts™
on
Pls help me to spot the error in the below block

CREATE OR REPLACE TYPE xmlobj AS OBJECT (
   col   XMLTYPE
);
/

CREATE OR REPLACE TYPE xmltab AS TABLE OF xmlobj;
/

CREATE TABLE XMLTABLE1
(
  COL1  XMLTYPE
)



DECLARE
   -- l_obj   xmltab;
   i       NUMBER;

   CURSOR c1
   IS
      SELECT XMLELEMENT (object_id, object_id) AS object1
        FROM user_objects
       WHERE ROWNUM <= 10;

   l_obj   xmltab := xmltab ();
BEGIN
   i := 1;

   FOR l1 IN c1
   LOOP
      l_obj.EXTEND;
      l_obj (i) := xmlobj (l1.object1);
      i := i + 1;
   END LOOP;

   FORALL i IN 1 .. l_obj.COUNT
      INSERT INTO xmltable1
           VALUES l_obj (i);
END;

Error on executing this block is

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

 26  /
      INSERT INTO xmltable1
      *
ERROR at line 23:
ORA-06550: line 23, column 7:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
ORA-06550: line 23, column 7:
PL/SQL: SQL Statement ignored


SQL>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
FORALL i IN 1 .. l_obj.COUNT
        INSERT INTO xmltable1
             VALUES ( l_obj(i).col);
Most Valuable Expert 2011
Top Expert 2012

Commented:
your table has column of xmltype
your collection is of objects that have a fields of xmltype

you were attempting to insert the object into the xmltype
you needed to insert the xmltype into the xmltype

also, you were missing () in the values clause
Thanks for the response sdstuber...


when i placed this i got a different error..

FORALL i IN 1 .. l_obj.COUNT
        INSERT INTO xmltable1
             VALUES ( l_obj(i).col);

/* Formatted on 2012/04/19 01:29 (Formatter Plus v4.8.8) */
DECLARE
   -- l_obj   xmltab;
   i       NUMBER;

   CURSOR c1
   IS
      SELECT XMLELEMENT (object_id, object_id) AS object1
        FROM user_objects
       WHERE ROWNUM <= 10;

   l_obj   xmltab := xmltab ();
BEGIN
   i := 1;

   FOR l1 IN c1
   LOOP
      l_obj.EXTEND;
      l_obj (i) := xmlobj (l1.object1);
      i := i + 1;
   END LOOP;

    FORALL i IN 1 .. l_obj.COUNT
        INSERT INTO xmltable1
             VALUES ( l_obj(i).col);
END;

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

 26  /
             VALUES ( l_obj(i).col);
                      *
ERROR at line 24:
ORA-06550: line 24, column 23:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND
table of records


SQL> 

Open in new window

Most Valuable Expert 2011
Top Expert 2012
Commented:
apparently a limitation of 10g.  It worked fine for me in 11g

you'll need to either upgrade, or don't use forall bulk operation, but iterate though with for loop
Yes, thanks sdstuber.. yeah it went well with normal loop.. might be a bug or unimplemented feature..!!