Hi
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.
LM ChampionI 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!
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.
