Inconsistent datatypes in Oracle

Question

i have the following function:

create or replace
FUNCTION "MXUPGKEYVAL"(tbname varchar2,colname varchar2) return number is
val number;

BEGIN

EXECUTE IMMEDIATE
'select sum(length('||colname||')) from '||tbname into val;

return val;
END;

and the following update:

update ANINTEGDATA set val1=to_char(nvl(MXUPGKEYVAL(MX5T,MX5C),0)) where type=1;

when i execute the update i get:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-06512: at "MAXIMO.MXUPGKEYVAL", line 6
ORA-06512: at line 2

any idea why that happens?

Regards, Radu.

Later edit:

table ANINTEGDATA is:

create table ANINTEGDATA
(
  MX5T VARCHAR2(50),
  MX5C VARCHAR2(50),
  MX6T VARCHAR2(50),
  MX6C VARCHAR2(50),
  TYPE NUMBER,
  VAL1 VARCHAR2(200),
  VAL2 VARCHAR2(200)
);

Answer 1

Your function works.

SQL> select mxupgkeyval('EMP', 'SAL') from dual
  2  /

MXUPGKEYVAL('EMP','SAL')
------------------------
                      78

SQL>

Furthermore it works in your update statement.

SQL> update ANINTEGDATA set val1=to_char(nvl(MXUPGKEYVAL(MX5T,MX5C),0)) where type=1;

1 row updated.

SQL> 

Where it doesn't work is when the column in question has the LONG datatype. The error message isn't as clear as it could be but is clear enough.

SQL> alter table t34 add long_col long;

Table altered.

SQL> select mxupgkeyval('T34', 'LONG_COL') from dual
  2  /
select mxupgkeyval('T34', 'LONG_COL') from dual
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-06512: at "APC.MXUPGKEYVAL", line 6


SQL>

This is just another reason why LONG is Teh Suck! and should have been done away with a long time ago. As you're doing a data migration exercise now would be a good time to consider moving to the oh-so flexible CLOB data type.

Either way you need a convention to indicate that the target column contains a shedload of data.

If you really need to know the precise extent of the LONG data volumes I suggest you unload the LONGs to CLOB columns and sum those instead.