From: KUBO Takehiro <kubo@...>
Date: 2004-03-28T23:41:47+09:00
Subject: Re: DBI, Oracle, output params and dbms_output

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