{"id":343,"date":"2014-06-10T20:33:05","date_gmt":"2014-06-10T20:33:05","guid":{"rendered":"http:\/\/blogs.nd.edu\/devops\/?p=343"},"modified":"2014-06-19T17:58:14","modified_gmt":"2014-06-19T17:58:14","slug":"calling-oracle-stored-procedures-from-ruby-with-ruby-plsql","status":"publish","type":"post","link":"https:\/\/sites.nd.edu\/devops\/2014\/06\/10\/calling-oracle-stored-procedures-from-ruby-with-ruby-plsql\/","title":{"rendered":"Calling Oracle Stored Procedures from Ruby with ruby-plsql"},"content":{"rendered":"

Isn’t it nice when something just works? \u00a0We are building Ruby on Rails apps on top of Oracle, so we’re using the Oracle Enhanced ActiveRecord<\/a> adapter on top of the ruby-oci8<\/a> driver library.<\/p>\n

The ActiveRecord adapter gives us a nice AR wrapper around our existing Oracle schema, which is great, but what about when I want to work with stored procedures or functions? \u00a0Turns out the author of this adapter, Raimonds Simanovskis<\/a>, has a gem just for this called ruby-plsql<\/a>.<\/p>\n

Include the gem in your Gemfile:<\/p>\n

gem 'ruby-plsql'<\/pre>\n
Then, write an initializer that hooks it to your existing ActiveRecord connection (config\/plsql.rb<\/strong>)<\/p>\n
plsql.activerecord_class = ActiveRecord::Base<\/pre>\n
After that, calling procedure is easy. \u00a0Oracle return types are automatically cast to ruby types. \u00a0Oracle exceptions are raised as OciError<\/strong>, which contains a “code” and “sql” attribute. \u00a0However, you can call a “message” method on that exception to get the full error output.<\/p>\n
Here I call an Oracle procedure, idcard.nd_is_valid_pin<\/strong> using the plsql<\/strong> object provided in the gem:<\/p>\n
ok_pin = plsql.idcard.nd_is_valid_pin( new_pin )\r\n  if ok_pin\r\n    plsql.idcard.update_pin_pr( @info.ndid, params[:old_pin], pin )\r\n  else\r\n    raise Errors::InvalidInput\r\n  end\r\nrescue OCIError => e\r\n render json: { error: e.message }, status: :unprocessable_entity<\/pre>\n
That’s it! \u00a0Nice and easy, and “rsims” is two for two.<\/p>\n","protected":false},"excerpt":{"rendered":"
Isn’t it nice when something just works? \u00a0We are building Ruby on Rails apps on top of Oracle, so we’re using the Oracle Enhanced ActiveRecord adapter on top of the ruby-oci8 driver library. The ActiveRecord adapter gives us a nice … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1550,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-343","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/posts\/343","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/users\/1550"}],"replies":[{"embeddable":true,"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/comments?post=343"}],"version-history":[{"count":2,"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/posts\/343\/revisions"}],"predecessor-version":[{"id":347,"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/posts\/343\/revisions\/347"}],"wp:attachment":[{"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/media?parent=343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/categories?post=343"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sites.nd.edu\/devops\/wp-json\/wp\/v2\/tags?post=343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}