Re: LONG column in PL/SQL
Date: Tue, 11 May 1999 16:53:22 GMT
Message-ID: <37396054.17200372_at_192.86.155.100>
A copy of this was sent to Remco Moolenaar <remco_at_scc.nl> (if that email address didn't require changing) On Tue, 11 May 1999 18:25:43 +0200, you wrote:
>Good afternoon,
>
>According to the Oracle manuals I can not do a select for a LONG column which is larger than
>32760 bytes.
>Is there, however, a way to retrieve the LONG value? I need this for a webapplication where a
>full webpage is stored in a LONG column.
>
>Remco Moolenaar
>SCC Internetworking & Databases
>http://www.scc.nl
>
this procedure demonstrates how to do that. You can use dbms_sql to piecewise fetch it. You send this a query that fetches 1 row with 1 column -- the long column. It dumps it using htp.p
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(4096); l_long_len number; l_buflen number := 4096; 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_buflen; htp.p( l_long_val ); exit when l_long_len = 0; end loop;
end if;
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; raise;
end showlong;
/
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
-- Opinions are mine and do not necessarily reflect those of Oracle CorporationReceived on Tue May 11 1999 - 18:53:22 CEST