Ruby: Invoking a PL/SQL Package with Array args

I'm fairly new to Ruby (and subsequently to Rails). I would be normally be labeled an "Enterprise Developer"... yada, yada. Anyway, I'm trying to invoke a Oracle PL/SQL Package from Ruby that has IN and OUT arguments that are arrays (TABLE OF VARCHAR2 INDEX BY BINARY-INTEGER, in Oracle PL/SQL terms). We have a fairly extensive library of PL/SQL that I want to reuse. This technique also works with Oracle Types (CREATE OR REPLACE TYPE PROD_TYPES.TYPE_STRING_ARRAY AS TABLE OF VARCHAR2(2000);).

Related Posts :

• http://rubyforge.org/projects/ruby-oci8/
• http://wiki.rubyonrails.org/rails/pages/OracleStoredProceduresAsDataSource
• http://www.oracle.com/technology/pub/articles/haefel-oracle-ruby.html

I've spent several weeks trying to come up with a solution. First, the best solution would be for Oracle to write the OCI driver for Ruby. OCI8 [http://rubyforge.org/projects/ruby-oci8/] (by Kubo Takehiro) is wonderful, but he is still a person. For companies to adopt and support Ruby/Rails, the support needs to be more robust. Upper Management and Operations resist open source. Single person supported software is easier for them to dismiss. I would prefer that Oracle had an OCI expert write and maintain the library (along the lines of their support for PHP). If this were the case, then the driver could support returning arrays natively.

My PL/SQL Packages :

CREATE OR REPLACE PACKAGE common_func IS
TYPE string_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
END common_func;
/
CREATE OR REPLACE PACKAGE BODY common_func
IS
BEGIN
   NULL;
END common_func;
/
create or replace package ruby_test is

function f_ruby(s in number,t out varchar2,st out common_func.string_table)

end ruby_test;
/
create or replace package body ruby_test is

function f_ruby(s in number,t out varchar2,st out common_func.string_table)
return varchar2
is
begin
   t := 'outta here';
   st(1) := 'array 1';
   st(2) := 'array 2';
   return 'Ruby rocks '||TO_CHAR(NVL(s,5))||' times!';
end;

begin
   null;
end ruby_test;

Desired Ruby :

cursor = conn.parse("BEGIN :result := ruby_test.f_ruby(s => :in,t => :out,st => st); END;")

cursor.bind_param(':result', nil, String, 100)
cursor.bind_param(':in', 10)
cursor.bind_param(':out', nil, String, 100)
# cursor.bind_param(':out_array', Array, 100) <= I tried this too!
cursor.bind_param(':out_array', String[], 100)

cursor.exec()

p cursor[':result'] # => 'Ruby rocks 10 times!'
p cursor[':out'] # => 'outta here'
p cursor[':out_array'] # => 'st(1) = array 1, st(2) = array 2' !!! Fails

First Solution : Convert the array to a string, bind to that string, and then split the delimited string apart on the Ruby side. I don't care for this solution for a couple of reasons :

• Size issue at 32K
• What delimiter should I use? How do I know that it will be the "right" delimiter?

First Solution (Revised) : I could switch from a string bind argument to a CLOB. OCI8 supports CLOBs, but I couldn't get it to work. The documentation is incomplete, and I'm not fluent enough in Ruby. Hints back to my issue with the library being solely maintained.

Current Solution : Convert the string array to a reference cursor (SYS_REFCURSOR) and use OCI8's bind to OCI::CURSOR support. Type :

CREATE OR REPLACE TYPE PROD_TYPES.TYPE_STRING_ARRAY AS TABLE OF VARCHAR2(2000)

Cursor Package :

CREATE OR REPLACE PACKAGE cursor_func IS
/**
Converts an array into a SYS_REFCURSOR (System Reference Cursor)

@Return
{*} SysRefCursor Success
{*} Exception Error
*/
FUNCTION f_array_to_SYSREFCURSOR(
   st_array_in       IN   common_func.STRING_TABLE )
RETURN SYS_REFCURSOR;
PRAGMA RESTRICT_REFERENCES(f_array_to_sysrefcursor,WNDS,TRUST);

END cursor_func;
/
CREATE OR REPLACE PACKAGE BODY cursor_func IS

/**
Converts an array into a SYS_REFCURSOR (System Reference Cursor)

@Return
{*} SysRefCursor Success
{*} Exception Error
*/
FUNCTION f_array_to_SYSREFCURSOR(
   st_array_in       IN   common_func.STRING_TABLE )
RETURN SYS_REFCURSOR
IS
   lsysrefcursor_array  SYS_REFCURSOR;
   le_error      EXCEPTION;

   lst_prodtypes    prod_types.type_string_array;

BEGIN

   lst_prodtypes := common_func.convert_table(st_array_in);

  OPEN lsysrefcursor_array FOR
     SELECT column_value FROM TABLE(cast(lst_prodtypes AS prod_types.type_string_array));

   RETURN lsysrefcursor_array;

END f_array_to_SYSREFCURSOR;

BEGIN
   Null;
END cursor_func;

Ruby Source :

plsql = conn.parse(
"DECLARE "+
"   st common_func.string_table; " +
"BEGIN "+
"   :result := ruby_test.f_ruby(s => :in,t => :out, st => st); " +
"   :cst := cursor_func.f_array_to_SYSREFCURSOR(st_array_in => st); " +
"END;")

plsql.bind_param(':result', nil, String, 100)
plsql.bind_param(':in', 10)
plsql.bind_param(':out', nil, String, 100)
plsql.bind_param(':cst', OCI8::Cursor)

plsql.exec()

puts "\nResults from returning a SysRefCursor\n"

p plsql[':result'] # => 'Ruby rocks 10 times!'
p plsql[':out'] # => 'outta here'

cursor = plsql[':cst']

plsql.close

x = ''
while r = cursor.fetch()
x = x + r.join(', ') + "\n"
end
cursor.close() # <= Don't forgot this

puts x

And success, finally!

Results from returning a SysRefCursor
"Ruby rocks 10 times"
"outta here"
array 1
array 2

Warning : There are two potential gotchas to this solution :

• If the developer forgets the cursor.close statement, the transaction could be jeopardized if there are too many cursors opened. I don't remember which ORA-##### this is.
• A single database session could run into issues with just having too many reference cursors open at any one instant.

Native support for arrays would be a blessing.

Posts of Interest :

• http://sqlrelay.sourceforge.net/sqlrelay/programming/rubydbi.html

Oracle SELECT with an array using an IN

declare

   -- CREATE OR REPLACE TYPE PROD_TYPES.TYPE_NUMBER_ARRAY AS TABLE OF NUMBER
  nt_src      prod_types.type_number_array := PROD_TYPES.TYPE_NUMBER_ARRAY();    -- Constructor     
  nt_dest     prod_types.type_number_array := PROD_TYPES.TYPE_NUMBER_ARRAY();    -- Constructor     

begin

  -- Test data (no order)
  select
    product_version_id
  bulk collect
    into
      nt_src
  from product_version v
  where v.product_version_id between 138120 and 140000;

  -- select * from table(sys.dbms_debug_vc2coll(1,2,'a')) order by 1 desc
 
  -- Data ordered
  select
    product_version_id
  bulk collect
    into 
      nt_dest
  from
    product_version
  where
    product_version.product_version_id in (select column_value from table(cast(nt_src AS prod_types.type_number_array)))
  order by
    product_version.expiration_date2 desc,
    product_version.base_price desc,
    product_version.expiration_date2 desc;

  for i in nt_dest.first .. nt_dest.last loop
    dbms_output.put_line(to_char(nt_dest(i)));
  end loop;

  exception
    when others then
      dbms_output.put_line('sqlerrm : '||sqlerrm);
end;