In PLSQL yes -- in SQL no.

If the long is 32k or less, you can do some interesting things in PLSQL. Lets say you have a table T with a long column like this:

ops$tkyte@8i> create table t ( x int, y long );
Table created.

ops$tkyte@8i> insert into t values ( 1, 'how now brown cow' );
1 row created.

ops$tkyte@8i> select substr( y, 1, 5 ) from t;
select substr( y, 1, 5 ) from t
*
ERROR at line 1:
ORA-00932: inconsistent datatypes


ops$tkyte@8i> create or replace function t_substr
( p_rowid in rowid,
p_from in number,
p_for in number ) return varchar2
2 as
3 l_str long;
4 begin
5 select y into l_str from t where rowid = p_rowid;
6
7 return substr( l_str, p_from, p_for );
8 end;
9 /

Function created.

ops$tkyte@8i> select t_substr( rowid, 1, 5 ) from t;

T_SUBSTR(ROWID,1,5)
------------------------------
how n



So, for longs that are 32k or less, we can write functions that are callable in SQL to allow us to do some operations on these long columns.


Additionally -- we can use dbms_sql to READ longs (but not write) them that are of any size. We would piecewise read them. You cannot call a function that uses dbms_sql from SQL as did above but you can use it in any other procedure/function you write. Here is an example of a piecewise read of a long in plsql:

create or replace procedure showlong( p_query in varchar2 )
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(250);
l_long_len number;
l_buflen number := 250;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );

dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

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_long_len;
dbms_output.put_line( l_long_val );
exit when l_long_len = 0;
end loop;
end if;
dbms_output.put_line( '====================' );
dbms_output.put_line( 'Long was ' || l_curpos || ' bytes in length' );
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;
raise;
end showlong;
/

set echo on
begin
showlong( 'select text from all_views where rownum = 1' );
end;
/