Re: Select and Update Long columns bigger than 32K..

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/13
Message-ID: <346d2039.4836374@newshost>#1/1

On Wed, 12 Nov 1997 18:12:15 -0500, Sabarinath Nair <snair_at_mit.edu> wrote:

>Hi,
>
>In my application I need to select and Update Long columns using stored
>procedures. Since variables in PL/SQL cannot be more than 32K I am not
>able to do this. Has anyone out there solved this problem..We are using
>Oracle 7.3
>
>
>Thanks in Advance
>Sabari

You cannot update longs >32k in pl/sql but you can fetch them if need be by piece. An example:  

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;
 
             -- Process Data
 
             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;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

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 Thu Nov 13 1997 - 00:00:00 CST