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 🙂

 

 

Railscasts crawler (Download all screencasts easily)

I wrote this script, as I felt really annoying to download each screencast everytime I needed them. So I ended up writing script to automatically download all screencasts without hassle i.e crawler.

Its in Ruby of course 🙂 and requires simple Hpricot gem.
If you not having it. Just type this command in your terminal —

$gem install hpricot

–Rest include this script in your /lib folder
–Change the path in the script where you want to download all the screencasts
–Go to your projects development environment (script/console) and run the script by these commands–
video = Railscasts.new #new Railscasts object
video.start #will start downloading all screencasts from Railscasts
Note:
  1. If you stop the script in between manually, it will not download those screencasts that are already in your hard disk.
  2. All logs are maintained in Railsproject/log/railscasts.log.
  3. Handling all exceptions raised

Improvements/Suggestions  are appreciated.

Thanks
And yes script:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
</pre>
<pre># Author : Akshay Gupta</pre>
<pre>#file: railscasts.rb
# First check you have all gems installed. Place the script in /lib folder and run the script.
# I don't have expertize in ruby, please tell how it can be improved.
# change the path accordingly, where you want to save path
# My working env is on MacOS, one need to make some changes if running on Windows
<code>
require 'rubygems'
require 'hpricot'
require 'open-uri'
require 'logger'
$log = Logger.new('log/railscasts.log')
$path = "/Users/akshaygupta/railsvideo/railscasts/"
$stop = false

class Railscasts
attr_accessor :url

def initialize
  @@page = 1
  @@url  = "http://railscasts.com/episodes?page="
  start
end

def url
  @url = @@url+@@page.to_s
end

def start
  url
  build_doc
  screencasts_links
  download_screencasts
  next_page
  if !$stop
    start
  else
    puts "Successfully done :) Enjy all the screencasts"
  end
end

def build_doc
  begin
    $log.info("*********Fetching #{@url}***********")
    @doc = Hpricot(open(@url))
  rescue Exception =&gt; e
    $log.debug("Problem fetching #{e}")
  end
end

def screencasts_links
  begin
    @download_links =
      (@doc/".download/a[1]").collect {|a| (a.search("[@href]").first[:href])}
    $log.info(" All Download links on this page :\n #{@download_links}")
  rescue
    $log.info("Problem in download links")
  end
end

def download_screencasts
  @download_links.each do |mov|
    begin
      file = mov.split('/').last
      res = `cd #{$path}; ls | grep "#{file}"`
      if !res
        $log.info("Now downloading file #{file}")
        result = `cd #{$path}; wget "#{mov}"`
        if result
          $log.info("Successfully Downloaded #{file}")
        end
      else
        $log.info("Already downloaded #{file}")
      end
    rescue Exception =&gt; e
      $log.info("problem downloding file #{e}")
    end
  end
end

  def next_page
    if @@page &lt; 17
      @@page += 1
    else
      $log.info("All screencasts downloaded :-), Mission accomplished!!")
      $stop = true
    end
  end
end</code></pre>
<pre><span style="font-family: monospace;">