From: Jason Sweat <jason.sweat@...>
Date: 2006-07-27T04:05:02+09:00
Subject: Re: oracle stored procs

On 7/26/06, AndrewMcDonagh <newsamd@amc.com> wrote:
> Hi,
>
> Has anyone managed to get calls to oracle stored procs to work yet?

Works fine for me out of the box.  Here are some extentions I made to
the OCI8 class to make it easier for me to deal with a ref cursor
returned from a package function:

class OCI8
  class Cursor
    def get_array
      ret = []
      while row = self.fetch_hash
        ret << row
      end
      ret
    end
    def bind(binds)
      binds.each_pair do |k,v|
        if v.respond_to? :indices
          self.bind_param(k, v[0], v[1], v[2])
        else
          self.bind_param(k, v)
        end
      end if binds
    end
  end

  def get_ref(plsql, binds=false, &block)
    cur = self.parse('begin :rs := '+plsql+'; end;')
    cur.bind_param(':rs', OCI8::Cursor)
    cur.bind(binds)
    cur.exec
    if block_given?
      arr = cur[':rs'].get_array.collect &block
    else
      cur[':rs'].get_array
    end
  end
end

-- 
Regards,
Jason
http://blog.casey-sweat.us/