PLSQL call the function using Groovy

I have datasource and embadded Groovy Script configured, the database team created custom function as scheduler status to monitor job status.,

begin

-- Call the function

:result := dbodatabase_get_scheduler_status(status_code => :status_code);

end;

Could you please guide me how to define the PLSQL function in Groovy script.

Manog1978

Posted 3 years ago·Last reply 3 years ago

2 comments

Manog1978

OP3 years ago

Thank for your suggestion. i have defined the same in datadource.

import groovy.sql.Sql

// Set host variables.
def databaseName = instanceProps.get("wildvalue")
def host = hostProps.get("system.hostname")
def user = hostProps.get("oracle.${databaseName.trim()}.user") ?: hostProps.get("jdbc.oracle.user")
def pass = hostProps.get("oracle.${databaseName.trim()}.password") ?: hostProps.get("jdbc.oracle.pass")
def port = hostProps.get("oracle.${databaseName.trim()}.port")?.toInteger() ?: (hostProps.get("jdbc.oracle.port")?.toInteger() ?: 1521)
def queryOutput = [:]
def conn = [:]

However, i am unable to define the below function

begin

-- Call the function

:result := database_tools.get_scheduler_status(status_code => :status_code);

end;

Please provide some sample query.

kendall

·3 years ago

I think the import statement you’ll need for the necessary library is:

import groovy.sql.Sql

And because it’s Oracle, you’ll also need:

import oracle.jdbc.driver.OracleTypes

Then you’ll need to create a sql object, something similar to this:

Sql sql = Sql.newInstance("jdbc:oracle:thin:@hostname:1521:dbname", "username","password","oracle.jdbc.driver.OracleDriver")

And finally, insert your sql query inside parentheses, like this:

sql.call()

Hope this helps get you started!