I need to select the first 255 characters from a Long

[gandolf989]

I have a table as such

MYTABLE (
PKEY CHAR(10) NOT NULL,
MYCOMMENTS LONG NULL
)

I want the equivalent of the following
SELECT PKEY, SUBSTR(MYCOMMENTS, 255) FROM MYTABLE;

I realize that I can't use SUBSTR. I also can't use UTL_RAW or DBMS_LOB.

Does anyone have either a SQL or PL/SQL solution that will work in Oracle 8.1.7.3. The OS is Windows NT 4.0 SP6, not that it matters.

Thanks.

[stecal]

You can use an PLSQL explicit cursor to retrieve up to 255 characters of the
long column data.


DECLARE
v_target VARCHAR2(255);
BEGIN
for c1 in (select v_long from test) LOOP
v_target := substr(c1.v_long,1,255);
dbms_output.put_line (v_target);
end LOOP;
END;

Solution Explanation:
=====================

This solution will work as long as the long column contains no more than 32k
of data. When you declare a statement in this way, the long information
is retireved into a pl/sql VARCHAR2 variable, which, unlike SQL, has a limit
on it's size of 32k bytes. Once the value is retrieved into the variable,
the substr function can be performed on it to give the required result.

Note:
=====
The real limit on the above code example is the DBMS_OUTPUT.PUT_LINE function.
There is a 255 byte limit on the DBMS_OUTPUT.PUT_LINE function.

[gandolf989]

Thanks for the help Stecal. This should work well!