使用动态 SQL 方法2 执行DDL语句失败ORA-01027
问题描述
====================
您使用的是动态SQL方法2执行DDL语句包含绑定变量。当你执行程序，你碰到了Oracle错误ORA-01027：数据定义操作部允许绑定变量。
问题说明:
====================
在这个例子中，尝试使用动态SQL方法2创建一个视图。其结果是ORA-01027错误。
#define USERNAME "SCOTT"
#define PASSWORD "TIGER"
#include
#include
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE oraca;
EXEC ORACLE OPTION (ORACA=YES);
EXEC SQL BEGIN DECLARE SECTION;
char *username = USERNAME;
char *password = PASSWORD;
VARCHAR sqlstmt[80];
varchar myempno[7];
EXEC SQL END DECLARE SECTION;
main()
{
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
oraca.orastxtf = ORASTFERR;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
puts((char *) "\nConnected to ORACLE.\n");
strcpy(myempno.arr, "'7%'");
myempno.len=strlen(myempno.arr);
strcpy((char *) sqlstmt.arr,
"CREATE VIEW cpview AS SELECT empno FROM emp WHERE empno LIKE :v1");
sqlstmt.len = strlen((char *) sqlstmt.arr);
puts((char *) sqlstmt.arr);
printf(" v1 = %s\n", myempno.arr);
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL EXECUTE S USING :myempno;
EXEC SQL COMMIT RELEASE;
puts((char *) "\nHave a good day!\n");
exit(0);
sqlerror:
printf("\n%.*s\n", sqlca.sqlerrm.sqlerrml,
sqlca.sqlerrm.sqlerrmc);
printf("in \"%.*s...\"\n", oraca.orastxt.orastxtl,
oraca.orastxt.orastxtc);
printf("on line %d of %.*s.\n\n", oraca.oraslnr,
oraca.orasfnm.orasfnml,
oraca.orasfnm.orasfnmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
解决方案描述:
=====================
这应该返回一个ORA-01027错误，因为你不能有数据定义语句中的绑定变量。为了解决这个问题，您可以连接到主机变量值的语句的其余部分，这样问题将在运行前将得到解决。然后，您可以使用动态SQL方法1，执行该语句。
解决方案说明:
=====================
例如:
这里是代码的更正版本的问题。
#define USERNAME "SCOTT"
#define PASSWORD "TIGER"
#include
#include
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE oraca;
EXEC ORACLE OPTION (ORACA=YES);
EXEC SQL BEGIN DECLARE SECTION;
char *username = USERNAME;
char *password = PASSWORD;
VARCHAR sqlstmt[80];
varchar myempno[7];
EXEC SQL END DECLARE SECTION;
main()
{
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
oraca.orastxtf = ORASTFERR;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
puts((char *) "\nConnected to ORACLE.\n");
strcpy(myempno.arr, "'7%'");
myempno.len=strlen(myempno.arr);
strcpy((char *) sqlstmt.arr,
"CREATE VIEW cpview AS SELECT empno FROM emp WHERE empno LIKE ");
strcat(sqlstmt.arr, myempno.arr);
sqlstmt.len = strlen((char *) sqlstmt.arr);
puts((char *) sqlstmt.arr);
EXEC SQL EXECUTE IMMEDIATE :sqlstmt;
EXEC SQL COMMIT RELEASE;
puts((char *) "\nHave a good day!\n");
exit(0);
sqlerror:
printf("\n%.*s\n", sqlca.sqlerrm.sqlerrml,
sqlca.sqlerrm.sqlerrmc);
printf("in \"%.*s...\"\n", oraca.orastxt.orastxtl,
oraca.orastxt.orastxtc);
printf("on line %d of %.*s.\n\n", oraca.oraslnr,
oraca.orasfnm.orasfnml,
oraca.orasfnm.orasfnmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK RELEASE;
exit(1);
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2012-4-12 9:58:22 |
赞(
操作
