How to pass arrays in and/or out of Oracle PL/SQL Package us

Ruby
1

Sorry to bug you. I can’t figure this out.

create or replace package ruby_test is

function f_ruby(s in number,t out varchar2,st out
common_func.STRING_TABLE)
return varchar2;

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(s)||’ times!’;
end;

begin
null;
end ruby_test;
/
cursor = conn.parse(“BEGIN :result := ruby_test.f_ruby(s => :in,t =>
:out,st => :out_array); END;”)
cursor.bind_param(’:in’, 10)
cursor.bind_param(’:result’, nil, String, 100)
cursor.bind_param(’:out’, nil, String, 100)
cursor.bind_param(’:out_array’, StringArray?)
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’

I can’t figure how what it takes to get the arrays to work.

Help…

Thanks,
Jason

2

Jason Vogel wrote:

/ …

I can’t figure how what it takes to get the arrays to work.

Given the confusion evident in your post, that is no surprise.

Please explain:

  1. What you hoped would happen.

  2. What happened instead.

  3. How they differ.

Leave nothing to the imagination.

Help…

Not quite enough information to actually help.

3

Jason V. schrieb:

…
st(1) := ‘array 1’;
st(2) := ‘array 2’;
…
cursor.bind_param(’:out_array’, StringArray?)
…
p cursor[’:out_array’] # => ‘st(1) = array 1, st(2) = array 2’

I can’t figure how what it takes to get the arrays to work.

Jason, doesn’t the output look right to you? The two strings of the
string array seem to be present. Note that you print the whole array. If
you like one element of the array, try

p cursor[’:out_array’][1]

I’m just guessing, but maybe this helps. You could also look at the
class of the array and at the methods it supports:

p cursor[’:out_array’].class
p cursor[’:out_array’].methods.sort

Regards,
Pit

4

Sorry guys,

The " # => ‘st(1) = array 1, st(2) = array 2’ " is my comment about
what I expect to get… The code doesn’t execute. The real problem is
that I don’t understand how to code the define_param correctly for
arrays.

Thanks,
Jason

5

That’s fair; I’m new to Ruby/Rails. I’m coming from a Java /
PowerBuilder world against Oracle, and I’m trying to solve a specific
issue. I’m that traditional Enterprise developer that is trying to
convince management that Ruby/Rails is the agile solution that we so
need.

Okay, background… I have the following Oracle PL/SQL.

create or replace package ruby_test is

TYPE string_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

function f_ruby(s in number,t out varchar2,st out STRING_TABLE)
return varchar2;

end ruby_test;

create or replace package body ruby_test is

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

begin
	null;
end ruby_test;

I tried…

require 'oci8'
conn = OCI8.new("jvogel","pass.","dev")
cursor = conn.parse("BEGIN :result := ruby_test.f_ruby(s => :in,t =>
:out,st => :out_array); END;")
cursor.bind_param(':in', 10)
cursor.bind_param(':result', nil, String, 100)
cursor.bind_param(':out', nil, String, 100)
cursor.bind_param(':out_array', String[], 100)
cursor.exec()

Results:
Oracle_test_OCI8.rb:21: undefined method `[]’ for String:Class
(NoMethodError)

Okay, how can I invoke this Oracle PL/SQL routine from Ruby and get the
correct results?

Thanks,
Jason

6

Jason Vogel wrote:

Sorry guys,

The " # => ‘st(1) = array 1, st(2) = array 2’ " is my comment about
what I expect to get… The code doesn’t execute. The real problem is
that I don’t understand how to code the define_param correctly for
arrays.

The problem is not the problem. The problem is your description of the
problem. You don’t understand how to get to a solution to a problem, but
we
don’t understand the problem you are trying to solve.

PLEASE say exactly what you expected, what you got, and how they differ.

If this all seems rather picky and anal, consider that computers are ten
times more anal than any person ever imagined being.

7

I’ve posted by solution…

Thanks,
Jason