开发人员反映，在调用带有变量的动态SQL时报错

点击(此处)折叠或打开

DECLARE
V_DOMAIN_NAME varchar2(50);
BEGIN
SELECT C_PARAM_VALUE INTO V_DOMAIN_NAME FROM OAPS_PARAMETERS WHERE C_PRODUCT_TYPE='OSMSR' AND C_GROUP_NAME='security' AND C_PARAM_NAME='Scp80SekmsDomainName';
EXECUTE IMMEDIATE 'ALTER TABLE OSMSR_EUICCS ADD (C_DOMAIN_NAME varchar2(50) DEFAULT :1)' using V_DOMAIN_NAME;
END;

Error report:

ORA-01027: bind variables not allowed for data definition operations

ORA-06512: at line 9

01027. 00000 - "bind variables not allowed for data definition operations"

*Cause: An attempt was made to use a bind variable in a SQL data definition

operation.

*Action: Such bind variables are not allowed.

在Oracle的动态语句中，不允许使用USING语法调用带有变量的DDL SQL

解决方法：
去掉USING语法，将引用的变量拼接成完整的语句。

点击(此处)折叠或打开

DECLARE
V_DOMAIN_NAME varchar2(50);
plsql_block VARCHAR2(500);
BEGIN
SELECT C_PARAM_VALUE INTO V_DOMAIN_NAME FROM OAPS_PARAMETERS WHERE C_PRODUCT_TYPE='OSMSR' AND C_GROUP_NAME='security' AND C_PARAM_NAME='Scp80SekmsDomainName';
plsql_block := 'ALTER TABLE OSMSR_EUICCS ADD (C_DOMAIN_NAME varchar2(50) DEFAULT ''' || V_DOMAIN_NAME || ''')';
EXECUTE IMMEDIATE plsql_block;
END;

ITPub博客

Oracle DDL动态SQL使用using语法报错'ORA-01027: bind'