CODEWITHSUNDEEP
oracle-databasejdbcgroovyplsql
AJR
AJR

Reputation: 589

Execute PL/SQL Block from Groovy

I need help executing a PL/SQL block from groovy. This PL/SQL Block calls a function that returns 1 or 0.

Here is what I have in groovy:

import groovy.sql.*;

Integer jobId = 41481;
String url = "jdbc:oracle:thin:@hostname:1527:SID";
String username = "Will";
String password = "password";
String driverClassName = "oracle.jdbc.driver.OracleDriver";

def sql = Sql.newInstance(url, username, password, driverClassName);

sql.call("""DECLARE
  JOBID NUMBER;
  v_Return NUMBER;
BEGIN
  JOBID := $jobId;
  v_Return := CALL_FUNC1(
  JOBID => JOBID
 );
END;""");

Running this code gives me the following error:

 Caught: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
at SQL.run(SQL.groovy:13)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)

The problem is that this driver isn’t in the classpath. How do we set the class path in groovy ??

Thanks

Upvotes: 0

Views: 1868

Answers (2)

Raphael
Raphael

Reputation: 1800

In order to load a class, the JVM needs to load the jar containing the compiled class.

Somewhere in your system there should be a jar provided by Oracle or you might need to download it. Expose the jar in the classpath of the app.

Groovy allows you to easily load jars programmaticaly, add this like at the top:

this.getClass().classLoader.rootLoader.addURL(new File("/path/to/OracleDriver.jar").toURL())

The other way is pass the classpath jars when calling from the command line:

groovy -cp /path/to/OracleDriver.jar yourScriptFile.groovy

If you're running with Intellij, check the docs here to add the jar: https://www.jetbrains.com/help/idea/2016.1/dependencies-tab.html

You can also annotate your class and groovy will download the necessary dependencies and load them up:

@GrabResolver(name='oracle', root='http://maven.oracle.com', m2Compatible='true')
@Grab('com.oracle:ojdbc14:10.2.0.3.0'') //this needs to be your specific driver

Upvotes: 0

Alex K.
Alex K.

Reputation: 774

groovy -cp "/path/to/your/driver/driver.jar" /path/to/your_script.groovy or you can use grape to manage dependencies - http://docs.groovy-lang.org/latest/html/documentation/grape.html

Upvotes: 1

Related Questions