Is it possible to pass a Hash to a stored procedure?

[willfults]

Hi, I am trying to call an Oracle stored procedure using ruby-oci8. I am able to call the procedure using the following code, but I am having an issue with the second parameter (parameterArray). It requires an array of items to be passed to it, in java we use the oracle.sql.ARRAY class but how would I handle this with ruby-oci8? It looks like there is no OCI8::BindType for a hash, perhaps currently this is not possible? The current error I get is "unsupported dataType: Hash"

My current code is as follows... starting_time = "01/05/2015" + " 00:00:00" ending_time = "01/05/2015" +" 00:00:00" data = [{owner_id: "3945073", time_min: nil, time_max: nil, duration_min: nil, duration_max: nil, date_format: "'MM/DD/YYYY HH24:MI:SS'", start_date: starting_time, end_date: ending_time}]

    conn = OCI8.new('user', 'password', '//example.com:1521/main.example.com')
    cursor = conn.parse("call reporting.execute_report(:report_type, :parameter_array, :out)")
    cursor.bind_param(:report_type, 'TRAFFIC_ANALYSIS_CALL_SUMMARY') # bind by name
    cursor.max_array_size=8 # unsure what this should be, but it errors out if I don't set this
    cursor.bind_param_array(:parameter_array, data) # the error is here
    cursor.exec()

[kubo]

If the second parameter of reporting.execute_report isn't a package type, you can use an array of hashes.

Examples:

create or replace type point as object (
  x integer,
  y integer
)
/
create or replace type point_array as array(50) of point
/
create or replace function add_points(points in point_array) return point is
  result point := point(0, 0);
begin
  for i in points.first .. points.last
  loop
    result.x := result.x + points(i).x;
    result.y := result.y + points(i).y;
  end loop;
  return result;
end;
/

require 'oci8'

conn = OCI8.new('username/password')

class Point < OCI8::Object::Base
end

class PointArray < OCI8::Object::Base
  # The Oracle type name is guessed from the ruby class name.
  #   ruby class name    Oracle type name
  #     FooBarBaz    =>   FOO_BAR_BAZ
  #
  # If it fails, use set_typename as follows:
  set_typename('point_array')
end

cursor = conn.parse("begin :result := add_points(:points); end;")

# The data must be an array of Point.
# But Hash values can be passed as bind values instead of Point.
data = [{:x => 1, :y => 2}, {:x => 3, :y => 4}]
cursor.bind_param(:result, nil, Point)
cursor.bind_param(:points, data, PointArray)
cursor.exec
result = cursor[:result]  # result is a Point object.
if result
  puts("x = #{result.x}")  # => x = 4
  puts("y = #{result.y}")  # => y = 6
else
  puts("result is nil")
end

You cannot use types in packages such as point_pkg.array_type_in_package created by the following SQL statements. This restriction may be removed in future if both client version and server version are Oracle 12c though the next ruby-oci8 will not support it.

create or replace type point as object (
  x integer,
  y integer
)
/
create or replace package point_pkg is
  type array_type_in_package is array(50) of point;
  function add_points(points in array_type_in_package) return point;
end;
/
create or replace package body point_pkg is
  function add_points(points in array_type_in_package) return point is
    result point := point(0, 0);
  begin
    for i in points.first .. points.last
    loop
      result.x := result.x + points(i).x;
      result.y := result.y + points(i).y;
    end loop;
    return result;
  end;
end;
/

[willfults]

Thanks with a little modification this seems to work since I am not getting any Oracle errors, the only issue I am getting at the moment is my OCI8::Cursor is returning back as nil. Am I doing something incorrect with the cursor? My code is as follows...

begin
    starting_time = "01/05/2013" + " 00:00:00"
    ending_time = "01/05/2015" +" 23:59:59" 
    conn = DBConnector.connect # normal connection stuff in this class
    cursor = conn.parse("begin reporting.execute_report(:name, :plist, :pcursor); end;")
     # modified the data a bit to fit our table
    data = [{:name => "owner_id", :value => "3945073"}, {:name => "time_min", :value => nil},{:name => "time_max", :value => nil},{:name => "duration_min", :value => nil},{:name => "duration_max", :value => nil},{:name => "date_format", :value => "'MM/DD/YYYY HH24:MI:SS'"},{:name => "start_date", :value => starting_time},{:name => "end_date", :value => ending_time},{:name => "limit_customers", :value => "0"},{:name => "limit_dow", :value => "0"},{:name => "limit_result", :value => "0"},{:name => "limit_extensions", :value => "0"},{:name => "ad_source_level", :value => "0"}]
    cursor.bind_param(:name, "TRAFFIC_ANALYSIS_CALL_SUMMARY", String)
    cursor.bind_param(:plist, data, ParameterType)
    cursor.bind_param(:pcursor, nil, OCI8::Cursor )

    cursor.exec()
    results_cursor = cursor[':pcursor']
    #cursor.close
    puts 'results is '
    # this is returing as null (nil in ruby)
    puts results_cursor
   # throws an exception here because results_cursor is nil
    while row = results_cursor.fetch()
      puts row
    end

    results_cursor.close

  rescue => ex
    puts 'Oracle Error msg : ' + ex.to_s[0,254]
  ensure
    cursor.close()
    conn.logoff unless conn.nil?
  end

end

[kubo]

The ruby code seems correct. Could you post minimum code (both PL/SQL and ruby) to reprodce the issue?

The follwing code works for me.

create or replace package test_cursor is
  type cursor_type is ref cursor;
  procedure open_cursor(csr out cursor_type);
end;
/
create or replace package body test_cursor as
  procedure open_cursor(csr out cursor_type) is
  begin
    open csr for select * from dict;
  end;
end;
/

require 'oci8'
conn = OCI8.new('username/password')
cursor = conn.parse("begin test_cursor.open_cursor(:pcursor); end;")
cursor.bind_param(:pcursor, nil, OCI8::Cursor)
cursor.exec
result_cursor = cursor[:pcursor]
if result_cursor
  while row = result_cursor.fetch
    puts row.join(',')
  end
else
  puts(":pcursor is nil")
end

[willfults]

I got this to work, the issue was my code was using something like... cursor.bind_param(:out, OCI8::Cursor)

but when I add a quote around the bind name it works... cursor.bind_param(':out', OCI8::Cursor)

Thanks again for your help!