I must state upfront that I’m a seasoned C#/Java developer but I have very little Oracle or Jira ScriptRunner experience.

The company I work for is running Jira v9.15.2 and I’m trying to execute an Oracle stored proc that updates some tables in Jenkins ScriptRunner, when a specific event is triggered. The stored proc has 4 parameters, of which the first 2 are optional and the 3rd is an out parameter.

I’ve written a script in IntelliJ that does what it needs to do (inserting a record):

    {  
   
        def sql = Sql.newInstance('my jdbc url', 'my username', 'my password',    'oracle.jdbc.OracleDriver')  
        sql.call("{CALL 'my schema.my package.my stored proc(?, ?, ?, ?)}'",
                [entity_no, null, Sql.VARCHAR, detected_by], { result ->
            String outString = result.toString()  
            if (outString.startsWith('Process Successful'))  
            {  
                //Some logic here  
            }  
        })  
        sql.close()  
    }  

However, when I try to run it in ScriptRunner it complains that it cannot find ‘oracle.jdbc.OracleDriver’. In any case, from the Jira documentation it seems that it is not recommended to do it this way.

So I created a Database connection in Resources:

Pool name: oracle
JDBC URL: ‘my jdbc url’
Driver class name: ‘oracle.jdbc.OracleDriver’ (from the “Show examples”)

The connection work fine when I test it in the input box provided

My Jira script looks like this:

    {  
        DatabaseUtil.withSql('oracle') {sql ->  
                sql.call("{CALL 'my schema.my package.my stored proc(?, ?, ?, ?)}'",
                [entity_no, null, Sql.VARCHAR, detected_by] , { result ->  
                    String outString = result.toString()  
                    if (outString.startsWith('Process Successful'))  
                    {  
                        //Some logic here  
                    } 
            }  
            })  
            sql.close()  
    }  

The IntelliJ version returns ‘Process Successful…’ in ‘result’, while the Jira version returns ” General Error: ORA-01403: no data found”

I have tried changing ‘Sql.VARCHAR’ to other types, as well as ‘Sql.out(Sql.VARCHAR.getType())’ and all iterations I can think of.

When I replace the stored proc with a select statement it returns the correct results for the select statement. We are not allowed to update tables except through stored procs.

When I give both optional parameters a value, the IntelliJ version returns a message that it is not allowed (as expected) but the Jira version still returns the ORA-01403.

Out of desperation I changed he name of the proc to something invalid, and it brought back the same. So I know I’m missing something fundamental but doesn’t know what it is.