Oracle-L: RE: Size of a Long Field

From: Richard Ji <richard.ji_at_mobilespring.com>
Date: Wed, 05 Feb 2003 13:25:36 -0800
Message-ID: <F001.00545048.20030205132536@fatcity.com>

This is for LONG. For LONG RAW, you could use utl_raw.length or use a C or Java program to read it and calculate the length.

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;
/

-----Original Message-----
Sent: Wednesday, February 05, 2003 3:05 PM To: Multiple recipients of list ORACLE-L

Not easy. If your LONG is shorter than 32767, then you can, in a PL/SQL function, fetch it into a VARCHAR2 of this size (allowed in PL/SQL) and aplly LENGTH() to this VARCHAR2. Assuming the suitable Oracle version, I think there is somewhere a function to convert LONGs to CLOBs - to which you can apply a function. With Pro*C or the OCIs, you can derived the length from the error you get when fetching into too small a buffer ...
--

Regards,

Stephane Faroult
Oriole Software
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Stephane Faroult
INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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 also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Richard Ji
INET: richard.ji_at_mobilespring.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------