Q

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 I received the following error: "ORA-00932: inconsistent datatypes: expected NUMBER got LONG."

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?
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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close