3 Replies Latest reply: Aug 30, 2012 4:42 PM by Jason_(A_Non) RSS

    CLOB datatype not working with XPATH functions in Oracle

    860250
      Hi,

      Below logic is not working. I cant change data type of lx from CLOB to XMLTYPE.

      Below is just a example. My program reads data from Table & XML script is stored in column of data type CLOB.

      Is there any work around to read from CLOB data type.

      DECLARE

      fir varchar2(2000):='First';
      las varchar2(2000):='Last';

      lx CLOB := '<dept>
           <employee>
                <name>
                     <first>John</first>
                     <last>Doe</last>
                </name>
           </employee>
           </dept>';

      BEGIN

      dbms_output.put_line(fir);
      dbms_output.put_line(las);

      SELECT X.firstname, X.lastname
      INTO fir, las
      FROM
      XMLTABLE ('//dept/employee' passing lx
      COLUMNS
      firstname      VARCHAR(20)      PATH 'name/first',
      lastname      VARCHAR(25)      PATH 'name/last') AS X;

      dbms_output.put_line(fir);
      dbms_output.put_line(las);

      END;

      --- Error ---

      ORA-06550: line 24, column 37:
      PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB
      ORA-06550: line 21, column 1:
      PL/SQL: SQL Statement ignored
      06550. 00000 - "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.
      *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
        • 1. Re: CLOB datatype not working with XPATH functions in Oracle
          odie_63
          Is there any work around to read from CLOB data type.
          Not a workaround, but a solution : convert the CLOB to XMLType.
          SQL> DECLARE
            2  
            3  fir  varchar2(2000):='First';
            4  las  varchar2(2000):='Last';
            5  
            6  lx CLOB := '<dept>
            7   <employee>
            8    <name>
            9     <first>John</first>
           10     <last>Doe</last>
           11    </name>
           12   </employee>
           13   </dept>';
           14  
           15  BEGIN
           16  
           17   dbms_output.put_line(fir);
           18   dbms_output.put_line(las);
           19  
           20  SELECT X.firstname, X.lastname
           21  INTO fir, las
           22  FROM
           23  XMLTABLE ('/dept/employee' passing xmltype(lx)
           24     COLUMNS
           25     firstname  VARCHAR(20)  PATH 'name/first',
           26     lastname  VARCHAR(25)  PATH 'name/last') AS X;
           27  
           28   dbms_output.put_line(fir);
           29   dbms_output.put_line(las);
           30  
           31  END;
           32  /
           
          First
          Last
          John
          Doe
           
          PL/SQL procedure successfully completed
           
          • 2. Re: CLOB datatype not working with XPATH functions in Oracle
            860250
            iam not able to convert using below
            lx:=xmltype (lx);
            • 3. Re: CLOB datatype not working with XPATH functions in Oracle
              Jason_(A_Non)
              Please stop to think about why that statement does not work.

              The XMLType() function converts the input parm into an XMLType datatype and returns that. You are then attempting to store and XMLType datatype into a CLOB. As there is no implicit conversion between these two data types setup, it fails with the error you are encountering.

              If you look at Marc's example above, you will see he does the conversion on-the-fly within the SQL statement
               23  XMLTABLE ('/dept/employee' passing xmltype(lx)
              If you wish to convert it outside the SQL, you will need to create an XMLType variable in your code to store the results into, such as
              l_xml_var      XMLTYPE;
              ...
              l_xml_var := xmltype(lx);
              and switch the SQL to using that l_xml_var instead of your lx CLOB.