Re: How to use LONG field in the Where clause

no.
If you want to use long (which is deprecated) then you must write a PL/SQL function to parse out a long column into a varchar2 variable(max 32k) and look for whatever you are seeking (e.g. 'Error' in the string). When it finds it it should return the first place it finds the mathcing stringe and if it looks through the whole long and does not find it then it should return 0. (for example)
It would look like:

select * from atc_message_log where myFunction(msg_content,'Error')>0;

It is going to be very expensive to execute. It wil cause each and every row in the table to be read and compared vi the plsql command. I think a better choice would
be to use Oracle's intermedia text engine , create an index on that column and use the contains( ) ans score () functions.

Jim

"Rangeshwara Reddy Kona" <konareddy_at_hotmail.com> wrote in message news:3bf2d7cd$1_at_news.cadence.com...
> I want to use field type as Long. Still I want to query the information on
> Long to get appropriate OBJ_ID. one of my friend told that I can use
to_char
> function to do this. Can I do this with to_char.
> Thx,
> Rangesh
>
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
> news:nrnI7.24206$XJ4.15027493_at_news1.sttln1.wa.home.com...
> > You can either use the intermedia text engine and create an index on the
> > field.
> > You can convert the long to a lob and use a function there. (long is
> > deprecated, does msg_content really need to be over 4000 bytes? if not
> then
> > use varchar2)
> > You can write your own pl/sql function that parses out the long in 32 K
> > chunks and looks for a particular string.
> >
> > Jim
> > "Rangeshwara Reddy Kona" <konareddy_at_hotmail.com> wrote in message
> > news:3bf1fb15_at_news.cadence.com...
> > > Hi all,
> > >
> > > Message contain date like 'Reached bad state' 'Error'
> > > I want to find the records which are having 'Error' in it.
> > > I can't use
> > > select * from atc_message_log where msg_content like '%Error%';
> > > Thanks in Advance.
> > > Rangesh
> > >
> > > SQL> desc atc_message_log;
> > > Name Null? Type
> >
>
> ----------------------------------------- -------- ----------------------
> > --
> > > ---
> > >
> > > OBJ_ID NOT NULL NUMBER(9)
> > > COMP_ID NOT NULL NUMBER(9)
> > > INTEGRATION_ID NOT NULL VARCHAR2(300)
> > > WORK_UNIT_ID NOT NULL NUMBER(9)
> > > MSG_NBR NOT NULL NUMBER(9)
> > > TIMESTAMP NOT NULL DATE
> > > SEVERITY NUMBER(5)
> > > MSG_CONTENT LONG
> > >
> > > SQL>
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Wed Nov 14 2001 - 16:47:16 CST