# Get return from oracle procedure **URL:** \ **Category:** Rails\ **Created:** [October 22, 2009, 8:05pm UTC](https://www.ruby-forum.com/t/get-return-from-oracle-procedure/177737 "2009-10-22T20:05:51Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![rafaelrosafu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@rafaelrosafu](https://www.ruby-forum.com/u/rafaelrosafu)\ **Post date:** [October 22, 2009, 8:05pm UTC](https://www.ruby-forum.com/t/get-return-from-oracle-procedure/177737/1 "2009-10-22T20:05:51Z")
Hi all, I have a procedure in which I pass two integers and two dates and get two integer as the result.How could I read the response with ROR? Here´s my code: ActiveRecord::Base.connection.execute(“DECLARE RESULTV NUMBER; RESULTBC NUMBER; BEGIN PODEVIAJAR(112,to\_date(‘21/10/2009’,‘DD/MM/YYYY’),to\_date(‘22/10/2009’,‘DD/MM/YYYY’),RESULTV,RESULTBC,26112); END;”) How could I fetch the value of the RESULTV and RESULTBC variables? Thanks, Rafael R. ---
**Author:** ![rafaelrosafu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@rafaelrosafu](https://www.ruby-forum.com/u/rafaelrosafu)\ **Post date:** [October 22, 2009, 8:21pm UTC](https://www.ruby-forum.com/t/get-return-from-oracle-procedure/177737/2 "2009-10-22T20:21:09Z")
On Thu, Oct 22, 2009 at 11:05 AM, Rafael R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > I have a procedure in which I pass two integers and two dates and get > two integer as the result.How could I read the response with ROR? > > Here´s my code: > ActiveRecord::Base.connection.execute("DECLARE RESULTV NUMBER;… > How could I fetch the value of the RESULTV and RESULTBC variables? Doesn’t that return a resultset? – Hassan S. ------------------------ removed\_email\_address@domain.invalid twitter: @hassan ---
**Author:** ![rafaelrosafu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@rafaelrosafu](https://www.ruby-forum.com/u/rafaelrosafu)\ **Post date:** [October 22, 2009, 8:37pm UTC](https://www.ruby-forum.com/t/get-return-from-oracle-procedure/177737/3 "2009-10-22T20:37:45Z")
It should return me two integers,but it´s returning “1”. ---
**Author:** ![rafaelrosafu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@rafaelrosafu](https://www.ruby-forum.com/u/rafaelrosafu)\ **Post date:** [October 22, 2009, 8:45pm UTC](https://www.ruby-forum.com/t/get-return-from-oracle-procedure/177737/4 "2009-10-22T20:45:10Z")
On Thu, Oct 22, 2009 at 11:37 AM, Rafael R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > It should return me two integers,but it´s returning “1”. OK, sorry – I don’t know anything about Oracle procedures, but if I execute a query using ActiveRecord::Base.connection.execute(sql) it returns a resultset, e.g. (via script/console) > > result = ActiveRecord::Base.connection.execute(“select 1”) > > =\> #Mysql::Result:0x1fe43a8 > > result.fetch\_row > > =\> [“1”] ## HTH, Hassan S. ------------------------ removed\_email\_address@domain.invalid twitter: @hassan ---
**Author:** ![rafaelrosafu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@rafaelrosafu](https://www.ruby-forum.com/u/rafaelrosafu)\ **Post date:** [October 23, 2009, 4:42pm UTC](https://www.ruby-forum.com/t/get-return-from-oracle-procedure/177737/5 "2009-10-23T16:42:18Z")
I believe the problem that oracle procedures do not return a value (oracle defines procedures and functions differently). The easiest way to interact with PLSQL procedures is to use the plsql gem [http://ruby-plsql.rubyforge.org/](http://ruby-plsql.rubyforge.org/) or [GitHub - rsim/ruby-plsql: ruby-plsql gem provides simple Ruby API for calling Oracle PL/SQL procedures. It could be used both for accessing Oracle PL/SQL API procedures in legacy applications as well as it could be used to create PL/SQL unit tests using Ruby testing libraries.](http://github.com/rsim/ruby-plsql). Read through the ruby-plsql documentation to see exactly how to use it but it will be something like: # you could name the argurments with :a\_number=\>112, :start\_date=\>‘21/10/2009’ etc. based on the parameters of podeviajar result = plsql.podeviajar(112, ‘21/10/2009’, ‘22/10/2009’, 1, 1,26112); puts result[:RESULTV] puts result[:RESULTBC] Hope this helps Alex ---
**Author:** ![rafaelrosafu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@rafaelrosafu](https://www.ruby-forum.com/u/rafaelrosafu)\ **Post date:** [October 22, 2009, 11:18pm UTC](https://www.ruby-forum.com/t/get-return-from-oracle-procedure/177737/6 "2009-10-22T23:18:35Z")
This doesn’t answer your question directly, but you might want to read about how to obtain Oracle debug output in your Rails app/log file: [http://www.alexrothenberg.com/2009/08/how-to-capture-oracles-dbmsoutput-in.html](http://www.alexrothenberg.com/2009/08/how-to-capture-oracles-dbmsoutput-in.html) I believe Alex’s patch was recently added to the Oracle Enhanced Adapter… if you’re using that and upgrade to the latest version that includes the patch, you’ll be able to put some debug/log code in your PL/SQL and get maybe get some idea of what’s going on. - pat [http://patshaughnessy.net](http://patshaughnessy.net) On Oct 22, 2:43 pm, Hassan S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)