Friday, April 25, 2008

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.