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

173 comments: