Home » SQL & PL/SQL » SQL & PL/SQL » How to fetch data frm "long" data type column
How to fetch data frm "long" data type column [message #260587] Mon, 20 August 2007 06:52 Go to next message
deepak samal
Messages: 29
Registered: February 2005
Junior Member
I have a problem selecting from a table where long datatype column is there in where clause.

select * from user_constraints where SEARCH_CONDITION

here user_constraints.SEARCH_CONDITION is of datatype 'LONG' .

so this is giving error as
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

my purpose is to extract the not null constraints present in the database.How to write this query .
Re: How to fetch data frm "long" data type column [message #260596 is a reply to message #260587] Mon, 20 August 2007 07:18 Go to previous message
Messages: 20847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LONG columns can not be searched this way; CLOBs, on the other hand, can. A workaroud might be creating a temporary table (with a help of the TO_LOB function) and use it for such a purpose:
SQL> create global temporary table gtt_user_cons
  2  (constraint_name    varchar2(30),
  3   search_condition   clob);

Table created.

SQL> insert into gtt_user_Cons
  2  select constraint_name, to_lob(search_condition)
  3  from user_Constraints;

7 rows created.

SQL> col search_condition format a40

SQL> select * from gtt_user_Cons
  2  where search_condition like '%NOT NULL%';

------------------------------ ----------------------------------------
SYS_C0045146                   "EMPNO" IS NOT NULL
SYS_C0052991                   "MIG_ID" IS NOT NULL
SYS_C0052992                   "VRIEME" IS NOT NULL
SYS_C0052988                   "MIG_ID" IS NOT NULL
SYS_C0052989                   "VRIEME" IS NOT NULL


Previous Topic: Error when using Collections / Records
Next Topic: Complex use of substring fucntion
Goto Forum:

Current Time: Tue Oct 25 21:40:57 CDT 2016

Total time taken to generate the page: 0.10858 seconds