RE: LONG RAW

From: Muthu Avudaiyappan <MAvudaiyappan_at_Vertek.com>
Date: Wed, 10 Jan 2001 15:00:36 -0500
Message-Id: <10737.126321@fatcity.com>

Hi,

Try this one too.... I have used this and it works

Muthu - New Jersey

RUN SQL SCRIPT TO FIND LENGTH OF LONG COLUMN

1. Create the following function using PL/SQL 2.2 and up, which utilizes the DBMS_SQL package. This is a generalized function that accepts 'Table name', 'Long Column Name' and the 'Rowid' of the row.

   ---

     CREATE OR REPLACE FUNCTION l_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 := 0;
                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;

  /

---

  2. You can then use the below given sample PL/SQL block with the

     above function to retrieve the lengths of long column:

     NOTE : Do not forget to replace the TABLE_NAME, LONG_COLUMN_NAME
     with your own table and long column names respectively.

     Before running the following block from SQL*PLUS, set the server
     output to some big buffer so that the dbms_output package can work
     properly.

  --------
     set serveroutput on size 100000;

     DECLARE
        len number;
        cRowid  varchar2(30);
        num number;
        cursor T_CURSOR is
           SELECT rowid
               FROM TABLE_NAME;
     BEGIN
        open T_CURSOR;
        LOOP
           FETCH  T_CURSOR
            INTO  cRowid;
     EXIT WHEN T_CURSOR%NOTFOUND;
           len := l_length('TABLE_NAME', 'LONG_COLUMN_NAME', cRowid);
           dbms_output.put_line(rowidtochar(cRowid) || '' ||
                             to_char(len, '999999'));
        END LOOP;
     END;






> -----Original Message-----

> From:	Charlie Mengler 

> Sent:	Wednesday, January 10, 2001 1:55 PM

> To:	Multiple recipients of list ORACLE-L

> Subject:	LONG RAW

>
> I'm supporting a 3rd party s/w application on an Oracle V8.1.6 instance.
> One table is highly fragmented and contains a LONG RAW field.
>
> Other than export/import what are some ways of doing a "reorg" of this
> table?
>
> Along these lines does anyone have a PL/SQL function or similar code
> that will return the size of the a LONG RAW field?
>
> Please keep in mind that since this is a purchased application,
> I have no control over the data types used within Oracle.
>
> TIA!
>
> --
> Charlie Mengler Maintenance Warehouse
> charliem_at_mwh.com 10641 Scripps Summit Ct
> 858-831-2229 San Diego, CA 92131
> While there are NO stupid questions, why am
> I plagued with LOTS of inquisitive idiots.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Charlie Mengler
> INET: charliem_at_mwh.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Jan 10 2001 - 14:00:36 CST