Archive for August, 2007

ActiveRecord, Oracle and stored procedures

After much painful searching for a built in way to get ActiveRecord to execute a stored procedure on Oracle complete with in and out bind variables I have concluded that its just not possible.

The Oracle connection adapter uses OCI8 under the covers to connect to Oracle and many of its methods are exposed to you. To execute a stored procedure with OCI 8, one method is like this:


require 'oci8'
conn = OCI8.new(user, password, db.world)
res = conn.exec('begin my_proc(:in, :out); end;', 'invalue', 'outvalue')

That’s pretty simple, I guess. To do such magical things with ActiveRecord, a method ‘execute’ is exposed, this looks like this:


def execute(sql, name = nil) #:nodoc:
  log(sql, name) { @connection.exec sql }
end

The method spec for OCI8#exec is however


def exec(sql, *args, &blk)
  ...
end

So ActiveRecord gives us a wrapper to exec, but sadly removes the essential bind variables.

In my application I added the following code:


class ActiveRecord::ConnectionAdapters::OracleAdapter
  def _exec(stm, *args)
    @connection.exec(stm, *args)
  end

  def _cursor(stm)
    @connection.parse(stm)
  end
end

Which lets me execute Oracle stored procedures using my ActiveRecord connection at last!

My application is not a Rails application as such, more an application that uses ActiveRecord, so I put the above code in my boot script after including ActiveRecord – not sure exactly where you would put it in a Rails app.

If there is a better built in way to do this, please comment and let me know as I have not been able to find it!

UPDATE – it seems there are a few other struggling to get this stuff to work – I have added a chunk of code below that makes this stuff work:


require "rubygems"
require_gem "activerecord"

ActiveRecord::Base.establish_connection(:adapter => "oracle",
                                        :database => "avdev7",
                                        :username => "sodonnel2",
                                        :password => "sodonnel2")

class ActiveRecord::ConnectionAdapters::OracleAdapter
  def _exec(stm, *args)
    @connection.exec(stm, *args)
  end

  def _cursor(stm)
    @connection.parse(stm)
  end
end

# create or replace procedure testproc(inxml in varchar2, outxml out varchar2)
# is
# begin
#     outxml := inxml;
# end;
# /

class PLSQL < ActiveRecord::Base
  attr_accessor :outxml
  def initialize
    execute
  end

  def execute
    cursor = connection._cursor("BEGIN testproc(:inxml, :outxml);  end;");
    xmltext = 'some text to print'
    cursor.bind_param("inxml", xmltext)
    cursor.bind_param("outxml", nil, String, 1000)
    cursor.exec
    @outxml = cursor['outxml']
    cursor.close
  end
end

obj = PLSQL.new
puts "The returned value is : #{obj.outxml}"

Advertisements

August 29, 2007 at 12:07 pm 4 comments


Feeds