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

I've posted by solution....

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

Thanks,
Jason

On Nov 18, 9:13 pm, "Jason  Vogel" <jasonvo...@gmail.com> wrote:
> 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