Ask the Expert

ORA-00932 using length function

An article on this Web site said the length function could tell me the length of a long datatype column. However, in SQL*Plus (10gr2), I received the following:
SQL> select length(commentaire) from cec.loggingprd where commentaire is
not null;
select length(commentaire) from cec.loggingprd where commentaire is not
null
              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Is there another function?

    Requires Free Membership to View

I got the following example from Metalink, Note:1036895.6:

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

This was first published in August 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: