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.