Oracle Stored Procedures

[sam944]

Hi,

I need to call Oracle Stored Procedures from RUBY DBI Moudule using
OCI8

Can anyone provide me with a working Oracle Store Procedure along with
a RUBY DBI Script which calls it using the OCI8 Driver.

I want to find out how I can correctly pass a String IN param to it and
handle a String OUT Param from the stored Procedure in RUBY.

Thanks
Sam

 

[dave]

a new ruby book may help you....Enterprise Ruby.....

 

[sam944]

Are you referring to 'Enterprise Integration with Ruby' by Maik Schmidt
from Pragmatic Programmer?

I have this bug and didnt find teh relevant information in it?

Is it possibly a different book?

Thanks

 

[dave]

....yes...that's the book....oka... first do you have access to
metalink.oracle.com ie an oracle support account....i'm still
interested in your post because i'm a caretaker DBA for our billing
app...and we have UAVledg package which i would like to link to...here
some steps to try:
1.) log onto your oracle client sql and try to use the package inside
a select stmt
2.) could you list your package contents here
3.) contact all of not only the author of Enterprise for Ruby, but
also, the authors of the ruby dbi program interface..
in the mean time .... all persue my metalink research and get back to
the this post....dave

 

[dave]

....oka our billing db uses this method of accessing it's view pacakge
that maybe you could use.....
it transfers into a temp table with the session's key query info on the
fly, then uses this table as part of package to build the query.... as
follows....

1.) create a table that has:
CREATE TABLE xxxxxxx
(
xxxxxxx_SESS_ID NUMBER NOT NULL,
xxxxxxx_key_CODE NUMBER(9) NOT NULL,
xxxxxxx_KEY_CODE2 VARCHAR2(7),
xxxxxxx_KEY_DATE DATE
)

2.) the first thing that your app does is update this table with this
info....
xxxxxxx_sess_id = userenv('SESSIONID') <<<try this out:
(select userenv('SESSIONID') from dual
and all of your other key info that your package needs.... as an
insert to this xxxxxxx table....
3.) from within your package in your select from xxxxxxx and in your
where clause....
where
xxxxxxx_sess_id = userenv('SESSIONID')
package_table_key1 = xxxxxxx_key_CODE etc....maybe this oracle stuff
will help ease the ruby pain....then use the examples in the enterprise
ruby book in their simple form...maybe this will help....dave

 

[sam944]

Hi Dave,

Thanks for your feedback.
I dont have access to metalink.oracle.com. I am not clear with your
other feedback as to the package you are using. Do you want me to acess
it? Hva eyou had any experience working with Oracle Strored procedures
using the Ruby DBI/OCI8 package?

Thanks
Sam

 

[BITDOGER]

I'm a caretaker oracle db....no i haven't any experience using ruby
dbi/oci8 with a store package....but just trying to help....dave

 

[Jeffrey Hulten]

Check this out... http://textsnippets.com/posts/show/501

@connection = OCI8.new("fxblotter6", "fxblotter6", "fx10g")

plsql = @conn.parse("BEGIN P_CURRENCY.select_currencyout); END;")
plsql.bind_param('ut', OCI8::Cursor)
plsql.exec

cursor = plsql['ut']
plsql.close

x = ''
while r = cursor.fetch()
x = x + r.join(', ') + '<br>'
end

@out = x

@connection.logout