PLS-00382: expression is of wrong type When using "execute immediate" dynamic SQL statement

今天测式部门同事遇到的一个问题记录一下，开发库运行正常，同样的复制到测试库执行应用报错，应用错误日志如下

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00905: object ZYYJHJ.P_UPDATEEXAMEXAMINEE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

显然对象是invalid状态了，我们都知道invalid procedure通常在call 里会自动编译，下面说一下思路

sys@TESTDB>select object_type,status from dba_objects where object_name='P_UPDATEEXAMEXAMINEE';
OBJECT_TYPE         STATUS
------------------- -------
PROCEDURE           INVALID
sys@TESTDB>alter procedure zyyjhj.P_UPDATEEXAMEXAMINEE compile;
Warning: Procedure altered with compilation errors.
sys@TESTDB>show error
Errors for PROCEDURE ZYYJHJ.P_UPDATEEXAMEXAMINEE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
59/5     PL/SQL: Statement ignored
59/23    PLS-00382: expression is of wrong type

Tip:
可以看到procedure 中调用存在无效的数据类型，看一下59行的sql 调用

execute immediate v_sql using p_exam_id, p_user_id, p_exam_id;

我的方法是先把v_sql 打印出来，手动执行看是否出错，行前加入

dbms_output.put_line('sqltext:'||v_sql);

手动执行sql 是可以成功的，下面看一下v_sql的数据类型，发现是v_sql clob; 那就查一下，execute immediate 后跟的数据类型是否支持clob.
10g:

dynamic_string
A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type CHAR or VARCHAR2, not NCHAR or NVARCHAR2.

11g r1 or higher

dynamic_sql_stmt
A string literal, string variable, or string expression that represents any SQL statement. It must be of type CHAR, VARCHAR2, or CLOB.

Summary:
原因找到了，10G 时动态sql不支持clob, 测试库10g ,生产库11g ，所以建议生产环境要与测试环境要尽可能的一致，达到真实的测试效果。case临时解决方法是把10g 的v_sql 定义为 varchar2(32767);