DBI, Oracle, output params and dbms_output

ruby-talk
1

Hi all,

Ruby 1.8.1
Oracle 9.2.x
DBI 0.21
oracle/oci8/ruby9i

When a PL/SQL stored proc has output parameters, is it possible to
grab those values in Ruby with the DBI module? In other words, what’s
the equivalent of the (Perl) DBI bind_param_inout() method? Or does
bind_param() work? My own tests were inconclusive (and my futzing
capabilities are limited at the moment).

Also, is there some way to redirect dbms_output into Ruby somehow?
I’ve got this sql script that is currently spool’ing output to a file.
I want to trap the dbms_output stuff in Ruby if possible.

Regards,

Dan

2

Hi,

djberg96@hotmail.com (Daniel Berger) writes:

Hi all,

Ruby 1.8.1
Oracle 9.2.x
DBI 0.21
oracle/oci8/ruby9i

When a PL/SQL stored proc has output parameters, is it possible to
grab those values in Ruby with the DBI module? In other words, what’s
the equivalent of the (Perl) DBI bind_param_inout() method? Or does
bind_param() work? My own tests were inconclusive (and my futzing
capabilities are limited at the moment).

oracle: work
dbh.do(“BEGIN DBMS_OUTPUT.ENABLE; END;”)
… use DBMS_OUTPUT.PUT_LINE …
dbh.prepare(“BEGIN DBMS_OUTPUT.GET_LINE(?, ?); END;”) do |sth|
outbuf = ’ ’ * 255
status = ‘0’ # parameter type is integer. but bind as string.
sth.bind_param(1, outbuf)
sth.bind_param(2, status)
while true
sth.execute
break if status != ‘0’
puts outbuf.sub(/ *$/, ‘’) # cut trailing space
end
end

oci8: work with a patch
original dbd_oci8 doesn’t support output parameters. To use them,
add the following code to DBI::DBD::OCI8::Statement.

def __bind_value(param)
  @cursor[param]
end

Then you can get output parameters as following.

dbh.do("BEGIN DBMS_OUTPUT.ENABLE; END;")
 ... use DBMS_OUTPUT.PUT_LINE ...
dbh.prepare("BEGIN DBMS_OUTPUT.GET_LINE(?, ?); END;") do |sth|
  sth.bind_param(1, ' ' * 255)
  sth.bind_param(2, 0)
  while true
    sth.execute
    break if sth.func(:bind_value, 2) != 0
    puts sth.func(:bind_value, 1)
  end
end

ruby9i: ???
I couldn’t run this. But as far as I look in the source code, I
guess it runs as following if it works correctly.

dbh.do("BEGIN DBMS_OUTPUT.ENABLE; END;")
 ... use DBMS_OUTPUT.PUT_LINE ...
dbh.prepare("BEGIN DBMS_OUTPUT.GET_LINE(?, ?); END;") do |sth|
  line = ... create an instance of Ruby9i::Varchar ...
  status = ... create an instance of Ruby9i::Number ...
  sth.bind_param(1, line)
  sth.bind_param(2, status)
  while true
    sth.execute
    break if status.to_i != 0
    puts line.to_s
  end 
end
···

–
KUBO Takehiro
email: kubo@jiubao.org
web: http://www.jiubao.org
GnuPG fingerprint = 5F7B C8EF CA16 57D0 FDE1 9F47 C001 1F93 AC08 2262