Re: Determining the size (length) of LONG

From: Mario Perreault <mario.perreault_at_bigfoot.com>
Date: Thu, 07 Oct 1999 22:28:33 -0400
Message-ID: <37FD56D1.E03F4802@bigfoot.com>

Ok, here is a sample code for function "long_length". This function must be call by another PL/SQL describe bottom...

CREATE OR REPLACE FUNCTION long_length(cTabName varchar2, cColName varchar2, cRowid varchar2)
RETURN NUMBER
IS

           cur_id integer;
           buff   varchar2(32767);
           len    integer;
           offset integer;
           v_length integer;
           stmt varchar2(500);
           ret    integer;
BEGIN
          stmt :=  ' SELECT  '|| cColName || ' FROM  ' || cTabName ||
                   ' WHERE rowid = '||''''||cRowid||'''';
          cur_id := dbms_sql.open_cursor;
          dbms_sql.parse(cur_id, stmt, dbms_sql.NATIVE);
          dbms_sql.define_column_long(cur_id, 1);
          ret := dbms_sql.execute(cur_id);

          IF (dbms_sql.fetch_rows(cur_id) > 0 )
          THEN
              offset := 1;
              len := 0;
              LOOP
                 dbms_sql.column_value_long(cur_id, 1,32767, offset,
                                            buff,  v_length);
                 len := len + v_length;
                 EXIT  WHEN v_length < 32767;
                 offset := offset + v_length;
              END LOOP;
          END IF;
          dbms_sql.close_cursor(cur_id);
          return( len );

END;
/

rem You can use the below given sample PL/SQL block with the rem above function to retrieve the lengths of long column: rem
rem NOTE : Do not forget to replace the TABLE_NAME, LONG_COLUMN_NAME rem with your own table and long column names respectively. rem
rem Before running the following block from SQL*PLUS, set the server rem output to some big buffer so that the dbms_output package can work

rem properly.

set serveroutput on size 100000;

DECLARE
  len number;
  cRowid varchar2(30);
  num number;
  cursor T_CURSOR is

         SELECT rowid
         FROM YOUR_TABLE;

BEGIN
  open T_CURSOR;
  LOOP
 FETCH T_CURSOR
 INTO cRowid;
 EXIT WHEN T_CURSOR%NOTFOUND;
 len := long_length('YOUR_TABLE', 'THE_LONG_COLUMNS', cRowid);  if len > 0
 then
  dbms_output.put_line(rowidtochar(cRowid)||' '||to_char(len, '999999'));
 end if;
  END LOOP;
END;
/

NetComrade wrote:

> Hi,
>
> Well length() doesn't work... any other ways?
> ---------------
> Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
> AOL: NetComrade ICQ: 11340726 remove NSPAM to email

--
Salutations!

        \\\|||///
        /  ^ ^  \
        (  0 0  )

------oOOo-(_)-oOOo-----
mario.perreault_at_bigfoot.com Received on Thu Oct 07 1999 - 21:28:33 CDT