Ok, 1,000 rows is smallish, we can call plsql from sql. In the following, rowid could be replaced with a primary key of course. We'll cache the long so as to do the recursive sql to fetch it out once per row instead of once per call. Since you have 30k max, we can use static sql (else we'd have to use dbms_sql to read this piecewise)
Here is the approach:
ops$tkyte@ORA9IR2> create or replace package get_long_pkg
2 as
3 function piece( p_rowid in rowid, p_from in number, p_for in number ) return varchar2;
4 end;
5 /
Package created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body get_long_pkg
2 as
3 g_rowid rowid;
4 g_long long;
5
6 function piece( p_rowid in rowid, p_from in number, p_for in number ) return varchar2
7 is
8 begin
9 if (g_rowid is null or g_rowid <> p_rowid)
10 then
11 select theText into g_long
12 from foo
13 where rowid = p_rowid;
14 g_rowid := p_rowid;
15 end if;
16
17 return substr( g_long, p_from, p_for );
18 end;
19
20 end;
21 /
Package body created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select textLength,
2 get_long_pkg.piece( rowid, 1, 20 ) p1,
3 get_long_pkg.piece( rowid, 21, 20 ) p2
4 from foo
5 where rownum <= 5;
TEXTLENGTH P1 P2
---------- -------------------- --------------------
546 select ue.name, u.na me, o.name, c.name,
2624 select ou.name, oc.n ame,
decode(c
1211 select u.name, c.nam e, o.name,
de
2730 select uc.name, oc.n ame, c.name, ut.name
243 select o.name, c.sch ema, c.package
from
table foo was created by this process:
</code>
http://asktom.oracle.com/Misc/MoveLongs.html <code>