Re: LONG column in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
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 Corporation

Received on Tue May 11 1999 - 18:53:22 CEST