ORA-00932 using length function

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