Technical
How To Execute Oracle Procedures

Ways of executing Oracle procedure in rails.

I found one case where procedure’s name can be case sensitive, in the first case procedure was failing yet in the second it executed correctly.

ActiveRecord::Base.connection.execute("call pkg_ged_process.prc_monitor_adhoc_queue()")

 ActiveRecord::Base.connection.execute("begin pkg_ged_process.prc_monitor_adhoc_queue; end;")

To use Oracle’s stored proc with the Cursor

This was mostly discovered/tested e.t.c by my co-worker Eric Farkas I just take a credit for th e wikiing it.

#oracle schema is an interface defined in config/database.yml
@db_config = Rails::Configuration.new.database_configuration["oracle_schema"]
 def self.execute_stored_procedure_for(date,product_id,procedure_name)
   summary = Array.new  
   begin
     conn = OCI8.new(@db_config["username"],@db_config["password"],@db_config["host"])
     procedure = conn.parse("call #{procedure_name}(:business_date,:product_id,:cursor,:error_code,:error_message)")
     procedure.bind_param(':business_date',date)
     procedure.bind_param(':product_id',product_id.to_i)
     procedure.bind_param(':cursor',OCI8::Cursor)
     procedure.bind_param(':error_code',Fixnum)
     procedure.bind_param(':error_message',nil,String,100)
     procedure.exec
     cursor = procedure[':cursor']
     cursor.prefetch_rows = 1000

     while row = cursor.fetch_hash()
        summary << Attributor.new(row) 
     end

   rescue Exception => e
     logger.debug("-- There was an error: #{e.message}---")     
   ensure
     procedure.close unless procedure.blank?
     cursor.close unless cursor.blank?
     conn.logoff unless cursor.blank?
     conn = nil 
   end
    summary
  end

That looks absolutely ugly but it looks the same way on other languages, and thats how oracle lets you use it.