開發人員反映，在呼叫帶有變數的動態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;

Oracle DDL動態SQL使用using語法報錯'ORA-01027: bind'

相關文章