can I use a select LIKE on a Long Field 1

[bookouri]

I have a table where they have stored narrative text in long field. I need to find any records where a certain string appears in the text. It looks like I can't do a select * from mytable where longfield_name like ('%HOME%') that gives me an error "expected char and got long"

Is there any way to work around that?

thanks

 

[SantaMufasa]

Bookouri,

LONG fields, as you know, are rather like John Steinbeck's character, "Lennie", in Steinbeck's book, "Of Mice and Men": Big, but not at all intelligent. I believe Oracle "ConText" product gives you some capability of dealing more intelligently with LONGs. But without using special Oracle-LONG packages, you can build user-defined functions to search and display LONG fields. Here are your user-defined-function capabilities: You can search the first 32,765 characters of a LONG field, then display those first 32,765 characters in eight 4000-character pieces + one 765-character piece; but displaying anything past the first 4000 characters is REALLY a pain. (Isn't this all rather silly?)

If this sounds interesting to you, I can help you build such functions (but I must go to a meeting in a few minutes, so my help would need to wait for a few hours.) If this all sounds like more of a pain than it's worth, so be it.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:54 (10Mar04) UTC (aka "GMT" and "Zulu"), 11:54 (10Mar04) Mountain Time)

 

[bookouri]

it sounds like a real pain. I was hoping there was a simple solution...I should have known better.<G> I solved my current problem by just importing the table into Access which transformed the long field into an access memo field and i was able to do a query to get what i needed. In most of my cases the LONG is used to keep a minimal text narrative.. probably no more than a few hundred characters at most. Ill look at Forms and Reports and see what they have built in to handle the longs before I try to build some complicated routines...

thanks again for the information

 

[SantaMufasa]

Bookouri,

Under the circumstances you have (where "...the LONG is used to keep a minimal text narrative...probably no more than a few hundred characters at most), you can solve your problem by converting your table's LONG column to a VARCHAR2(4000). Following is a coded example of how to convert your LONG column:

desc longthang
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 X                                                  LONG
 ID                                                 NUMBER

create or replace function get_long (row in rowid) return varchar2 is
	longhold	varchar2(4000);
begin
	select x into longhold from longthang where rowid = row;
	return longhold;
end;
/
create table long2 (id number, x varchar2(4000));
insert into long2 select id, get_long(rowid) from longthang;
update longthang set x = null;
alter table longthang modify x varchar2(4000);
update longthang set x = (select x from long2 where id = longthang.id);

desc longthang
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 X                                                  VARCHAR2(4000)
 ID                                                 NUMBER

SQL> col x format a20
select * from longthang;

X                            ID
-------------------- ----------
Hello                         1

Let us know if this resolves your problem or if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:54 (11Mar04) UTC (aka "GMT" and "Zulu"), 00:54 (11Mar04) Mountain Time)

 

[bookouri]

Thanks again, as usual you have helped me out of another jam..

 

[kevineva]

We're using "Oracle9i", right? 9.2, hopefully?

Then you can follow Oracle's advice: Stop using longs. use to_lob to convert the column to a CLOB. Then you can use context to provide all the cool full text searching you could ever want, or, if you just need to treat it like a big varchar2(4,000,000) you can do that too.

"Oracle9i supports LONG as well as LOB datatypes. When possible, change your existing applications to use LOBs instead of LONGs because of the added benefits that LOBs provide." -- Oracle9i Application Developer's Guide - Large Objects