ActiveRecord, Oracle and stored procedures

August 29, 2007 at 12:07 pm 4 comments

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

Entry filed under: oracle, rails.

Of course it’s the Database, I don’t need a Profiler Setting up a Sun Enterprise 250 (E250)

4 Comments Add your own

  • 1. Brent  |  November 17, 2007 at 4:08 pm

    First off, I just wanted to say thank you. I’ve been searching the internet for hours trying to figure out how to call an Oracle store procedure via ActiveRecord. I was just wondering if you could give an example of how to use this code, specifically showing how to make the method call, and how to grab the return parameters. That would be great.

    Reply
  • 2. Stephen  |  November 20, 2007 at 8:52 am

    Thanks for the comment – an example of using the code is given below:

    class MyClass < ActiveRecotrd::Base
    def execute
    xmltext = 'some string to send to stored proc
    cursor = connection._cursor("BEGIN mypkg.proc(:inxml, :outstatus, :outxml, false, null); end;");
    # Simple varchar2 input var
    cursor.bind_param("inxml", xmltext)
    # Varchar2 output var - 10 character max
    cursor.bind_param("outstatus", nil, String, 10)
    # CLOB output - 128K max
    cursor.bind_param("outxml", nil, OCI8::CLOB, 128000)
    # Run the proc
    cursor.exec
    # Read the results into instance variables.
    @status = cursor['outstatus']
    @outxml = cursor['outxml'].read
    cursor.close
    end

    Reply
  • 3. andrew  |  February 6, 2008 at 5:59 am

    stuck stuck stuck

    here is my stored proc

    CREATE OR REPLACE PROCEDURE hello_xml(name_in IN VARCHAR2, xml_out IN OUT VARCHAR2) IS
    BEGIN
    xml_out := ” || name_in || ”;
    END hello_xml;

    here is some code that should work (but does not of course!)

    cursor = ActiveRecord::Base.connection._cursor(“BEGIN hello_xml(:name_in, :xml_out); end;”);

    name_in = ‘andrew’
    cursor.bind_param(“:name_in”, name_in)
    cursor.bind_param(‘:xml_out’, nil, String, 1000)
    cursor.exec

    cursor[‘name_in’]
    cursor[‘xml_out’]

    here is the output…

    >> cursor.exec
    cu=> true
    >>
    ?> cursor[‘name_in’]
    => nil
    >> cursor[‘xml_out’]
    => nil

    can you please help me past this ! thanks

    Reply
  • 4. Oracle Stored Procedures with ActiveRecord « Socklog  |  April 8, 2008 at 10:43 pm

    […] Here’s a summary of the solution found at Software bits and pieces: […]

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Feeds


%d bloggers like this: