如何从groovy againts oracle数据库运行PL / SQL代码 [英] How to run below PL/SQL code from groovy againts oracle database

问题描述

  BEGIN 
 EXECUTE 
下面是我想从groovy程序针对oracle数据库运行的PL / SQL代码。 IMMEDIATE'DROP TABLE Employee'; 
 EXCEPTION 
当其他人
 IF SQLCODE！= -942 THEN 
 RAISE; 
 END IF; 
 END; 
 / 
  

我怎样才能得到它。我把所有的东西都安装好像从我的groovy程序连接到oracle数据库。
我想这样做如下所示：

  sql = Sql.newInstance（url，username，password，driver ）
 String plSql =BEGIN\\\
+ 
EXECUTE IMMEDIATEDROP TABLE Employee'; \\\
+ 
EXCEPTION \\\
+ 
WHEN OTHERS THEN \\\
+ 
IF SQLCODE！= -942 THEN \\\
+ 
RAISE; \\\
+ 
END IF; \\\
+ 
END; \\\
+ 
/
 
 sql.execute（plSql）
  

错误日志 - 来自评论

以下是我收到的错误...

  2015年6月29日9:05:52 groovy.sql.Sql执行WARNING：无法执行：BEGIN EXECUTE IMMEDIATE'DROP TABLE Employee';例外，如果其他人如果SQLCODE！= -942则升高;万一;结束; /因为：ORA-06550：第9行，第1列：PLS-00103：遇到符号/符号/被忽略。捕获：java.sql.SQLException：ORA-06550：第9行，第1列：PLS-00103：遇到符号/忽略符号/
  

解决方案

使用call方法执行PL / SQL块。
如上所述，不要添加斜杠，而是使用终止分号。

  groovyCon.call（BEGIN 
 EXECUTE IMMEDIATE'DROP TABLE Employee'; 
 EXCEPTION 
当其他人
 IF SQLCODE！= -942 THEN 
 RAISE; 
 END IF; 
 END;）
  

另外，您可能从Groovy多行字符串（ ）允许在数据库和Groovy之间直接剪切和粘贴PL / SQL块。

Below is PL/SQL code I want to run from groovy program against oracle database.

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE Employee';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

How can I get that. I have every thing setup like connecting to oracle database from my groovy program. I want to do it somthing like below:

sql = Sql.newInstance(url, username, password, driver)
String plSql="BEGIN\n" +
        "   EXECUTE IMMEDIATE 'DROP TABLE Employee';\n" +
        "EXCEPTION\n" +
        "   WHEN OTHERS THEN\n" +
        "      IF SQLCODE != -942 THEN\n" +
        "         RAISE;\n" +
        "      END IF;\n" +
        "END;\n" +
        "/"

sql.execute(plSql)

Error log-from comments

Below is error I am getting...

Jun 29, 2015 9:05:52 PM groovy.sql.Sql execute WARNING: Failed to execute: BEGIN EXECUTE IMMEDIATE 'DROP TABLE Employee'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / because: ORA-06550: line 9, column 1: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored. Caught: java.sql.SQLException: ORA-06550: line 9, column 1: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored

解决方案

Use the call method to execute a PL/SQL block. As mentioned above do not add slash, but use the terminating semicolon.

groovyCon.call("""BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE Employee';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;""")

Additionally you may profit from the Groovy multiline string (""") which allowes direct cut and paste of the PL/SQL block between database and Groovy.

这篇关于如何从groovy againts oracle数据库运行PL / SQL代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！