Forum Discussion

Manog1978's avatar
Manog1978
Icon for Neophyte rankNeophyte
2 years ago

PLSQL call the function using Groovy

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.

  • 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!

  • 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.