Re: 'substring' a LONG column

From: Mark D Powell <mark.powell_at_eds.com>
Date: 20 Sep 2001 07:21:39 -0700
Message-ID: <178d2795.0109200621.13e97754@posting.google.com>

"Frank" <franjoe_at_frisurf.no> wrote in message news:<ju7q7.20805$1T5.248984_at_news1.oke.nextra.no>...
> Hi!
>
> The LONG datatype has many constraints. Try to send the LONG column into a
> PLSQL function with a parameter
> VARCHAR2(max 32000 characthers I think) and use substr inside the function,
> and return the 80 characthers from
> the function. Such a function can be made possible to be callef from a SQL
> statement.
>
> (not tested :-)
> Frank
>
> Richelle Hutchinson <rihutchin_at_columbus.rr.com> wrote in message
> news:u37q7.55438$Iq4.29737501_at_typhoon.columbus.rr.com...
> > Does anyone know how to 'substring' a LONG field? We need to obtain the
> > first 80 characters and the substring function won't work. Changing this
> > column to a LOB datatype on the database is not an option at this time.
> >
> > Thanks in advance.
> >
> > -Richelle Hutchinson
> >

Richelle, if you want to process a partial long column you will need to resort to using either pl/sql or a pro* language. If the total length of the long column in under 32K you can declase a pl/sql varchar2 variable of 32K in size and select long into v_varchar2 ... as Frank mentioned though I think his post was a little unclear. If the long column data exceeds 32k in size then I believe you have to resort to using piece-wise fetching even though you only want the first piece.

Here is sql posted in the past by Thomas Kyte that will do just that:

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,
     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, NULL, NULL, TEMPORARY, 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

---

• Mark D Powell --

Received on Thu Sep 20 2001 - 09:21:39 CDT