This discussion is archived
3 Replies Latest reply: Aug 30, 2012 2:42 PM by Jason_(A_Non) RSS

CLOB datatype not working with XPATH functions in Oracle

860250 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    iam not able to convert using below
    lx:=xmltype (lx);
  • 3. Re: CLOB datatype not working with XPATH functions in Oracle
    Jason_(A_Non) Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points