Re: Transferring LONG RAW from TAB1 to TAB2

From: <markp7832_at_my-deja.com>
Date: Thu, 23 Sep 1999 16:45:21 GMT
Message-ID: <7sdles$dvk$1_at_nnrp1.deja.com>

Here is a post I had saved for study; maybe it will be of some help.

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_FR EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","M IN_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_FREELI ST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TABLE_LOC K","SAMPLE_SIZE","LAST_ANALYZED","
PARTITIONED","IOT_TYPE","TABLE_TYPE_OWNER","TABLE_TYPE","TEMPORARY","NES TED","BUFFER_POOL"
from all_object_tables

---

Long was 1114 bytes in length

PL/SQL procedure successfully completed.

Thomas Kyte
tkyte_at_us.oracle.com

---

In article <37E7CC4F.7D1E6B0A_at_itsnet.com>,   Nathan Christiansen <nathanc_at_itsnet.com> wrote:
> This is a multi-part message in MIME format.
> --------------B3D224819B8064688F07A23E
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit

>

> "Ms. D.H. Harvey" wrote:
> >
> > Where are the newsgroup archives please? Need urgent help before
> > am driven mad. All I want to do is look at the contents of a long
> > raw!!!!
> >
> > TIA Helen

>

> Try http://www.deja.com/home_ps.shtml

>

> Type comp.databases.oracle.tools into the Forum field.

>

> Type Kyte into the Author field.

>

> BTW, how do you solve this same problem in Java using the thin client?
> (See my other posting dated Mon. 20 Sept.)

>

> --

>

> Nathan Christiansen
> Multimedia Programmer
> HTML/Java Group
> Courseware
> Allen Communication
> --------------B3D224819B8064688F07A23E

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

Received on Thu Sep 23 1999 - 18:45:21 CEST