Re: Long field into varchar variables?!?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 03 Sep 1999 10:42:23 -0400
Message-ID: <+d3PNyGm3bbJ8az8iNp1vm+G51zT_at_4ax.com>

A copy of this was sent to Fraser Boswell <Fraser.Boswell_at_spamme.ed.ac.uk> (if that email address didn't require changing) On Fri, 03 Sep 1999 15:35:24 +0100, you wrote:

>In Forms 4.5, using PL/SQL, is it possible to take a long datatype of
>more than 32K and move it into 2 or more varchar2 variables? If so how?
>
>Thanks in advance
>
>Fraser

You can piecewise READ (but not write) LONGS with plsql using dbms_sql. here is a small examle:

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;
      raise;

end showlong;
/

So you could write a stored procedure to get a 'piece' of a long (like substr) and use that.

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
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 Fri Sep 03 1999 - 16:42:23 CEST