Rails: Calling Stored Procedure and read OUT variables in Sequel

Very recently stumbled upon a situation, where I need to call a Oracle DB Stored Procedure and also need to read the OUT variables in one of our Ruby on Rails application or in simple words read the output after calling stored procedure in one of our Rails application.

To add to our dismay, it’s not ActiveRecord connection but we were using Sequel library. Sequel do support the Oracle library and it’s very silimar to AR ORM and thanks to this wonderful gem that works out-of-the-box. But it’s not straight forward to call Stored PROC and read the OUT variables and there is next to no documentation to process OUT variables from our stored PROC

DB specific SQL can be executed using “execute” method and almost all the DB adapters provide this method. But question remains the same how to read the OUT variables and I have never called a Stored Procedure ever in my life. It seems an interesting problem to solve and answer may be hidden somewhere in the code but no documentation.

Let’s try to read the OUT variables in different ways taking one step at a time:

1. Create a sample procedure which takes one integer and one string as input and returns integer multiplied by 2 and UPPERCASE of string

create or replace
PROCEDURE MYPROC 
(
 age IN NUMBER 
, name IN VARCHAR2 
, fathers_age OUT NUMBER 
, capital_name OUT VARCHAR2 
) AS 
BEGIN
 fathers_age := age*2;
 capital_name := UPPER(name);
END MYPROC;

 

2. How to call using SQL and read OUT variables (in SQLDeveloper)

 var age number
 var naam varchar2
 var papa_age number
 var bada_naam varchar2
 exec :age := 20
 exec :naam := 'akshay'
 exec MYPROC (age => :age, name => :naam, fathers_age => :papa_age, capital_name => :bada_naam)
 print papa_age
 print bada_naam

OUTPUT

PAPA_AGE
--
40
BADA_NAAM
------
AKSHAY

 

3. How to call it using ActiveRecord using Ruby OCI8 GEM

cursor = ActiveRecord::Base.connection.raw_connection.parse("BEGIN MYPROC(:age, :name, :fathers_age, :capital_name); END;")
cursor.bind_param(:name, 'akshay')
cursor.bind_param(:age, '20')
cursor.bind_param(:fathers_age, nil, Fixnum)
cursor.bind_param(:capital_name, nil, String, 20)
cursor.exec()
p cursor[:fathers_age], cursor[:capital_name]

OUTPUT

1.8.7 :125 > cursor.exec()
 => 1
1.8.7 :126 > p cursor[:fathers_age], cursor[:capital_name]
40
"AKSHAY"

 

4. How to call it using Sequel Database Toolkit.

require 'sequel'
db = Sequel.connect(:adapter=>'oracle', :host=>'localhost', :database=>'db_name', :user=>'test', :password=>'test')
db.execute("begin MYPROC(:age, :name, :fathers_age, :capital_name); end;", {:arguments => [[20, "integer"], ["akshay", "string"], [nil, "integer"], [nil, "string"]]}) { |cursor| [cursor[3], cursor[4]] }

OUTPUT:

1.8.7 :158 > db.execute("begin MYPROC(:age, :name, :fathers_age, :capital_name); end;", {:arguments => [[20, "integer"], ["akshay", "string"], [nil, "integer"], [nil, "string"]]}) { |cursor| [cursor[3], cursor[4]] }
 => [40, "AKSHAY"]

 

This is important to note, oracle adapter inside sequel uses the Ruby OCI8 gem but the implementation is not straight forward as in the case of ActiveRecord.

To further understand how the variables are bound and how are we reading out the OUT variables, I recommend to read the code written inside the ORACLE adapter of SEQUEL gem.

Hope this bit of information is useful and many thanks to  for such a great sequel gem. Happy Hacking 🙂