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

╰半橙微兮° 2024-04-18 01:27 7阅读 0赞

开发人员反映，在调用带有变量的动态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/