From: Jason  Vogel <jasonvogel@...>
Date: 2006-11-19T12:15:07+09:00
Subject: Re: How to pass arrays in and/or out of Oracle PL/SQL Package using OCI8

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.

-------------------------------------------------------------------------------------------------------------------------------------------------
[code]
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;
[/code]
-------------------------------------------------------------------------------------------------------------------------------------------------

I tried...

[code]
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()
[/code]

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

On Nov 18, 6:08 pm, Paul Lutus <nos...@nosite.zzz> wrote:
> 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.
> 
> --
> Paul Lutushttp://www.arachnoid.com