How can I get PL/SQL OUT parameters?

CREATE OR REPLACE PACKAGE my_package IS
  PROCEDURE my_proc(a1 IN number, a2 IN varchar2, b1 OUT number, b2 OUT varchar2);
END;
/
CREATE OR REPLACE PACKAGE BODY my_package IS
  PROCEDURE my_proc(a1 IN number, a2 IN varchar2, b1 OUT number, b2 OUT varchar2) IS
  BEGIN
    b1 := a1 * 2;
    b2 := UPPER(a2);
  END;
END;
/

Explicitly bind parameters by their names

 # use OCI8::Cursor#bind_param and OCI8::Cursor#exec
 cursor = conn.parse('begin my_package.my_proc(:a1, :a2, :b1, :b2); end;')
 cursor.bind_param(':a1', 123)
 cursor.bind_param(':a2', 'abc')
 # bind as a Fixnum. The value is NULL.
 cursor.bind_param(':b1', nil, Fixnum)
 # bind as a String. The max length is 20. The value is NULL.
 cursor.bind_param(':b2', nil, String, 20)
 cursor.exec()
 # If you bind the parameter by name, use the name.
 p cursor[':b1'] # => 246
 p cursor[':b2'] # => ABC

Explicitly bind parameters by their positions

 # use OCI8::Cursor#bind_param and OCI8::Cursor#exec
 cursor = conn.parse('begin my_package.my_proc(:a1, :a2, :b1, :b2); end;')
 cursor.bind_param(1, 123)
 cursor.bind_param(2, 'abc')
 cursor.bind_param(3, nil, Fixnum)
 cursor.bind_param(4, nil, String, 20)
 cursor.exec()
 # If you bind the parameter by its position, use the position.
 p cursor[3] # => 246
 p cursor[4] # => "ABC"

Implicitly bind parameters

 # use OCI8::Cursor#exec with bind parameters.
 cursor = conn.parse('begin my_package.my_proc(:a1, :a2, :b1, :b2); end;')
 cursor.exec(123, 'abc', [nil, Fixnum], [nil, String, 20])
 # The first argument is the bind value for the first position.
 # The second is for the second. etc.
 # If the value is a Array, the first element is the value to bind,
 # the second is type, the third is the maximum length.
 # They are bound by their position.
 p cursor[3] # => 246
 p cursor[4] # => "ABC"

Execute and get bind parameters at once.

 # OCI8#exec with bind parameters
 # It returns an array of all parameter values if the specified sql is a PL/SQL block.
 params = conn.exec('begin my_package.my_proc(:a1, :a2, :b1, :b2); end;',
                     123, 'abc', [nil, Fixnum], [nil, String, 20])
 p params[0] # => 123
 p params[1] # => "abc"
 p params[2] # => 246
 p params[3] # => "ABC"

Ruby/DBI itself doesn't support OUT parameters. It is done by a driver specific function.

 sth = dbh.prepare('begin my_package.my_proc(?, ?, ?, ?); end;')
 sth.bind_param(1, 123)
 sth.bind_param(2, 'abc')
 # Use nill with a type attribute to bind NULL value.
 sth.bind_param(3, nil, {'type' => Fixnum})
 # But as for String it cannot because there is no
 # way to set the maximum length.
 sth.bind_param(4, ' ' * 20)
 sth.execute
 # call a driver specific function.
 p sth.func(:bind_value, 3) # => 246
 p sth.func(:bind_value, 4) # => "ABC"

Get the raw connection and use it with ruby-oci8 API.

 conn = ActiveRecord::Base.connection.raw_connection
 ...