Executing Oracle function and procedures with JDBC adapter for connection

[hogihung]

I'm attempting to build out some automated tests for Oracle PL/SQL. I've used Sequel with a ruby based app (api) at the job but at that time didn't need to call Oracle functions and procedures.

For example I have a procedure, ITM_UTILS.CLEANUP_DEBUG_LOG(p_start_date, p_num_of_days). I also have functions that return a value as well as procedures/functions that can specify IN/OUT parameters.

How can I use Sequel to execute my functions/procedures and when either returns a value, capture that?

Thank you.

John...

By the way, Perplexity AI provides an answer using DB.oracle_procedure() which I don't find in Sequel. It also said to try this:

call_proc = DB.oracle_procedure_expr("PACKAGE.PROCEDURE_NAME(:param1, :param2)", :param1 => value1, :param2 => value2)
call_proc.call

[jeremyevans]

You could try using DB.call_sproc('ITM_UTILS.CLEANUP_DEBUG_LOG', :args=>[p_start_date, p_num_of_days]). If the procedure returns rows, pass a block, which should be yielded each row. I don't think IN/OUT parameters are supported, though.

If that doesn't work, you'll probably have to drop down to the connection level and call the appropriate JDBC functions. that would be something like:

DB.synchronize do |conn|
  sproc = conn.prepareCall('SQL for stored procedure call')
  sproc.setDate(1, p_start_date)
  sproc.setLong(2, p_num_of_days)
  sproc.executeQuery
  # handle result of query
ensure
  sproc&.close
end

Unfortunately, I don't have much JDBC experience, as I've only dealt with JDBC when testing Sequel.