-
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.
-
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.
-
Thanks for the help Stecal. This should work well!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|