首页 » ORACLE » PLS-00382: expression is of wrong type When using “execute immediate” dynamic SQL statement

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);

打赏

,

对不起,这篇文章暂时关闭评论。