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
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' !!! FailsFirst 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?
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 2Warning : 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.
Posts of Interest :
6 comments:
Just one thing... the type of an array of strings in Ruby is not String[], it is Array.
(String[] actually means calling the method called "[]" on the object called "String", which is not what you want. And there's only one array type in Ruby; Ruby's arrays will gladly accomomdate any object. This is one of the things that was hard to grok for me when I first wrote some Ruby code because I was used to type systems like Java's/C++'s. And Ruby's is just different/insane/wonderful. Anyway, I hope this little digression helps you...)
Perhaps throwing in Array instead of String[] in your first version might work. It should, by the principle of least surprise, but then I don't know the OCI8 driver.
I did dig down into OCI8 driver [some]. Neither "String[]" nor "Array" are supported. I hadn't tried "Array" until just now, but that doesn't work either.
Thanks,
Jason
About your first solution: Why using an OUT parameter in a PL/SQL FUNCTION? This will never work. OUT Parameters will only work in PL/SQL PROCEDURES.
In PL/SQL FUNCTIONS you have to use the return value. Like in your final solution. ;-)
Where can I plug a blogspot.com blog to display and highlight the code?
Sorry, what do you mean? Are you asking how I do the code formatting? If so, look at http://jasonvogel.blogspot.com/2009/01/syntax-highlighting-posted-code.html.
Hi, of course this post is in fact fastidious and I have learned lot of things from it about blogging. thanks.
Post a Comment