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

[Jason Vogel]

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 =>
ut,st => ut_array); END;")
cursor.bind_param(':in', 10)
cursor.bind_param(':result', nil, String, 100)
cursor.bind_param('ut', nil, String, 100)
cursor.bind_param('ut_array', StringArray?)
cursor.exec()
p cursor[':result'] # => 'Ruby rocks 10 times!'
p cursor['ut'] # => 'outta here'
p cursor['ut_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

 

[Pit Capitain]

...
st(1) := 'array 1';
st(2) := 'array 2';
...
cursor.bind_param('ut_array', StringArray?)
...
p cursor['ut_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['ut_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['ut_array'].class
p cursor['ut_array'].methods.sort

Regards,
Pit

 

[Jason Vogel]

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

...
st(1) := 'array 1';
st(2) := 'array 2';
...
cursor.bind_param('ut_array', StringArray?)
...
p cursor['ut_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['ut_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['ut_array'].class
p cursor['ut_array'].methods.sort

Regards,
Pit

 

[Jason Vogel]

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

 

[Jason Vogel]

I've posted by solution....

http://jasonvogel.blogspot.com/2006/11/invoking-plsql-package-routine-with.html

Thanks,
Jason

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

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.