Ruby-OCI8 1.0.2 gem is available

As Kubo announced in Ruby mailing list, with the release of version 1.0.2, the gem of Ruby-OCI8 is available.  Now you can easily install ruby-oci8 by simple commd "gem install ruby-oci8".

Though the installation of Ruby-OCI8 has been eased a lot with gem, you still have to pay attention to:

1) The same as installation from source code,  Oracle client is still required to be installed before the gem installation. Check help page "Oracle Full Client" and "Oracle Instant Client" on ruby-oci8 website for details, please be specially careful to the setting of environment variable "LD_LIBRARY_PATH".

2) If you don't tell the ruby to load RubyGems every time it starts up by setting the environment variable "RUBYOPT" to "rubygems", you should add "require 'rubygems'" before "require 'oci8'", or you will get error "no such file to load -- oci8".

Now the Ruby-OCI8 gem is only available for version 1.0.2,  no support for 2.0 yet.

Ruby-OCI8 2.0 New Feature: Complete Schema Object Metadata Describe Support

1, Introduction

This feature enables you to perform an explicit describe of the following schema objects and their subschema objects via Ruby-OCI8:

• tables and views
• synonyms
• procedures
• functions
• packages
• sequences
• collections
• types
• schemas
• databases

Information about other schema elements (procedure/function arguments, columns, type attributes, and type methods) is available through a describe of one of the above schema objects or an explicit describe of the subschema object.

2, Ruby-OCI8 2.0 Installation

svn co http://ruby-oci8.rubyforge.org/svn/trunk/ruby-oci8

And then follow the Ruby-OCI8 install instruction.

3,Metadata Describe API

[General Purpose]
This method can be used to describe any schema object.
OCI8#describe_any(object_name)

[table or view]
OCI8#describe_table(table_name, table_only = false)
Return:
OCI8::Metadata::Table

[view]
OCI8#describe_view(view_name)
Return:
OCI8::Metadata::View

[procedure]
OCI8#describe_procedure(procedure_name)
Return:
OCI8::Metadata::Procedure

[function]
OCI8#describe_function(function_name)
Return:
OCI8::Metadata::Function

[package]
OCI8#describe_package(package_name)
Return:
OCI8::Metadata::Package

[type]
OCI8#describe_type(type_name)
Return:
OCI8::Metadata::Type

[synonym]
OCI8#describe_synonym(synonym_name, check_public_also = false)
Return:
OCI8::Metadata::Synonym

[sequence]
OCI8#describe_sequence(sequence_name)
Return:
OCI8::Metadata::Sequence

[schema]
OCI8#describe_schema(schema_name)
Return:
OCI8::Metadata::Schema

[database]
OCI8#describe_database(database_name)
Return:
OCI8::Metadata::Database

4, Examples

4.1 Describe Table

Table definition:

CREATE TABLE employees_demo(
  employee_id    NUMBER(6),
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25),
  email          VARCHAR2(25)
)

Describe via Ruby-OCI8

require 'oci8'
conn = OCI8.new('ruby','oci8','localhost')
emp_tab = conn.describe_table('employees_demo')
puts "Table Name: #{emp_tab.obj_name}"
emp_tab.columns.each do |col|
  puts "  Column #{col.name}, type: #{col.data_type}, size: #{col.data_size}"
end
conn.logoff

Output:

Table Name: EMPLOYEES_DEMO
  Column EMPLOYEE_ID, type: number, size: 22
  Column FIRST_NAME, type: varchar2, size: 20
  Column LAST_NAME, type: varchar2, size: 25
  Column EMAIL, type: varchar2, size: 25

4.2 Describe Procedure

Procedure definition:

CREATE OR REPLACE PROCEDURE get_name(emp_id IN NUMBER, emp_name OUT VARCHAR2) AS
  emp_fn VARCHAR2(20);
  emp_ln VARCHAR2(25);
BEGIN
SELECT first_name, last_name INTO emp_fn, emp_ln
  FROM employees_demo WHERE employee_id = emp_id;
emp_name := emp_fn || ' ' ||  emp_ln;
END;

Describe via Ruby-OCI8

require 'oci8'
conn = OCI8.new('ruby','oci8','localhost')
get_name_proc = conn.describe_procedure('get_name')
puts "Procedure Name: #{get_name_proc.obj_name}"
args = get_name_proc.arguments
args.each { |arg|
  puts "  Argument #{arg.name}, type: #{arg.data_type}, in/out: #{arg.iomode.to_s}"
}
conn.logoff

Output:

Procedure Name: GET_NAME
  Argument EMP_ID, type: number, in/out: in
  Argument EMP_NAME, type: varchar2, in/out: out

4.3 Describe Function

Function definition:

CREATE OR REPLACE FUNCTION get_email(emp_id NUMBER)
RETURN VARCHAR2 AS
  emp_email VARCHAR2(25);
BEGIN
  SELECT email INTO emp_email FROM employees_demo
    WHERE employee_id = emp_id;
  RETURN(emp_email);
END;

Describe via Ruby-OCI8

get_email_func = conn.describe_function('get_email')
puts "Function Name: #{get_email_func.obj_name}"
args = get_email_func.arguments
args.each_index { |i|
if i == 0
  puts "  Return Type: #{args[i].data_type}"
else
  puts "  Argument #{i}: #{args[i].name}, Type: #{args[i].data_type}"
end                     
}

Output:

Function Name: GET_EMAIL
  Return Type: varchar2
  Argument 1: EMP_ID, Type: number

4.4 Describe Package

Package Definition

CREATE OR REPLACE PACKAGE emp_pkg AS
  FUNCTION get_email(emp_id NUMBER) RETURN VARCHAR2;
  PROCEDURE get_name(emp_id IN NUMBER, emp_name OUT VARCHAR2);
END emp_pkg;

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  FUNCTION get_email(emp_id NUMBER)
  RETURN VARCHAR2 AS
    emp_email VARCHAR2(25);
  BEGIN
    SELECT email INTO emp_email FROM employees_demo
      WHERE employee_id = emp_id;
    RETURN(emp_email);
  END;

  PROCEDURE get_name(emp_id IN NUMBER, emp_name OUT VARCHAR2) AS
    emp_fn VARCHAR2(20);
    emp_ln VARCHAR2(25);
  BEGIN
    SELECT first_name, last_name INTO emp_fn, emp_ln
      FROM employees_demo WHERE employee_id = emp_id;
    emp_name := emp_fn || ' ' ||  emp_ln;
  END;
END emp_pkg;

Describe via Ruby-OCI8

require 'oci8'
conn = OCI8.new('ruby','oci8','localhost')
emp_pkg = conn.describe_package('emp_pkg')
puts "Package Name: #{emp_pkg.obj_name}"
sub_progs = emp_pkg.subprograms
sub_progs.each { |subp|
  if subp.is_a? OCI8::Metadata::Function
    puts "Subprogram:  Function #{subp.name}"
    args = subp.arguments
    args.each_index { |i|
      if i == 0
        puts "  Return Type: #{args[i].data_type}"
      else
        puts "  Argument #{i}: #{args[i].name}, Type: #{args[i].data_type}"                       
      end
    }
  elsif subp.is_a? OCI8::Metadata::Procedure
    puts "Subprogram:  Procedure #{subp.name}"
    args = subp.arguments
    args.each { |arg|
      puts "Argument #{arg.name}, type: #{arg.data_type}, in/out: #{arg.iomode.to_s}"
    }
  end
}
conn.logoff

Result:

Package Name: EMP_PKG
Subprogram:  Function GET_EMAIL
  Return Type: varchar2
  Argument 1: EMP_ID, Type: number
Subprogram:  Procedure GET_NAME
  Argument EMP_ID, type: number, in/out: in
  Argument EMP_NAME, type: varchar2, in/out: out

4.5 Describe Type

Type definition:

CREATE OR REPLACE TYPE person_t AS object
  (name varchar2(30),
  age number(3));

Describe via Ruby-OCI8

require 'oci8'
conn = OCI8.new('ruby','oci8','localhost')
person_t = conn.describe_type('person_t')
puts "Type Name: #{person_t.obj_name}"
person_t.type_attrs.each { |attr|
  puts "  Attribute #{attr.name}, type: #{attr.data_type}, size: #{attr.data_siz  e}"}
conn.logoff

Result:

Type Name: PERSON_T
  Attribute NAME, type: varchar2, size: 30
  Attribute AGE, type: number, size: 22

5, Reference

Describing Schema Metadata on Oracle Call Interface Programmer's Guide

Ruby-OCI8 2.0 New Feature: Array DML Support

1, Introduction To Array DML

Oracle Call Interface provides an Array Interface. This array interface significantly reduces round trips to the database when you are updating or inserting a large volume of data. This reduction can lead to considerable performance gains in a busy client/server environment. For example, consider an application that needs to insert 100 rows into the database. Calling OCIStmtExecute() 100 times with single values results in 100 network round trips to insert all the data. The same result is possible with a single call to OCIStmtExecute() using an input array, which involves only one network round trip.


2, Ruby-OCI8 v2.0 Installation

svn co http://ruby-oci8.rubyforge.org/svn/trunk/ruby-oci8

And then follow the Ruby-OCI8 install instruction.

3, Usage

Three new instance methods are added to class OCI8::Cursor for supporting array dml:

1) max_array_size = val

Set the maximum array size for bind_param_array. This method should be called before bind_param_array.

example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(:name, ['mike', 'john', 'danny'])
cursor.bind_param_array (:age, [20, 21, 22])
cursor.exec_array

When calling this method, all the binds will be clean from cursor if instance variable max_array_size of cursor is set before.

example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(:name, ['mike', 'john', 'danny'])
cursor.bind_param_array(:age, [20, 21, 22])
cursor.max_array_size = 4
cursor.exec_array

Above example will generate an exception because you don't re-bind the params after calling max_array_size.

2) bind_param_array(key, var_array, type = nil, max_item_length = nil)

Bind array explicitly. This method is used to bind an array of values to a placeholder embedded in the prepared statement which is to be executed with "exec_array".

When key is number, it binds by position, which starts from 1. When key is string, it binds by the name of placeholder.

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name)")
cursor.max_array_size = 3
cursor.bind_param_array(:name, ['mike', 'john', 'danny']) #bind by name
... or ...
cursor.bind_param_array(1, ['mike', 'john', 'danny']) #bind by position

The size of arrays bound through bind_param_array should not be greater than the size set by method max_array_size = val.

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name)")
cursor.max_array_size = 2
cursor.bind_param_array(:name, ['mike', 'john', 'danny'])
cursor.exec_array

Above example will generate an exception because the size of array passed to bind_param_array is 3, which is greater than the size 2 set by max_array_size.

If you are binding more than one parameters, all the arrays are required the same size.

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(:name, ['mike', 'john', 'danny'])
cursor.bind_param_array(:age, [20, 21])
cursor.exec_array

Above example will generate an exception becase the name parameter array and age parameter array are not the same size.

The array dml also supports setting values through method "[key] = val".

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(1, nil, String)
cursor.bind_param_array(2, nil, Fixnum)
cursor[1] = ['mike', 'john', 'danny']
cursor[2] = [20, 21, 22]
cursor.exec_array

3) exec_array

Executes the SQL statement assigned the cursor with array binding. This implementation currently only supports non-data returning statements (INSERT, UPDATE, DELETE but not SELECT). All binding arrays should be the same size and this size will be used as iteration count for OCIStmtExecute().

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 10
cursor.bind_param_array(1, nil, String)
cursor.bind_param_array(2, nil, Fixnum)
cursor[1] = ['mike', 'john', 'danny']
cursor[2] = [20, 21, 22]
cursor.exec_array

In above example, three rows are inserted to the table though max_array_size is set 10.

Ruby-OCI8 2.0 New Feature: Oracle Object Type support

There are two main versions in Ruby-OCI8. Stable version 1.x has been widely used in Ruby and RoR apps, and the unstable version is 2.x, in which many new features are under experiment. Here I will give a short tutorial on how to manipulates Object Type via Ruby-OCI8 v2.0.

1, Introduction to Oracle Object Type

Object types are abstractions of the real-world entities, such as purchase orders, that application programs deal with. An object type is a schema object with three kinds of components:

• A name, which identifies the object type uniquely within that schema.
• Attributes, which are built-in types or other user-defined types. Attributes model the structure of the real-world entity.
• Methods, which are functions or procedures written in PL/SQL and stored in the database, or written in a language like C or Java and stored externally. Methods implement operations the application can perform on the real-world entity.
  

Learn more at Oracle® Database SQL Language Reference

2, Ruby-OCI8 v2.0 Installation

svn co http://ruby-oci8.rubyforge.org/svn/trunk/ruby-oci8

And then follow the Ruby-OCI8 install instruction.

3, Tutorial

Suppose we use account ruby/oci8 on an Oracle db installed on localhost

Step 1, Create object type and table in database

CREATE TYPE person_t AS object
   (name varchar2(30),
   age number(3));

CREATE TABLE person_tab
   (person_no number,
   person person_t);

Step 2, Insert sample data

INSERT INTO person_tab VALUES (1, person_t('Lan', 20));
INSERT INTO person_tab VALUES (2, person_t('Chang', 21));

Step 3, Retrieve object type data via Ruby-OCI8

require 'oci8'
conn = OCI8.new('ruby','oci8','localhost')
cursor = conn.exec('select * from person_tab')
while r = cursor.fetch
   puts "Person no: #{r[0]}"
   puts "  name: #{r[1].name}"
   puts "  age: #{r[1].age}"
end
cursor.close
conn.logoff

Result:

Person No.: 1
   name: Lan
   age: 20
Person NO.: 2
   name: Change
   age: 21

As we see, once we have retrieved an Object Type object in Ruby, we can use .{AttributeName} to get its specific attribute value stored in database.