Hi Jeremy, As per your suggestion. This works...
............ java_import "java.sql.Types" java_import "oracle.jdbc.OracleTypes" ............... @@db.synchronize do |conn| cs = conn.prepareCall('{call SpInsertCustomer(?, ?,?,?,?,?)}') cs.registerOutParameter(1, Types::VARCHAR) cs.setString(2, 'Test Customer') cs.setInt(3, 99) cs.setInt(4, 18) cs.setInt(5, 2) cs.setString(6, 'XYZ10000') cs.executeQuery newCustomerID = cs.getString(1) puts newCustomerID end On Wednesday, October 27, 2021 at 5:06:25 PM UTC+2 Dylan Camilleri wrote: > Thank you Jeremy, > > I am getting this if I switch to :select > > Sequel::DatabaseError: Java::JavaSql::SQLException: Cannot perform fetch > on a PLSQL statement: next > raise_error at > C:/app/dylanca/jruby-9.3.1.0/lib/ruby/gems/shared/gems/sequel-5.49.0/lib/sequel/database/misc.rb:443 > call_sproc at > C:/app/dylanca/jruby-9.3.1.0/lib/ruby/gems/shared/gems/sequel-5.49.0/lib/sequel/adapters/jdbc.rb:208 > > reference: > > result = > @@db.dataset.call_sproc(:select,"SpInsertCustomer",out_param,"Test > Customer",99,18,2,"123456",nil) > > Thanks, > > Dylan > > On Wednesday, October 27, 2021 at 4:28:36 PM UTC+2 Jeremy Evans wrote: > >> On Wed, Oct 27, 2021 at 5:43 AM Dylan Camilleri <dca...@gmail.com> wrote: >> >>> An update: >>> >>> This works and executes the procedure and "creates the customer" >>> >>> out_param = 0 >>> result = >>> @@db.dataset.call_sproc(:update,"SpInsertCustomer",out_param,"Test >>> Customer",99,18,2,"123456",nil) >>> >>> puts out_param >> 0 (no change) >>> puts result >> nil (no return value) >>> >>> Basically the issue is I am not getting back the value from the executed >>> procedure result: >>> >>> Oracle definition: >>> >>> PROCEDURE SpInsertCustomer( >>> *pCustomerID* *OUT* NUMBER, >>> pTitle VARCHAR2 , >>> pProduct IN NUMBER , >>> pType NUMBER , >>> pStatus NUMBER , >>> pCustomerNo VARCHAR2 , >>> pPassword VARCHAR2 DEFAULT NULL) >>> >> >> out_param will always be 0. Integers are immutable in Ruby, and a method >> call cannot change the value of a local variable of the caller. >> >> You could try switching update to select and see if the return value >> includes the OUT column. If that doesn't work, you'll need to use the JDBC >> API directly (see the JDBC documentation for how to do that): >> >> @@db.synchronize do |conn| >> # conn is the JDBC connection object >> end >> >> Thanks, >> Jeremy >> > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/5e2b7bd7-7bdf-449a-a077-3223f13d7579n%40googlegroups.com.