run_sp.rb

require 'java'
require 'ojdbc-6.jar'

java_import "java.sql.Types"
java_import "oracle.jdbc.OracleTypes"

USER_ID = "12345"
WORKSPACE_ID = "222"
DOMAIN_ID = "X_1_1"
  

jdbc_url = 'jdbc:oracle:thin:@hostname:1521:sid'
connection = java.sql.DriverManager.get_connection(jdbc_url, 'blah', 'blah')
statement = connection.prepare_call("{call PACKAGE.DO_CALL(?,?,?,?,?,?,?,?)}")
statement.set_string(1, WORKSPACE_ID)
statement.set_string(2, DOMAIN_ID)
statement.set_string(3, USER_ID)
statement.set_string(4, 'MY_REPORT')
statement.set_string(5, 'Y')
statement.registerOutParameter(6, Types::INTEGER)
statement.registerOutParameter(7, Types::VARCHAR)
statement.registerOutParameter(8, OracleTypes::CURSOR)

statement.execute_query

i = statement.get_int(6)
s = statement.get_string(7)
rs = statement.get_object(8)

puts "#{i} - #{s}"



#
# Display Metadata
rs_metadata = rs.get_meta_data
column_count = rs_metadata.get_column_count
puts "column count: #{column_count}"
columns = []
(1..column_count).each do |c|
  columns << rs_metadata.column_name(c)
end
puts columns.join(", ")

#
# Retrieve actual results from returned cursor.
column_names = []
labels = []
while rs.next()
  column_names << rs.get_string("COLUMN_NAME")
  labels << rs.get_string("COLUMN_LABEL")
end
puts column_names.join(", ")
puts labels.join(", ")

rs.close
statement.close
connection.close