Re: Like, is there a LIKE like operator that functions with LONG columns?
Date: 1996/09/30
Message-ID: <324ff06d.8883433_at_dcsun4>#1/1
On Mon, 30 Sep 1996 11:36:13 -0400, janet <janet_at_telesph.com> wrote:
>>
>> hagen_at_violet.berkeley.edu wrote:
>> > Is there a means of conducting word match searches with the LONG datatype?
>> Dave Mausner wrote:
>> you could write a PL/SQL function which takes the LONG column name as a
>> parameter, treats it like a VARCHAR and performs the matching operation
>> internally using PL/SQL's LIKE operator. however you cannot return a boolean
>> result from PL/SQL to SQL, so return 0 or 1 instead. your code would look
>> like: select blah from blah where MyLike(longcol, '%this%')=1;
>
>Dave,
>I had the same question and it worked great! Thanks!
>
>declare
> cursor long_cur is
> select long_col
> from table;
> long_col_varchar varchar(240);
>begin
> open long_cur;
> fetch long_cur into long_col_varchar;
> if long_col_varchar like '%SEARCH TEXT%'
> then....
> blah blah blah
>
>always looking for info on how to manipulate LONG datatypes...
>janet
Then here is an added bonus in 7.3, the ability to piecewise fetch longs >32k (the limit in pl/sql currently). Here is a snippet that shows how to do this in pl/sql using dbms_sql. This routine expects someone else has opened a cursor and the cursor returns 1 row and the first column in the cursor is a long value and you want to display it on the web...... I am piecewise fetching 4k at a time...
procedure showlong( p_cursor in integer ) as
l_cursor integer default p_cursor;
l_n number;
l_long_val varchar2(4096);
l_long_len number;
l_buflen number := 4096;
l_curpos number := 0;
begin
dbms_sql.define_column_long(l_cursor, 1); l_n := dbms_sql.execute(l_cursor);
htp.preOpen;
if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
l_curpos := l_curpos + l_buflen;
htp.p( htf.escape_sc( l_long_val ) );
exit when l_long_len = 0;
end loop;
end if;
htp.preClose;
dbms_sql.close_cursor(l_cursor);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
htp.preClose;
end if;
htp.preClose;
raise;
end showlong;
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Sep 30 1996 - 00:00:00 CEST