Re: urgent???????

From: William Beilstein <BeilstWH_at_obg.com>
Date: Mon, 26 Jun 2000 10:27:20 -0400
Message-Id: <10540.110417@fatcity.com>

Sorry about my earlier answer, I through it out without checking. Please = see article 67566.1 in metalink. What you want to use is the DBMS_SQL.COLUM= N_VALUE_LONG procedure. This is specifically provided to return chunks of = a long value from a cursor.

>>> Vadim Gorbunov <dim_at_protek.ru> 06/26/00 10:57AM >>>
Hi, Mohamed,
This script is from www.onwe.co.za=20
Hope this helps
Vadim

rem -----------------------------------------------------------------------=

rem Filename: readlong.sql
rem Purpose: Fetch Long column values piece-wise from PL/SQL

rem Date:       12-Jan-1999
rem Author:     Frank Naude (frank_at_onwe.co.za)
rem -----------------------------------------------------------------------=

set serveroutput on

• Create test table drop table longtable; create table longtable (longcol long) tablespace TOOLS; insert into longtable values ( rpad('x', 257, 'QWERTY') );

DECLARE

  cur1       PLS_INTEGER         :=3D DBMS_SQL.OPEN_CURSOR;;
  rc         NUMBER;
  long_piece VARCHAR2(256);
  piece_len  INTEGER             :=3D 0;
  long_tab   DBMS_SQL.VARCHAR2S;
  long_len   INTEGER             :=3D 0;

BEGIN
  DBMS_SQL.PARSE(cur1, 'select longcol from longtable', DBMS_SQL.NATIVE);   DBMS_SQL.DEFINE_COLUMN_LONG(cur1, 1);
  rc :=3D DBMS_SQL.EXECUTE(cur1);

  rc :=3D DBMS_SQL.FETCH_ROWS(cur1);                       -- Get one row

• Loop until all pieces of the long column are processed LOOP DBMS_SQL.COLUMN_VALUE_LONG(cur1, 1, 256, long_len, long_piece, = piece_len); EXIT WHEN piece_len =3D 0; DBMS_OUTPUT.PUT_LINE('Long piece len=3D'|| piece_len);

     long_tab( NVL(long_tab.LAST, 0)+1 ) :=3D long_piece; -- Add piece to = table

     long_len :=3D long_len + piece_len;   END LOOP;
  DBMS_SQL.CLOSE_CURSOR(cur1);
  DBMS_OUTPUT.PUT_LINE('Total long col fetched, len=3D'|| long_len); Received on Mon Jun 26 2000 - 09:27:20 CDT