Friday, May 9, 2008

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

Saturday, May 3, 2008

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.