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.