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

Discussion in 'Ruby' started by Jason Vogel, Nov 18, 2006.

  1. Jason  Vogel

    Jason Vogel Guest

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

  2. Jason  Vogel

    Pit Capitain Guest

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

    Regards,
    Pit
    Pit Capitain, Nov 18, 2006
    #2
    1. Advertising

  3. Jason  Vogel

    Jason Vogel Guest

    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

    On Nov 18, 8:50 am, Pit Capitain <> wrote:
    > Jason Vogel schrieb:
    >
    > > ...
    > > st(1) := 'array 1';
    > > st(2) := 'array 2';
    > > ...
    > > cursor.bind_param(':eek:ut_array', StringArray?)
    > > ...
    > > p cursor[':eek: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[':eek: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[':eek:ut_array'].class
    > p cursor[':eek:ut_array'].methods.sort
    >
    > Regards,
    > Pit
    Jason Vogel, Nov 18, 2006
    #3
  4. Jason  Vogel

    Jason Vogel Guest

    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;
    
    -------------------------------------------------------------------------------------------------------------------------------------------------

    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()
    
    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 <> 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
    Jason Vogel, Nov 19, 2006
    #4
  5. Jason  Vogel

    Jason Vogel Guest

    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" <> 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;
    > 
    > -------------------------------------------------------------------------------------------------------------------------------------------------
    >
    > 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()
    > 
    >
    > 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 <> 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
    Jason Vogel, Nov 21, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brian Candler
    Replies:
    1
    Views:
    183
    Brian Candler
    Mar 20, 2007
  2. Peter Bailey
    Replies:
    11
    Views:
    533
    Vetrivel Vetrivel
    Nov 30, 2009
  3. Dheeraj Gambhir
    Replies:
    0
    Views:
    138
    Dheeraj Gambhir
    Jul 27, 2009
  4. Feyruz
    Replies:
    4
    Views:
    2,160
    Sherm Pendley
    Oct 14, 2005
  5. ankur srivastava

    ERR In Connecting Oracle DB using OCI8 gem

    ankur srivastava, Jun 17, 2013, in forum: Ruby
    Replies:
    1
    Views:
    243
    Simon Krahnke
    Jun 17, 2013
Loading...

Share This Page