CODEWITHSUNDEEP
oracle-databasegroovy
Mukesh Singh
Mukesh Singh

Reputation: 323

How to run below PL/SQL code from groovy againts oracle database

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

Upvotes: 1

Views: 2080

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21115

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.

Upvotes: 3

Related Questions