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

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

点击(此处)折叠或打开

1. DECLARE
   
2. V_DOMAIN_NAME varchar2(50);
   
3. BEGIN
   
4. 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';
   
5. EXECUTE IMMEDIATE 'ALTER TABLE OSMSR_EUICCS ADD (C_DOMAIN_NAME varchar2(50) DEFAULT :1)' using V_DOMAIN_NAME;
   
6. 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语法，将引用的变量拼接成完整的语句。

点击(此处)折叠或打开

1. DECLARE
   
2. V_DOMAIN_NAME varchar2(50);
   
3. plsql_block VARCHAR2(500);
   
4. BEGIN
   
5. 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';
   
6. plsql_block := 'ALTER TABLE OSMSR_EUICCS ADD (C_DOMAIN_NAME varchar2(50) DEFAULT ''' || V_DOMAIN_NAME || ''')';
   
7.     EXECUTE IMMEDIATE plsql_block;
   
8. END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2136962/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2136962/