Re: Package for manipulating LONG fields

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/25
Message-ID: <3617924b.151862767@192.86.155.100>#1/1

A copy of this was sent to Flemming Jans <fj_at_belle.dk> (if that email address didn't require changing) On Fri, 25 Sep 1998 09:17:50 +0200, you wrote:

>Hi all,
>
>In Oracle8 there is a package dbms_lob for manipulating lobs,
>reading/writing piecewise etc.
>
>Is there such a package for ordinary LONG fields also, or do I have to
>use OCI ????
>
>Regards,
>
>Flemming

In v7.3 and up dbms_sql will let you piecewise READ a long in pl/sql (but not insert/update). The following is an example of how to do this. Other then that, yes -- you will have to use OCI or PRO*C or some other 3gl like language to manipulate longs.

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(250);
    l_long_len  number;
    l_buflen    number := 250;
    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_long_len;
            dbms_output.put_line( l_long_val );
            exit when l_long_len = 0;
      end loop;

   end if;

   dbms_output.put_line( '====================' );

   dbms_output.put_line( 'Long was ' || l_curpos || ' bytes in length' );    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;
      htp.preClose;
      raise;

end showlong;
/

set echo on
begin

    showlong( 'select text from all_views where rownum = 1' ); end;
/
set echo off  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Fri Sep 25 1998 - 00:00:00 CDT