Re: select long type column

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 07 Sep 1998 17:29:49 GMT
Message-ID: <35fb1747.6058922@192.86.155.100>

A copy of this was sent to "Alvin Huang" <dkualvin_at_tp.silkera.net> (if that email address didn't require changing) On 7 Sep 1998 08:38:29 GMT, you wrote:

>Hi,all:
>
>Is there any way to select or insert long type column without using pro*c??
>(ORACLE 7.x)
>
>Regards
>
>Alvin,9/7

In 7.3, you can piecewise SELECT it using pl/sql (but *not* piecewise insert it). So, if the long is <= 32k -- plsql can do it all. If > 32k, pl/sql can read but not write it.

An example of piecewise selecting, which requires you to use dbms_sql, follows.

Other options are (not inclusive, I am sure there are others) include:

• OCI (full piecewise read/write of longs and long raws)
• ODBC

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

That piecewise fetches a long with 4k chunks. You send it a query that select a single row and a single column (a long). It prints it out on the web in this example (the htp.p call) but you can do whatever you want with it...  

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 Mon Sep 07 1998 - 12:29:49 CDT