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."
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 LONGIs there another 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; /
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments