The default is 80 chars for a LONG datatype, to get it into a file you
may need to have another field in the SELECT statement after the LONG
field.
Mark D Powell wrote:
>
> "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 Mon Sep 24 2001 - 07:02:10 CDT
