A couple DBMS_SQL limits
While developing a dynamic SQL example in Oracle 11g that builds a query based on available display columns, I found two interesting error messages. Now instead of noting it for the umpteenth time, I’m documenting it for everybody. The error messages are generated when this DBMS_SQL
package’s statement is a SELECT
statement, and is executed with either a BLOB
, BFILE
or CFILE
column in the list of returned columns.
26 | STATUS := dbms_sql.execute(stmt); |
BLOB
data type
You get the following error when a column in the query has a BLOB
data type. If you alter the query to exclude the column, no error occurs.
BEGIN test('DEMO'); END; * ERROR at line 1: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB ORA-06512: at "SYS.DBMS_SQL", line 1575 ORA-06512: at "STUDENT.TEST", line 26 ORA-06512: at line 1 |
BFILE
or CFILE
data type
You get the following error when a column in the query has a BFILE
or CFILE
data type. If you alter the query to exclude the column, no error occurs.
BEGIN test('DEMO'); END; * ERROR at line 1: ORA-00932: inconsistent datatypes: expected NUMBER got FILE ORA-06512: at "SYS.DBMS_SQL", line 1575 ORA-06512: at "STUDENT.TEST", line 26 ORA-06512: at line 1 |
It’s never a joy to debug the DBMS_SQL
package, at least it’s never a joy for me. I hope this helps somebody sort out an issue more quickly.