Re: Which datatype for Text Field 32k MAX

I think I didn't make my meaning entirely clear on instr() etc. I meant the ones built into dbms_lob. e.g.

create table c (n1 number, c1 clob);

insert into c values (1,'A very short LOB'); insert into 2 value (2,'A slightly longer LOB');

select n1 from c
where dbms_lob.instr(c1,'long') != 0;

Allowing for typos, this should give you an output of :

N1

--
 2



--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



JF wrote in message <8Mi56.51981$_h.352306_at_NewsReader>...


>I've just had a look at some overview info on interMedia and am probably

>going to use that route once I get up to speed with Oracle, any book

>recommendations that does a good job on using interMedia?

>

>I also like the sound of instr with SQL and could be a good quick fix, does

>anyone have an example of using instr() on a CLOB field?

>I presume that a PL/SQL procedure is required and that I can't execute a

 SQL


>statement directly from a client that utilises the instr() function

 required


>on a LOB.

>

>

>

>Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message

>news:978686500.18871.0.nnrp-02.9e984b29_at_news.demon.co.uk...

>>

>> It is possible, though, to use functions from the dbms_lob

>> package in SQL. There are the usual sorts of things

>> like 'instr()' and 'substr()' that can be used in SQL

>>

>>

>> --

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> Practical Oracle 8i:  Building Efficient Databases

>>

>> Publishers:  Addison-Wesley

>> See a first review at:

>> http://www.ixora.com.au/resources/index.htm#practical_8i

>> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

>>

>>

>>

>> Richard Armstrong wrote in message

>> <978657729.9750.0.nnrp-12.9e9809ca_at_news.demon.co.uk>...

>> >John,

>> >

>> >I don't think you can search LONGs and

>> >CLOBs using ordinary SQL operators (e.g WHERE ARTICLE_TEXT LIKE

 '%keyword%')


>> >so it might be worth considering interMedia and Oracle 8.1.6.

>> >

>>

>>

>

>