Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00932 when converting column_expression from user_ind_expressions using to_lob (Oracle 10.2, Solaris 10)
ORA-00932 when converting column_expression from user_ind_expressions using to_lob [message #352190] Mon, 06 October 2008 09:50 Go to next message
bitberry
Messages: 2
Registered: October 2008
Junior Member
Try running these two simple statements on Oracle 10.2:

CREATE TABLE mytest(table_name varchar2(30), index_name varchar2(30), column_expression clob, column_position number);

INSERT INTO mytest (table_name,index_name,column_expression, column_position) SELECT table_name,index_name, to_lob(column_expression), column_position FROM user_ind_expressions EXPRA WHERE NOT EXISTS (SELECT 1 FROM user_constraints WHERE constraint_name = EXPRA.index_name AND table_name = EXPRA.table_name);


This results in this error:

ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got LONG

If I omit the WHERE NOT EXISTS like this:

INSERT INTO mytest (table_name,index_name,column_expression, column_position) SELECT table_name,index_name,to_lob(column_expression), column_position FROM user_ind_expressions EXPRA;


It works:

23 rows created.

What is going on?
Re: ORA-00932 when converting column_expression from user_ind_expressions using to_lob [message #352195 is a reply to message #352190] Mon, 06 October 2008 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: ORA-00932 when converting column_expression from user_ind_expressions using to_lob [message #352199 is a reply to message #352195] Mon, 06 October 2008 10:20 Go to previous messageGo to next message
bitberry
Messages: 2
Registered: October 2008
Junior Member
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 6 15:17:43 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> CREATE TABLE MyTest (
  Table_Name        VARCHAR2(30),
  Index_Name        VARCHAR2(30),
  Column_Expression CLOB,
  Column_Position   NUMBER); 

Table created.

SQL> INSERT INTO MyTest
           (Table_Name,
            Index_Name,
            Column_Expression,
            Column_Position)
SELECT Table_Name,
       Index_Name,
       To_lob(Column_Expression),
       Column_Position
FROM   User_Ind_Expressions expra
WHERE  NOT EXISTS (SELECT 1
                   FROM   User_Constraints
                   WHERE  Constraint_Name = expra.Index_Name
                          AND Table_Name = expra.Table_Name);  
       To_lob(Column_Expression),
       *
ERROR at line 8:
ORA-00932: inconsistent datatypes: expected - got LONG


SQL> INSERT INTO MyTest
           (Table_Name,
            Index_Name,
            Column_Expression,
            Column_Position)
SELECT Table_Name,
       Index_Name,
       To_lob(Column_Expression),
       Column_Position
FROM   User_Ind_Expressions expra; 

23 rows created.

SQL>
Re: ORA-00932 when converting column_expression from user_ind_expressions using to_lob [message #352202 is a reply to message #352199] Mon, 06 October 2008 10:37 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Seems a bug in SQL syntax analyzer because of sys.con$.condition column from user_constraints:
SQL> INSERT INTO MyTest
  2             (Table_Name,
  3              Index_Name,
  4              Column_Expression,
  5              Column_Position)
  6  SELECT Table_Name,
  7         Index_Name,
  8         To_lob(Column_Expression),
  9         Column_Position
 10  FROM   User_Ind_Expressions expra
 11  WHERE  NOT EXISTS (SELECT 1
 12                     FROM   User_Constraints
 13                     WHERE  Constraint_Name = expra.Index_Name
 14                            AND Table_Name = expra.Table_Name);  
       To_lob(Column_Expression),
       *
ERROR at line 8:
ORA-00932: inconsistent datatypes: expected - got LONG

SQL> create table c as select constraint_name, table_name from user_constraints;

Table created.

SQL> INSERT INTO MyTest
  2             (Table_Name,
  3              Index_Name,
  4              Column_Expression,
  5              Column_Position)
  6  SELECT Table_Name,
  7         Index_Name,
  8         To_lob(Column_Expression),
  9         Column_Position
 10  FROM   User_Ind_Expressions expra
 11  WHERE  NOT EXISTS (SELECT null
 12                     FROM   c
 13                     WHERE  Constraint_Name = expra.Index_Name
 14                            AND Table_Name = expra.Table_Name);  

0 rows created.

Regards
Michel
Previous Topic: problem storing japanese text
Next Topic: Bulk insert problems
Goto Forum:
  


Current Time: Sat Dec 10 22:50:37 CST 2016

Total time taken to generate the page: 0.14318 seconds