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