Re: PL/SQL Variable Size Problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 25 Jun 1998 17:46:29 GMT
Message-ID: <35978c94.18266565_at_192.86.155.100>

A copy of this was sent to Ben Weber <weber_at_pjm.com> (if that email address didn't require changing) On Thu, 25 Jun 1998 11:20:30 -0400, you wrote:

>Hello,
>
>I have a table which is used to store reports that I am allowing users
>to download via WebServer. The reports are stored in the database as
>LONG (which has a max length of 2G). However, the PL/SQL function that
>I am using to retrieve the report from the DB seems to have a limitation
>based on PL/SQL variables of 32K.
>
>Looking through the documentation for PL/SQL, there was no workaround
>that would allow me to get the entire value. It said that even if you
>defined it as a LONG, it would have a max of 32K.
>
>Is there any other way to get around this variable limitation?
>
>Thanks in advance,
>Ben Weber

piecewise fetching will do it... here is an example. You send showlong a query that selects 1 column (a long) and fetches 1 row (it'll only fetch the first row):

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

If you create it, then you can test it with:

SQL> begin
  2 showlong( 'select text from all_views where rownum = 1' );   3 end;
  4 /
select OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME,

     PCT_FREE, PCT_USED,
     INI_TRANS, MAX_TRANS,
     INITIAL_EXTENT, NEXT_EXTENT,
     MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
     FREELISTS, FREELIST_GROUPS, LOGGING,
     BACKE
D_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS,
     AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,
     AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS,
     DEGREE, INSTANCES, CACHE, TABLE_LOCK, 
     SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED,
     IOT_TYPE, NULL, NULL, TE
MPORARY, NESTED,
     BUFFER_POOL

from all_tables
union all
select

"OWNER","TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","PCT_FREE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTENTS","MAX_EXTENTS","PCT_INCREASE
","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","AVG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TABLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","
PARTITIONED","IOT_TYPE","TABLE_TYPE_OWNER","TABLE_TYPE","TEMPORARY","NESTED","BUFFER_POOL"

from all_object_tables

---

Long was 1114 bytes in length

PL/SQL procedure successfully completed.  

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 Thu Jun 25 1998 - 19:46:29 CEST