I just released Oracle enhanced adapter version 1.4.0 and here is the summary of main changes.

Rails 3.1 support

Oracle enhanced adapter GitHub version was working with Rails 3.1 betas and release candidate versions already but it was not explicitly stated anywhere that you should use git version with Rails 3.1. Therefore I am releasing new version 1.4.0 which is passing all tests with latest Rails 3.1 release candidate. As I wrote before main changes in ActiveRecord 3.1 are that it using prepared statement cache and using bind variables in many statements (currently in select by primary key, insert and delete statements) which result in better performance and better database resources usage.

To follow up how Oracle enhanced adapter is working with different Rails versions and different Ruby implementations I have set up Continuous Integration server to run tests on different version combinations. At the moment of writing everything was green :)

Other bug fixes and improvements

Main fixes and improvements in this version are the following:

• On JRuby I switched from using old ojdbc14.jar JDBC driver to latest ojdbc6.jar (on Java 6) or ojdbc5.jar (on Java 5). And JDBC driver can be located in Rails application ./lib directory as well.

• RAW data type is now supported (which is quite often used in legacy databases instead of nowadays recommended CLOB and BLOB types).

• rake db:create and rake db:drop can be used to create development or test database schemas.

• Support for virtual columns in improved (only working on Oracle 11g database).

• Default table, index, CLOB and BLOB tablespaces can be specified (if your DBA is insisting on putting everything in separate tablespaces :)).

• Several improvements for context index additional options and definition dump.

See list of all enhancements and bug fixes

If you want to have a new feature in Oracle enhanced adapter then the best way is to implement it by yourself and write some tests for that feature and send me pull request. In this release I have included commits from five new contributors and two existing contributors - so it is not so hard to start contributing to open source!

Readme Driven Development

One of the worst parts of Oracle enhanced adapter so far was that for new users it was quite hard to understand how to start to use it and what are all additional features that Oracle enhanced adapter provides. There were many blog posts in this blog, there were wiki pages, there were posts in discussion forums. But all this information was in different places and some posts were already outdated and therefore for new users it was hard to understand how to start.

After reading about Readme Driven Development and watching presentation about Readme Driven Development I knew that README of Oracle enhanced adapter was quite bad and should be improved (in my other projects I am already trying to be better but this was my first one :)).

Therefore I have written new README of Oracle enhanced adapter which includes main installation, configuration, usage and troubleshooting tasks which previously was scattered across different other posts. If you find that some important information is missing or outdated then please submit patches to README as well so that it stays up to date and with relevant information.

If you have any questions please use discussion group or report issues at GitHub or post comments here.

I just released Oracle enhanced adapter version 1.3.2 with latest bug fixes and enhancements.

Bug fixes and improvements

Main fixes and improvements are the following:

• Previous version 1.3.1 was checking if environment variable TNS_NAME is set and only then used provided database connection parameter (in database.yml) as TNS connection alias and otherwise defaulted to connection to localhost with provided database name. This was causing issues in many setups.
  Therefore now it is simplified that if you provide only database parameter in database.yml then it by default will be used as TNS connection alias or TNS connection string.
• Numeric username and/or password in database.yml will be automatically converted to string (previously you needed to quote them using "...").
• Database connection pool and JNDI connections are now better supported for JRuby on Tomcat and JBoss application servers.
• NLS connection parameters are supported via environment variables or in database.yml. For example, if you need to have NLS_DATE_FORMAT in your database session to be DD-MON-YYYY then either you specify nls_date_format: DD-MON-YYYY in database.yml for particular database connection or set ENV['NLS_DATE_FORMAT'] = 'DD-MON-YYYY' in e.g. config/initializers/oracle.rb. You can see the list of all NLS parameters in source code.
  It might be necessary to specify these NLS session parameters only if you work with some existing legacy database which has, for example, some stored procedures that require particular NLS settings. If this is new database just for Rails purposes then there is no need to change any settings.
• If you have defined foreign key constraints then they are now correctly dumped in db/schema.rb after all table definitions. Previously they were dumped after corresponding table which sometimes caused that schema could not be recreated from schema dump because it tried to load constraint which referenced table which has not yet been defined.
• If you are using NCHAR and NVARCHAR2 data types then now NCHAR and NVARCHAR2 type values are correctly quoted with N'...' in SQL statements.

Upcoming changes in Rails 3.1

Meanwhile Oracle enhanced adapter is updated to pass all ActiveRecord unit tests in Rails development master branch and also updated according to Arel changes. Arel library is responsible for all SQL statement generation in Rails 3.0.

Rails 3.0.3 is using Arel version 2.0 which was full rewrite of Arel 1.0 (that was used initial Rails 3.0 version) and as a result of this rewrite it is much faster and now Rails 3.0.3 ActiveRecord is already little bit faster than in ActiveRecord in Rails 2.3.

There are several improvements in Rails master branch which are planned for Rails 3.1 version which are already supported by Oracle enhanced adapter. One improvement is that ActiveRecord will support prepared statement caching (initially for standard simple queries like find by primary key) which will reduce SQL statement parsing time and memory usage (and probably Oracle DBAs will complain less about Rails dynamic SQL generation :)). The other improvement is that ActiveRecord will correctly load included associations with more than 1000 records (which currently fails with ORA-01795 error).

But I will write more about these improvements sometime later when Rails 3.1 will be released :)

Install

As always you can install Oracle enhanced adapter on any Ruby platform (Ruby 1.8.7 or Ruby 1.9.2 or JRuby 1.5) with

gem install activerecord-oracle_enhanced-adapter

If you have any questions please use discussion group or report issues at GitHub or post comments here. And the best way how to contribute is to fix some issue or create some enhancement and send me pull request at GitHub.

Initial version of ruby-plsql-spec gem was using RSpec version 1.3. But recently RSpec 2.0 was released which API is not compatible with previous RSpec 1.x API and as a result plsql-spec utility was failing if just RSpec was upgraded to version 2.0.

Therefore I updated also ruby-plsql-spec to use latest RSpec 2.0 gem and released ruby-plsql-spec gem version 0.2.1. You can install the latest version with

gem install ruby-plsql-spec

Upgrade from previous version

If you previously already installed initial ruby-plsql-spec version 0.1.0 then you need to update your spec/spec_helper.rb file to use RSpec 2.0. You can do it by running one more time

plsql-spec init

which will check which current files are different from the latest templates. You need to update just spec_helper.rb file. When you will be prompted to overwrite spec_helper.rb file then at first you can enter d to see differences between current file and new template. If you have not changed original spec_helper.rb file then you will see just one difference

- Spec::Runner.configure do |config|
+ RSpec.configure do |config|

You can then answer y and this file will be updated. When you will be prompted to overwrite other files then you can review the changes in the same way and decide if you want them to be overwritten or not (e.g. do not overwrite database.yml file as it has your specific database connection settings).

HTML output option

In addition plsql-spec utility now has --html option which will generate test results report as HTML report. It might be useful for usage in text editors where you can define which command line utility to run when pressing some shortcut key and then display generated HTML output report. If you will execute

plsql-spec run --html

then it will generate HTML report in test-results.html file. You can override this file name as well using --html output_file_name.html option.

Questions or suggestions

If you have any other feature suggestions or questions about ruby-plsql-spec then please post comments here or report any bugs at GitHub issues page.

During recent Oracle OpenWorld conference I presented session PL/SQL unit testing can be fun! where I demonstrated how to do PL/SQL unit testing with Ruby:

Audience was quite interested and had a lot of questions and therefore it motivated me to do some more improvements to ruby-plsql-spec to make it easier for newcomers.

ruby-plsql-spec gem and plsql-spec command line utility

Initially ruby-plsql-spec was just repository of sample tests and if you wanted to start to use it in your project you had to manually pick necessary files and copy them to your project directory.

Now ruby-plsql-spec is released as a gem which includes all necessary dependencies (except ruby-oci8 which you should install if using MRI Ruby implementation) and you can install it with

gem install ruby-plsql-spec

See more information about installation in README file or see specific installation instructions on Windows.

When you have installed ruby-plsql-spec gem and want to start to use it in your existing project then go to your project directory and from command line execute

plsql-spec init

It will create spec subdirectory in current directory where all initial supporting files will be created. The main configuration file which should be updated is spec/database.yml where you should specify username, password and database connection string that should be used when running tests:

default:
  username: hr
  password: hr
  database: orcl

If you specify just database: name then it will be used as TNS connection string (and TNS_ADMIN environment variable should point to directory where tnsnames.ora file is located) or you can also provide hostname: and if necessary also port: parameters and then you can connect to database without tnsnames.ora file.

Now you can start to create your tests in spec directory and your tests file names should end with _spec.rb. You can see some examples at ruby-plsql-spec examples directory

To validate your installation you can try to create simple dummy test in spec/dummy_spec.rb:

require "spec_helper"

describe "test installation" do
  it "should get SYSDATE" do
    plsql.sysdate.should_not == NULL
  end
end

And now from command line you can try to run your test with:

plsql-spec run

If everything is fine you should see something similar like this:

Running all specs from spec/
.

Finished in 0.033782 seconds

1 example, 0 failures

Code coverage reporting

During my Oracle OpenWorld presentation I also showed how to get PL/SQL code coverage report (which shows which PL/SQL code lines were executed during tests run). It might be useful when you want to identify which existing PL/SQL code is not yet covered by unit tests.

Now code coverage reporting is even easier with new ruby-plsql-spec gem. It uses Oracle database DBMS_PROFILER package to collect code coverage information and I took rcov reports HTML and CSS files to present results (so that they would be very similar to Ruby code coverage reports).

To try code coverage reporting let’s create simple PL/SQL function:

CREATE OR REPLACE FUNCTION test_profiler RETURN VARCHAR2 IS
BEGIN
  RETURN 'test_profiler';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'others';
END;

and simple test to verify code coverage reporting:

require "spec_helper"

describe "test code coverage" do
  it "should get result" do
    plsql.test_profiler.should == 'test_profiler'
  end
end

And now you can run tests with --coverage option which will produce code coverage report:

plsql-spec run --coverage

As a result code coverage reports are created in coverage/ subdirectory. Open coverage/index.html in your browser and click on TEST_PROFILER function and you should see something similar like this report:

You can see that RETURN 'test_profiler'; line (with green background) was executed by test but RETURN 'others'; line (with red background) was not. Lines with light background are ignored by DBMS_PROFILER and I do not take them into account when calculating code coverage percentage (but they are taken into account when calculating total coverage percentage).

Questions or feedback

If you have any other questions about using ruby-plsql-spec for PL/SQL unit testing then please post comments here or if you find any issues when using ruby-plsql-spec then please report them at GitHub issues page.

Rails 3.0 was released recently and therefore I am releasing new Oracle enhanced adapter version 1.3.1 which is tested and updated against latest Rails 3.0.0 version. You can read about main changes in oracle_enhanced adapter for Rails 3 support in my previous blog post. Latest version 1.3.1 mainly contains several bug fixes (which you can find in change log as well as in detailed commit list) as well as several new features that I will describe here.

Usage with Rails 3

I have improved a little bit configuration and loading of oracle_enhanced adapter in Rails 3 and here are the initial steps that you should do to use oracle_enhanced adapter in Rails 3 application. I assume that you are using latest Rails 3.0.0 version as well as latest Bundler 1.0.0 version.

At first you need to include necessary gems in Gemfile of your application:

gem 'ruby-oci8', '~> 2.0.4'
gem 'activerecord-oracle_enhanced-adapter', '~> 1.3.1'

It is recommended to use ~> version (requires specified version or later minor version update where only the last digit of version has changed) or = version in your Gemfile and not >= (which might include major version changes). In this way you ensure that your application will not break when major API changes will happen in gem that you are using.

If you want to run your application both on MRI and JRuby then you can specify

platforms :ruby do
  gem 'ruby-oci8', '~> 2.0.4'
end

which will load ruby-oci8 gem only when using MRI 1.8 or 1.9 and not when using JRuby.

If you would like to use the latest development version of oracle_enhanced then change Gemfile to:

gem 'activerecord-oracle_enhanced-adapter', '~> 1.3.1', :git => 'git://github.com/rsim/oracle-enhanced.git'

If you will use also ruby-plsql gem in your application then include as well (and specify version as needed)

gem "ruby-plsql", "~> 0.4.3"

After these changes in Gemfile run bundle update to install necessary gems and generate corresponding Gemfile.lock.

If you want to use all default oracle_enhanced settings then you need just to specify your database connection in database.yml, for example, something like this:

development:
  adapter: oracle_enhanced
  database: orcl
  username: user
  password: secret

and you can start to use Rails with Oracle database. If you would like to change some oracle_enhanced adapter settings then it is recommended to create initializer file config/initializers/oracle.rb where you can specify necessary defaults, for example:

ActiveSupport.on_load(:active_record) do
  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
    self.emulate_integers_by_column_name = true
    self.emulate_dates_by_column_name = true
    self.emulate_booleans_from_strings = true

    # to ensure that sequences will start from 1 and without gaps
    self.default_sequence_start_value = "1 NOCACHE INCREMENT BY 1"

    # other settings ...
  end
end

It is important to use ActiveSupport.on_load(:active_record) as Rails 3 does lazy loading of all components and we need to ensure that oracle_enhanced adapter defaults are set only after ActiveRecord is loaded.

You can take a look at sample Rails 3 application on Oracle to see sample configuration files that I mentioned here.

Database connection options

There are several ways how to specify database connection in database.yml file.

Using tnsnames.ora file with TNS aliases

If you are using tnsnames.ora file with TNS names and connection descriptions then you need to set TNS_ADMIN environment variable to point to directory where tnsnames.ora file is located. If oracle_enhanced adapter will detect that ENV[‘TNS_ADMIN’] is not empty then it will try to use TNS name in :database parameter to connect to database. So in this case in database.yml you need to specify:

development:
  adapter: oracle_enhanced
  database: connection_name_from_tnsnames
  username: user
  password: secret

Connection using tnsnames is supported both for MRI with ruby-oci8 as well as for JRuby with JDBC. Use this option if you would not like to hardcode database server address, port and database name in your application and want to specify separately in tnsnames.ora file.

Using host, port and database option

If you do not want to create separate tnsnames.ora file and want to specify database server, port and database name directly in application, then you can specify these options separately in database.yml file, for example:

development:
  adapter: oracle_enhanced
  host: localhost
  port: 1521
  database: orcl
  username: user
  password: secret

port default value is 1521 and can be omitted. It is also possible to specify host, port and database name is Oracle specific format in database option:

development:
  adapter: oracle_enhanced
  database: //localhost:1521/orcl
  username: user
  password: secret

It is also possible to specify TNS connection description directly in database.yml file (if you do not want to create separate tnsnames.ora file), for example:

development:
  adapter: oracle_enhanced
  database: "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"
  username: user
  password: secret

Using JNDI connections in JRuby

If you deploy your JRuby application in Java application server that supports JNDI connections then it is possible to specify also JNDI connection in database.yml file, for example:

production: 
  adapter: oracle_enhanced
  jndi: "jdbc/jndi_connection_name"

I am not using this connection option but some oracle_enhanced users are using it.

Contributing to oracle_enhanced adapter

If you experience any issues with oracle_enhanced adapter then please report issues at GitHub issue tracker or discuss them at oracle_enhanced discussion group.

But even better if you want some new feature in oracle_enhanced adapter then fork oracle_enhanced git repository and make your changes and send me pull requests for review.

For all changes please add also RSpec tests as well as verify if all existing tests are passing after your changes. I added description how to set up environment for running tests – please let me know if something is missing there.

Big thanks to all contributors who have submitted patches so far :)

Rails 3 is in final finishing stage (currently in beta4) and therefore I released new Oracle enhanced adapter version 1.3.0 which I was working on during last months.

Rails 3 compatibility

The major enhancement is that Oracle enhanced adapter is now compatible with Rails 3. To achieve that I also developed Oracle SQL compiler for Arel gem which is used now by ActiveRecord to generate SQL statements. When using Oracle enhanced adapter with Rails 3 you will notice several major changes:

• Table and column names are always quoted and in uppercase to avoid the need for checking Oracle reserved words.
  E.g. now Post.all will generate query
  

  SELECT "POSTS".* FROM "POSTS"
  

• Better support for limit and offset options (when possible just ROWNUM condition in WHERE clause is used without using subqueries).
  E.g. Post.first (or Post.limit(1)) will generate query
  

  SELECT "POSTS".* FROM "POSTS" WHERE ROWNUM <= 1
  

  but Post.limit(1).offset(1) will generate
  

  select * from (select raw_sql_.*, rownum raw_rnum_
    from (SELECT "EMPLOYEES".* FROM "EMPLOYEES") raw_sql_ where rownum <= 2)
    where raw_rnum_ > 1
  

When using Oracle enhanced adapter with current version of Rails 3 and Arel it is necessary to turn on table and column caching option in all environments as otherwise Arel gem will cause very many SQL queries on data dictionary tables on each request. To achieve that you need to include in some initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.cache_columns = true

I have published simple Rails 3 demo application using Rails 3 and Oracle enhanced adapter. You can take a look at Gemfile and Oracle initializer file to see examples how to configure Oracle enhanced adapter with Rails 3.

Rails 2.3 compatibility

Oracle enhanced adapter version 1.3.0 is still compatible with Rails 2.3 (I am testing it against Rails 2.3.5 and 2.3.8) and it is recommended to upgrade if you are on Rails 2.3 and plan to upgrade to Rails 3.0 later. But if you are still on Rails 2.2 or earlier then there might be issues with Oracle enhanced adapter 1.3.0 as I am using some Rails methods which appeared just in Rails 2.3 – so in this case it might be safer to stay on previous Oracle enhanced adapter version 1.2.4 until you upgrade to latest Rails version.

Oracle CONTEXT index support

Every edition of Oracle database includes Oracle Text option for free which provides different full text indexing capabilities. Therefore in Oracle database case you don’t need external full text indexing and searching engines which can simplify your application deployment architecture.

The most commonly used index type is CONTEXT index which can be used for efficient full text search. Most of CONTEXT index creation examples show how to create simple full text index on one table and one column. But if you want to create more complex full text indexes on multiple columns or even on multiple tables and columns then you need to write your custom procedures and custom index refreshing logic.

Therefore to make creation of more complex full text indexes easier I have created additional add_context_index and remove_context_index methods that can be used in migrations and which creates additional stored procedures and triggers when needed in standardized way.

This is how you can create simple single column index:

add_context_index :posts, :title

And you can perform search using this index with

Post.contains(:title, 'word')

This is how you create index on several columns (which will generate additional stored procedure for providing XML document with specified columns to indexer):

add_context_index :posts, [:title, :body]

And you can search either in all columns or specify in which column you want to search (as first argument you need to specify first column name as this is the column which is referenced during index creation):

Post.contains(:title, 'word')
Post.contains(:title, 'word within title')
Post.contains(:title, 'word within body')

See Oracle Text documentation for syntax that you can use in CONTAINS function in SELECT WHERE clause.

You can also specify some dummy main column name when creating multiple column index as well as specify to update index automatically after each commit (as otherwise you need to synchronize index manually or schedule periodic update):

add_context_index :posts, [:title, :body], :index_column => :all_text,
  :sync => 'ON COMMIT'
Post.contains(:all_text, 'word')

Or you can specify that index should be updated when specified columns are updated (e.g. in ActiveRecord you can specify to trigger index update when created_at or updated_at columns are updated). Otherwise index is updated only when main index column is updated.

add_context_index :posts, [:title, :body], :index_column => :all_text,
  :sync => 'ON COMMIT', :index_column_trigger_on => [:created_at, :updated_at]

And you can even create index on multiple tables by providing SELECT statements which should be used to fetch necessary columns from related tables:

add_context_index :posts,
  [:title, :body,
  # specify aliases always with AS keyword
  "SELECT comments.author AS comment_author, comments.body AS comment_body FROM comments WHERE comments.post_id = :id"
  ],
  :name => 'post_and_comments_index',
  :index_column => :all_text,
  :index_column_trigger_on => [:updated_at, :comments_count],
  :sync => 'ON COMMIT'
# search in any table columns
Post.contains(:all_text, 'word')
# search in specified column
Post.contains(:all_text, "aaa within title")
Post.contains(:all_text, "bbb within comment_author")

In terms of Oracle Text performance in most cases it is good enough (typical response in not more that hundreds of milliseconds). But from my experience it is still slower compared to dedicated full text search engines like Sphinx. So in case if Oracle Text performance is not good enough (if you need all search operations return in tens of milliseconds) then you probably need to evaluate dedicated search engines like Sphinx or Lucene.

Other changes

Please see change history file or commit list to see more detailed list of changes in this version.

Install

As always you can install Oracle enhanced adapter on any Ruby platform (Ruby 1.8.7 or Ruby 1.9.1/1.9.2 or JRuby) with

gem install activerecord-oracle_enhanced-adapter

If you have any questions please use discussion group or report issues at GitHub or post comments here.

I am trying to tell more people at Oracle OpenWorld about Ruby and Rails and how it can be used with Oracle database. Unfortunately my session proposals were rejected by organizers but now there is a second chance to propose sessions at mix.oracle.com and top voted sessions will be accepted for conference. But currently my proposed sessions do not have enough votes :(

I would be grateful if my blog readers and Ruby on Oracle supporters would vote for my sessions Fast Web Applications Development with Ruby on Rails on Oracle and PL/SQL Unit Testing Can Be Fun!.

You need to log in to mix.oracle.com with your oracle.com login (or you should create new one if you don’t have it). And also you need to vote for at least one more session as well (as votes are counted if you have voted for at least 3 sessions). Voting should be done until end of this week (June 20).

And if you have other oracle_enhanced or ruby-plsql users in your
organization then please ask their support as well :)

Thanks in advance!

I just released ruby-plsql version 0.4.2 which mainly adds support for more PL/SQL procedure parameter types. See change history file for more detailed list of changes.

Object types and object methods

Now you can use ruby-plsql to construct PL/SQL objects and call methods on these object. For example, if you have the following type defined:

CREATE OR REPLACE TYPE t_address AS OBJECT (
  street    VARCHAR2(50),
  city      VARCHAR2(50),
  country   VARCHAR2(50),
  CONSTRUCTOR FUNCTION t_address(p_full_address VARCHAR2)
    RETURN SELF AS RESULT,
  MEMBER FUNCTION display_address(p_separator VARCHAR2 DEFAULT ',') RETURN VARCHAR2,
  MEMBER PROCEDURE set_country(p_country VARCHAR2),
  STATIC FUNCTION create_address(p_full_address VARCHAR2) RETURN t_address
);

Then you can construct PL/SQL objects and call methods on them:

# call default constructor with named parameters
address = plsql.t_address(:street => 'Street', :city => 'City', :country => 'Country')
# call default constructor with sequential parameters
address = plsql.t_address('Street', 'City', 'Country')
# call custom constructor
address = plsql.t_address('Street, City, Country')
address = plsql.t_address(:p_full_address => 'Street, City, Country')

# returned PL/SQL object is Hash object in Ruby
address == {:street => 'Street', :city => 'City', :country => 'Country'}

# but in addition you can call PL/SQL methods on it
address.display_address == 'Street, City, Country'
address.set_country('Other') == {:street => 'Street', :city => 'City', :country => 'Other'}

# or you can call object member methods also with explicit self parameter
plsql.t_address.display_address(:self => {:street => 'Street', :city => 'City', :country => 'Other'},
  :p_separator => ',') == 'Street, City, Country'

# or you can call static methods of type
plsql.t_address.create_address('Street, City, Country') ==
  {:street => 'Street', :city => 'City', :country => 'Country'}

Record types and table of record types inside packages

Now you can call Pl/SQL procedures with parameters which have record or table of record type that is defined inside PL/SQL package. For example if you have the following package:

CREATE OR REPLACE PACKAGE test_records IS
  TYPE t_employee IS RECORD(
    employee_id   NUMBER(15),
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    hire_date     DATE
  );
  TYPE t_employees IS TABLE OF t_employee;
  TYPE t_employees2 IS TABLE OF t_employee
    INDEX BY BINARY_INTEGER;
  FUNCTION test_employee (p_employee IN t_employee)
    RETURN t_employee;
  FUNCTION test_employees (p_employees IN t_employees)
    RETURN t_employees;
  FUNCTION test_employees2 (p_employees IN t_employees2)
    RETURN t_employees2;
END;

Then you can call these package functions from Ruby:

employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2010,2,26)
}
# PL/SQL record corresponds to Ruby Hash
plsql.test_records.test_employee(employee) == employee
# PL/SQL table corresponds to Ruby Array
plsql.test_records.test_employees([employee, employee]) == [employee, employee]
# PL/SQL index-by table corresponds to Ruby Hash
plsql.test_records.test_employees({1 => employee, 2 => employee}) == {1 => employee, 2 => employee}

If you will use table types defined inside PL/SQL packages then ruby-plsql will dynamically create session specific temporary tables which will be used to pass and get table parameter values. To ensure that these session specific temporary tables will be dropped you need to explicitly call plsql.logoff to close connection. For example, if you use ruby-plsql-spec for PL/SQL unit testing then in spec_helper.rb include

at_exit do
  plsql.logoff
end

to ensure that connection will be closed with plsql.logoff before Ruby script will exit. But in case of some script failure if this was not executed and you notice that there are temporary tables with RUBY_ prefix in your schema then you can call plsql.connection.drop_all_ruby_temporary_tables to drop all temporary tables.

Establish new connection

Now there is simpler connect! method how to establish new ruby-plsql connection when you need a new connection just for ruby-plsql needs. You can do it in several ways:

plsql.connect! username, password, database_tns_alias
plsql.connect! username, password, :host => host, :port => port, :database => database
plsql.connect! :username => username, :password => password, :database => database_tns_alias
plsql.connect! :username => username, :password => password, :host => host, :port => port, :database => database

And the good thing is that this method will work both with MRI 1.8 or 1.9 or with JRuby – you do not need to change the way how you are establishing connection to database.

Savepoints

Now there is simpler way how to define savepoints and how to rollback to savepoint:

plsql.savepoint "before_something"
plsql.rollback_to "before_something"

Check validity of database objects

Now ruby-plsql will check if referenced database object is valid before trying to call it. And if it will not be valid then corresponding compilation error will be displayed. For example, if you have invalid database object:

CREATE OR REPLACE FUNCTION test_invalid_function(p_dummy VARCHAR2) RETURN VARCHAR2 IS
  l_dummy invalid_table.invalid_column%TYPE;
BEGIN
  RETURN p_dummy;
END;

then when trying to call it

plsql.test_invalid_function('dummy')

you will get the following error message:

ArgumentError: Database object 'HR.TEST_INVALID_FUNCTION' is not in valid status
Error on line    2:   l_dummy invalid_table.invalid_column%TYPE;
     position   11: PLS-00201: identifier 'INVALID_TABLE.INVALID_COLUMN' must be declared
     position   11: PL/SQL: Item ignored

Other improvements

See History.txt file for other new features and improvements and see RSpec tests in spec directory for more usage examples.

I have released maintenance version of ActiveRecrod Oracle enhanced adapter with some bug fixes and some new features. This is the last maintenance version for Rails 2, I have already done majority of work to support also Rails 3 in next adapter versions, but that deserves another post when it will be ready :).

Detailed changes can be found in change history file and commit log, here I will point out the main changes.

Schema and structure dump

There are several improvements in schema (rake db:schema:dump) and structure dump (rake db:structure:dump) tasks. Now structure dump is improved to contain all schema objects in SQL statements format.

Also db:test:purge rake task (which is run before recreating test schema when running rake test or rake spec) is changed that it will delete all schema objects from test schema – including also views, packages, procedures and functions which are not recreated from schema.rb. So if you need to have additional database objects in your schema besides tables, indexes, sequences and synonyms (which are dumped in schema.rb) then you need to recreate them after standard rake task db:schema:load is run. Here is example how to execute any additional tasks after db:schema:load (include this in some .rake file in lib/tasks directory):

namespace :db do
  namespace :schema do
    task :load do
      Rake::Task["db:schema:create_other_objects"].invoke
    end
    task :create_other_objects do
      # include code here which creates necessary views, packages etc.
    end
  end
end

Additional options for schema definition methods

You can pass :temporary => true option for create_table method to create temporary tables.

You can use :tablespace => "tablespace name" option for add_index method to create index in non-default Oracle tablespace that is specified for user (e.g. if it is requested by your DBA for performance reasons). You can also define function based indexes using add_index and they will be correctly dumped in schema.rb.

Savepoints and nested ActiveRecord transactions

oracle_enhanced adapter now supports ActiveRecord nested transactions using database savepoints.

ruby-oci8 version

As I am using and testing oracle_enhanced adapter just with ruby-oci8 2.0.3 then I have made this as precondition (if you use MRI 1.8 or 1.9). So if you haven’t yet upgraded to latest ruby-oci8 version then please do so before upgrading to oracle_enhanced 1.2.4.

JNDI connection support

If you are using oracle_enhanced with JRuby then now you can also use JNDI database connections – please see this issue with comments to see some examples.

Install

As always you can install Oracle enhanced adapter on any Ruby platform (Ruby 1.8.6 / 1.8.7 or Ruby 1.9.1 or JRuby) with

gem install activerecord-oracle_enhanced-adapter

If you have any questions please use discussion group or post comments here.

In my previous post I already described how to do Oracle PL/SQL unit testing with Ruby. I now have named it as ruby-plsql-spec unit testing framework. But probably you didn’t want to read such long text or maybe it seemed for you too difficult to try it out therefore I prepared two screencasts to show how easy and fun it is :)

Testing simple function

The first example is based on classic BETWNSTR function example from utPLSQL tutorial.

Load screencast in QuickTime format (4.7 MB).

Testing procedure that changes tables

Second example is based on Quest Code Tester for Oracle testing tables demo screencast. So you can see both unit testing frameworks in action and can compare which you like better :)

Load screencast in QuickTime format (8.1 MB).

Test driven development

In both these screencasts I demonstrated how to do test driven development of PL/SQL

• Write little test of indended functionality before writing code.
• Write implementation of new functionality until this test passes and verify that all existing tests pass as well.
• Refactor implementation when needed and verify that all tests still pass.

From my experience TDD style of development can improve design and testability of code and also make you think before coding what you actually want to implement. But existing visual PL/SQL testing tools (Quest Code Tester, SQL Developer 2.1) do not quite support TDD style of development, they expect that there is already existing code that should be tested. Therefore this is one more ruby-plsql-spec advantage if you would like to do TDD style development in PL/SQL.

More information

Examples shown in screencasts are available in ruby-plsql-spec GitHub repository. And if you want to see more examples how to use ruby-plsql library for PL/SQL unit testing then you can take a look at ruby-plsql own RSpec tests or read previous posts about ruby-plsql.

Based on feedback from using ruby-plsql for PL/SQL unit testing I have release new version 0.4.1 with several new features. You can read about initial versions of ruby-plsql in previous blog posts.

Package variables

When you call methods on plsql Ruby object then ruby-plsql uses all_procedures and all_arguments data dictionary views to search for procedures and their argument metadata to construct corresponding PL/SQL block for execution. Unfortunately there are no corresponding data dictionary views for package variables (sometimes called “global variables”) that are defined in package specifications. Therefore there was no support for package variables in initial ruby-plsql versions.

But as there is quite frequent need in PL/SQL tests to set and get package variable values then I created the following solution for accessing package variables. I assume that typically package variables are defined in one line in package specifications and I scan PL/SQL package specification source in all_source data dictionary view for potential package variable definitions.

As a result if you have the following example of package specification:

CREATE OR REPLACE PACKAGE test_package IS
  varchar2_variable VARCHAR2(50);
  number_variable NUMBER(15,2);
  string_constant CONSTANT  VARCHAR2(10) := 'constant';
  integer_constant CONSTANT INTEGER := 1;
END;

then you can access these package variables in the same way as procedures:

plsql.test_package.varchar2_variable = 'test'
plsql.test_package.number_variable = 123
plsql.test_package.varchar2_variable # => 'test'
plsql.test_package.number_variable # => 123
plsql.test_package.string_constant # => 'constant'
plsql.test_package.integer_constant # => 1

Other basic data types as well as %ROWTYPE, %TYPE and schema object types are also supported for package variables. Only custom types defined in package specification are not supported (they are not supported for procedure parameters as well). As there are no data dictionary views for types defined in package specifications I don’t feel very enthusiastic about parsing package sources from all_source to get information about types defined inside packages :)

Views

In previous post I described how to use ruby-plsql to perform basic table operations. Now these operations can be performed also with views:

plsql.view_name.insert
plsql.view_name.first
plsql.view_name.all
plsql.view_name.count
plsql.view_name.update
plsql.view_name.delete

insert_values method

Additional insert_values method is added for tables and views which can be helpful in PL/SQL tests for test data preparation. You can specify with more compact syntax which data you would like to insert into table or view:

plsql.employees.insert_values [:employee_id, :first_name, :last_name],
    [1, 'First', 'Last'],
    [2, 'Second', 'Last']

# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'First', 'Last')
# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (2, 'Second', 'Last')

DBMS_OUTPUT logging

If you use DBMS_OUTPUT.PUT_LINE in your PL/SQL procedures to log some debug messages then you can use plsql.dbms_output_stream= method to set where these messages should be displayed. Use the following to display DBMS_OUTPUT messages in standard output:

plsql.dbms_output_stream = STDOUT

Or write DBMS_OUTPUT messages to file:

plsql.dbms_output_stream = File.new('debug.log', 'w')

STANDARD package procedures

Procedures from SYS.STANDARD package can be called without sys.standard prefix, e.g.:

plsql.sysdate
plsql.substr('abcde',2,2)

Other improvements

See History.txt file for other new features and improvements and see RSpec tests in spec directory for more usage examples.

And also this version of ruby-plsql requires ruby-oci8 gem latest version 2.0.3 (if you use MRI / standard Ruby interpreter 1.8.6, 1.8.7 or 1.9.1) so please upgrade it as well if you do not have it. But as previously you can use ruby-plsql with JRuby and Oracle JDBC driver as well.

Current PL/SQL unit testing options

Unit testing and TDD (test driven development) practices are nowadays one of the key software development practices. It is especially important if you are doing agile software development in small iterations where you need to automate unit testing as much as possible, as you cannot do manual regression testing of all existing and new functionality at the end of each iteration.

In some languages (like Java, Ruby, Python, C# etc.) there is quite good tools and frameworks support for unit testing and as a result there is quite high testing culture among top developers in these communities. But unfortunately in PL/SQL community so far automated unit testing is not used very often. During recent Oracle OpenWorld conference in presentations about unit testing when it was asked who is doing automated unit testing then only few hands were raised.

Why is it so? And what are current options for doing automated PL/SQL unit testing?

The first unit testing framework for PL/SQL was utPLSQL which was created by Steven Feuerstein and based on API defined by many other xUnit style frameworks (like e.g. JUnit). But the issue with this approach was that PL/SQL syntax for tests was quite verbose and tests were not very readable (see example). As a result Steven stopped developing further utPLSQL and currently there are no other active maintainers of this project. There are some other alternative frameworks which tried to simplify writing tests in PL/SQL (OUnit, pl/unit, PLUTO etc.) but none of them are very actively used and maintained by PL/SQL community.

Because of the issues with utPLSQL Steven Feuerstein started development of graphical interface tool for PL/SQL unit testing which is now Quest Code Tester for Oracle. This tool is actively developed and maintained by Quest Software but there are several issues with it:

• It is a commercial tool and as a result it will not become widely accepted by all PL/SQL developers. There is also a freeware edition of it but the functionality of it is very limited.
• It is a graphical tool – it can help you with quick creation of simple tests but when you will need more complex logic you might get stuck that you cannot do it (or you need to do it again in plain PL/SQL and have the same issues as in utPLSQL).
• It stores tests in database repository – and it means that it might be hard to maintain unit tests in version control system like Subversion or Git.

And finally also Oracle started to do something in PL/SQL unit testing area and there is unit testing support in latest SQL Developer version 2.1 which currently still is in early adopter status. SQL Developer has very similar approach to Quest Code Tester – it is graphical tool which stores tests and test results in repository. So the benefit of SQL Developer over Quest Code Tester is that it is free :) But compared to Quest Code Tester it still has less features (e.g. currently not all complex data types are supported) and still is not released as final version and still has bugs.

Ruby as testing tool for PL/SQL

As you probably know I am quite big Ruby fan and always exploring new ways how to use Ruby to increase my productivity. And Ruby community has very high testing culture and has many good tools for testing support (I like and use RSpec testing framework). Therefore some time ago I started to use Ruby and RSpec also for testing PL/SQL code in our projects where we use Ruby on Rails on top of Oracle databases with existing PL/SQL business logic.

I have created ruby-plsql library which provides very easy API for calling PL/SQL procedures from Ruby and recent ruby-plsql version supports majority of PL/SQL data types.

So let’s start with with simple example how to use Ruby, RSpec and ruby-plsql to create PL/SQL procedure unit test. I will use BETWNSTR procedure example from utPLSQL examples:

CREATE OR REPLACE FUNCTION betwnstr (
   string_in   IN   VARCHAR2,
   start_in    IN   INTEGER,
   end_in      IN   INTEGER
)
   RETURN VARCHAR2
IS
   l_start PLS_INTEGER := start_in;
BEGIN
   IF l_start = 0
   THEN
      l_start := 1;
   END IF;
   RETURN (SUBSTR (string_in, l_start, end_in - l_start + 1));
END;

I took example tests from utPLSQL and wrote them in Ruby and RSpec:

describe "Between string" do
  it "should be correct in normal case" do
    plsql.betwnstr('abcdefg', 2, 5).should == 'bcde'
  end
  it "should be correct with zero start value" do
    plsql.betwnstr('abcdefg', 0, 5).should == 'abcde'
  end
  it "should be correct with way big end value" do
    plsql.betwnstr('abcdefg', 5, 500).should == 'efg'
  end
  it "should be correct with NULL string" do
    plsql.betwnstr(nil, 5, 500).should be_nil
  end
end

As you can see the tests are much shorter than in utPLSQL and are much more readable (also more readable than utPLSQL template which can be used to generate utPLSQL tests). And also you can create these tests faster than using GUI tools like Quest Code Tester or SQL Developer.

More complex example

Second more complex example I took from SQL Developer unit testing tutorial. We will create tests for PL/SQL procedure AWARD_BONUS:

CREATE OR REPLACE
 PROCEDURE award_bonus (
  emp_id NUMBER, sales_amt NUMBER) AS
  commission    REAL;
  comm_missing  EXCEPTION;
BEGIN
  SELECT commission_pct INTO commission
    FROM employees2
      WHERE employee_id = emp_id;
  IF commission IS NULL THEN
    RAISE comm_missing;
  ELSE
    UPDATE employees2
      SET salary = NVL(salary,0) + sales_amt*commission
        WHERE employee_id = emp_id;
  END IF;
END award_bonus;

I didn’t quite like the testing approach in SQL Developer unit testing tutorial – it was assuming that there is already specific data in employees2 table and was testing procedure using specific primary key values. As a result tests are not very readable as you cannot see all input data in the test case and tests could easily broke if initial data in table are different.

Therefore I created tests in Ruby using better approach that each test creates all necessary data that are needed for it and at the end of test there are no side effects which can influence other tests:

describe "Award bonus" do
  include CustomerFactory
  
  [ [1000,  1234.55,  0.10,   1123.46],
    [nil,   1234.56,  0.10,   123.46],
    [1000,  1234.54,  0.10,   1123.45]
  ].each do |salary, sales_amt, commission_pct, result|
    it "should calculate base salary #{salary.inspect} + sales amount #{sales_amt} * " +
                  "commission percentage #{commission_pct} = salary #{result.inspect}" do
      employee = create_employee(
        :commission_pct => commission_pct,
        :salary => salary
      )
      plsql.award_bonus(employee[:employee_id], sales_amt)
      get_employee(employee[:employee_id])[:salary].should == result
    end
  end
end

I am generating three different tests with three different sets of input values. When you run these tests you see result:

Award bonus
- should calculate base salary 1000 + sales amount 1234.55 * commission percentage 0.1 = salary 1123.46
- should calculate base salary NULL + sales amount 1234.56 * commission percentage 0.1 = salary 123.46
- should calculate base salary 1000 + sales amount 1234.54 * commission percentage 0.1 = salary 1123.45

In addition I am using factory pattern (create_customer method) for test data creation. When using factory pattern you create test data creation method which will create valid new record with default field values. If in your test you need some specific non-default values then you can pass just these values as parameters to factory method. Factory pattern also helps in the maintenance of tests. For example, if new mandatory columns will be added to employees table then it will be necessary to add new fields with default values in factory methods and nothing should be changed in individual tests.

Here is example of employee factory implementation:

module EmployeeFactory
  # Creates new employee with valid field values.
  # Pass in parameters only field values that you want to override.
  def create_employee(params)
    employee = {
      :employee_id => plsql.employees2_seq.nextval,
      :last_name => 'Last',
      :email => 'last@example.com',
      :hire_date => Date.today,
      :job_id => plsql.jobs.first[:job_id],
      :commission_pct => nil,
      :salary => nil
    }.merge(params)
    plsql.employees2.insert employee
    get_employee employee[:employee_id]
  end
  
  # Select employee by primary key
  def get_employee(employee_id)
    plsql.employees2.first :employee_id => employee_id
  end
end

And here is additional test for testing if procedure will raise exception if one input value is missing:

  it "should raise ORA-06510 exception if commission percentage is missing" do
    salary, sales_amt, commission_pct = 1000,  1234.55,  nil
    employee = create_employee(
      :commission_pct => commission_pct,
      :salary => salary
    )
    lambda do
      plsql.award_bonus(employee[:employee_id], sales_amt)
    end.should raise_error(/ORA-06510/)
  end

How to use it

I hope that if you are looking for PL/SQL unit testing tool then you will try this out :) You can get examples from this article together with necessary setup code and installation instructions at http://github.com/rsim/ruby-plsql-spec.

If you have any feedback or questions or feature suggestions then please comment.

I have just released ruby-plsql gem version 0.4.0 which provides many new features. You can read about initial versions of ruby-plsql in previous blog posts.

Oracle complex data type support

Initial versions of ruby-plsql supported just simple Oracle types like NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, BLOB as PL/SQL procedure parameters. Now support for many more complex data types is added. See examples below how to call PL/SQL procedures with these complex data types.

PL/SQL Record

Let’s assume you have PL/SQL procedure with PL/SQL record type parameter (which most typically will be in table%ROWTYPE format):

CREATE TABLE test_employees (
          employee_id   NUMBER(15),
          first_name    VARCHAR2(50),
          last_name     VARCHAR2(50),
          hire_date     DATE
        );
CREATE OR REPLACE FUNCTION test_full_name (p_employee test_employees%ROWTYPE)
RETURN VARCHAR2 IS
BEGIN
  RETURN p_employee.first_name || ' ' || p_employee.last_name;
END;

Then you can create Ruby Hash with record field values (specifying field names as Symbols), e.g.:

p_employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2000,01,31)
}

and pass this Hash as a parameter which will be translated to PL/SQL record parameter by ruby-plsql:

plsql.test_full_name(p_employee) #=> "First Last"
# or
plsql.test_full_name(:p_employee => p_employee) #=> "First Last"

In the same way you can get PL/SQL function return values or output parameter values as Hash values.

Object type

In similar way also object type parameters can be passed as Hash values. In this case also nested objects or nested collections of objects are supported:

CREATE OR REPLACE TYPE t_address AS OBJECT (
  street    VARCHAR2(50),
  city      VARCHAR2(50),
  country   VARCHAR2(50)
);
CREATE OR REPLACE TYPE t_phone AS OBJECT (
  type            VARCHAR2(10),
  phone_number    VARCHAR2(50)
);
CREATE OR REPLACE TYPE t_phones AS TABLE OF T_PHONE;
CREATE OR REPLACE TYPE t_employee AS OBJECT (
  employee_id   NUMBER(15),
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  hire_date     DATE,
  address       t_address,
  phones        t_phones
);
CREATE OR REPLACE FUNCTION test_full_name (p_employee t_employee)
  RETURN VARCHAR2
IS
BEGIN
  RETURN p_employee.first_name || ' ' || p_employee.last_name;
END;

and from Ruby side you can call this PL/SQL function as:

p_employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2000,01,31),
  :address => {:street => 'Main street 1', :city => 'Riga', :country => 'Latvia'},
  :phones => [{:type => 'mobile', :phone_number => '123456'}, {:type => 'home', :phone_number => '654321'}]
}
plsql.test_full_name(p_employee) #=> "First Last"
# or
plsql.test_full_name(:p_employee => p_employee) #=> "First Last"

And also object type return values and output parameters will be returned as Ruby Hash values (with nested Hashes or Arrays if necessary).

There is one limitation that these object types should be defined as database types and not just inside PL/SQL package definition. Unfortunately you cannot access type definitions inside packages from OCI or JDBC drivers and as a result cannot call such procedures from outside of PL/SQL.

TABLE and VARRAY collections

TABLE and VARRAY collection parameters can be passed as Array values:

CREATE OR REPLACE TYPE t_numbers AS TABLE OF NUMBER(15);
CREATE OR REPLACE FUNCTION test_sum (p_numbers IN t_numbers)
  RETURN NUMBER
IS
  l_sum   NUMBER(15) := 0;
BEGIN
  IF p_numbers.COUNT > 0 THEN
    FOR i IN p_numbers.FIRST..p_numbers.LAST LOOP
      IF p_numbers.EXISTS(i) THEN
        l_sum := l_sum + p_numbers(i);
      END IF;
    END LOOP;
    RETURN l_sum;
  ELSE
    RETURN NULL;
  END IF;
END;

And from Ruby side:

plsql.test_sum([1,2,3,4]) #=> 10

CURSOR

You can get also cursor return values from PL/SQL procedures:

CREATE OR REPLACE FUNCTION test_cursor
  RETURN SYS_REFCURSOR
IS
  l_cursor  SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
  SELECT * FROM test_employees ORDER BY employee_id;
  RETURN l_cursor;
END;

can be called from Ruby in the following way:

plsql.test_cursor do |cursor|
  cursor.fetch #=> first row from test_employees will be returned
end

It is important to pass block parameter in this case and do something with returned cursor within this block as after ruby-plsql finishes PL/SQL procedure call it will close all open cursors and therefore it will not be possible to do anything with returned cursor outside this block.

It is also possible to use returned cursor as input parameter for another PL/SQL procedure:

CREATE OR REPLACE FUNCTION test_cursor_fetch(p_cursor SYS_REFCURSOR)
  RETURN test_employees%ROWTYPE
IS
  l_record  test_employees%ROWTYPE;
BEGIN
  FETCH p_cursor INTO l_record;
  RETURN l_record;
END;

which can be called from Ruby

plsql.test_cursor do |cursor|
  plsql.test_cursor_fetch(cursor) #=> first record as Hash
end

Note: you can pass cursors as PL/SQL procedure input parameter just when using ruby-plsql on MRI 1.8/1.9 with ruby-oci8, unfortunately I have not found a way how to pass cursor as input parameter when using JRuby and JDBC.

BOOLEAN

And finally you can use also PL/SQL BOOLEAN type – it is quite tricky data type as it is supported just by PL/SQL but not supported as data type in Oracle tables. But now you can also use it with ruby-plsql:

CREATE OR REPLACE FUNCTION test_boolean
  ( p_boolean BOOLEAN )
  RETURN BOOLEAN
IS
BEGIN
  RETURN p_boolean;
END;

plsql.test_boolean(true) #=> true

You can find more PL/SQL procedure call usage examples in ruby-plsql RSpec tests.

Table and sequence operations

I have been using and promoting to others ruby-plsql as PL/SQL procedure unit testing tool. As current PL/SQL unit testing tools are not so advanced and easy to use as Ruby unit testing tools then I like better to use Ruby testing tools (like RSpec) together with ruby-plsql to write short and easy to understand PL/SQL unit tests.

In unit tests in setup and teardown methods you typically need some easy way how to create some sample data in necessary tables as well as to validate resulting data in tables after test execution.

If you are Ruby on Rails developer then you probably will use ActiveRecord (or DataMapper) for manipulation of table data. But if Ruby is used just for unit tests then probably ActiveRecord would be too complicated for this task.

Therefore I added some basic table operations to ruby-plsql which might be useful e.g. in unit tests. Some syntax ideas for these table operations are coming from Sequel Ruby library.

INSERT

# insert one record
employee = { :employee_id => 1, :first_name => 'First', :last_name => 'Last', :hire_date => Time.local(2000,01,31) }
plsql.employees.insert employee # INSERT INTO employees VALUES (1, 'First', 'Last', ...)

# insert many records 
employees = [employee1, employee2, ... ]  # array of many Hashes
plsql.employees.insert employees

If primary key values should be selected from sequence then you can get next sequence values with

plsql.employees_seq.nextval # SELECT employees_seq.NEXTVAL FROM dual
plsql.employees_seq.currval # SELECT employees_seq.CURRVAL FROM dual

SELECT

# select one record
plsql.employees.first # SELECT * FROM employees
                      # fetch first row => {:employee_id => ..., :first_name => '...', ...}
plsql.employees.first(:employee_id => 1)  # SELECT * FROM employees WHERE employee_id = 1
plsql.employees.first("WHERE employee_id = 1")
plsql.employees.first("WHERE employee_id = :employee_id", 1)

# select many records
plsql.employees.all                       # => [{...}, {...}, ...]
plsql.employees.all(:order_by => :employee_id)
plsql.employees.all("WHERE employee_id > :employee_id", 5)

# count records
plsql.employees.count                     # SELECT COUNT(*) FROM employees
plsql.employees.count("WHERE employee_id > :employee_id", 5)

UPDATE

# update records
plsql.employees.update(:first_name => 'Second', :where => {:employee_id => 1})
                      # UPDATE employees SET first_name = 'Second' WHERE employee_id = 1

DELETE

# delete records
plsql.employees.delete(:employee_id => 1) # DELETE FROM employees WHERE employee_id = 1

Other SQL statements

Any other SELECT statement can be executed with

plsql.select :first, "SELECT ..."
# or
plsql.select :all, "SELECT ..."

or any other non-SELECT SQL statement can be executed with

plsql.execute "..."

And also COMMIT or ROLLBACK could be executed simply with

plsql.commit
plsql.rollback

I plan to write a separate blog post about how I recommend to create PL/SQL unit tests using Ruby and ruby-plsql and RSpec.

Install

As always you can install latest version of ruby-plsql with

gem install ruby-plsql

Latest gem version is just on Gemcutter but now it should be available as default gem source for all Ruby installations.

And as always ruby-plsql is supported both on

• Ruby 1.8.6/1.8.7 or Ruby 1.9.1 with ruby-oci8 gem version 2.0.3 or later (some specific issues with complex data types will be fixed in later versions of ruby-oci8)
• JRuby 1.3/1.4 with Oracle JDBC driver (testing mainly with ojdbc14.jar but also ojdbc5.jar or ojdbc6.jar should be fine)

Please try it out and tell me if there are any issues with some particular data types or if there are still some unsupported PL/SQL data types that you would like to be supported in ruby-plsql. And also I encourage you to try ruby-plsql out for PL/SQL unit testing if you had no PL/SQL unit tests previously :)

People

Last week I participated in annual Oracle OpenWorld 2009 conference. There is quite wide coverage of conference in various web sites and blogs therefore I will write just some personal notes that I wanted to highlight.

For me the most value was meeting with different interesting people. At first thanks to Justin Kestelyn and all OTN team for Oracle community support. Oracle ACE dinner, bloggers meetup, OTN lounge and unconference were great places where to meet and discuss with interesting and active Oracle community members.

It was nice to meet Kuassi Mensah and Christopher Jones who are supporters of dynamic languages in Oracle and supporters of Ruby in particular. And also had interesting discussions with Rich Manalang – Ruby guru at Oracle, who is from the AppsLab team.

This year there were quite a few Sun people in the conference. Scott McNealy and James Gosling were doing keynotes. And I had interesting discussions with Arun Gupta and Tim Bray. BTW they have very good coverage of Oracle OpenWorld in their blogs (and also have a fresh look at it as they were for the first time here).

This year I did two unconference sessions – Oracle adapters for Ruby ORMs and Server Installation and Configuration with Chef. They were not very many attendees but at least it seemed that those who attended were satisfied with content :) This year Oracle Develop track was located quite far from unconference location and probably this also was a reason why there were not very many attendees (as my sessions were quite developer oriented).

Technologies

Here is the list of Oracle products and technologies that I am interested in to spend some time investigating them:

• Fustion applications. I expected to hear more about next-generation of new Fusion applications but there was just short demo in the final keynote and a promise that they will be available sometime next year. User interface of new applications seems much better than for the current Oracle applications as well as current beta-testers are telling that usability is really much better. So I am really looking for trying them out.
• Application Development Framework (ADF). I am not a big fan of ADF drag-and-drop development style (that’s why I prefer Ruby on Rails :)) but as ADF is the main development platform for Fusion Applications then it will be necessary to use it if we would like to extend or customize Fusion applications. But what I would be really interested in is how to integrate JRuby with ADF – it would be nice to use ADF Faces UI components to get ADF look and feel, but to use JRuby for model & controller business logic development.
• SQL Developer unit testing. It was nice to see that finally Oracle has PL/SQL unit testing support in latest version of SQL Developer which hopefully will increase awareness about unit testing among PL/SQL developers. Steven Feuerstein gave very good “motivational” talk about unit testing during converence. But I still can’t decide if SQL Developer repository based unit tests is the best way how to do them. E.g. as all unit tests are stored in database repository you cannot version control them with Subversion or Git (which is the place where we store source of all PL/SQL procedures).
  Therefore I plan to make enhancements to my ruby-plsql gem to support more PL/SQL data types and then it would be possible to write PL/SQL unit tests with Ruby and RSpec which would provide more compact syntax compared to current utPLSQL framework. Need to write blog post about it :)
• Oracle Coherence. Recently I have heard many references to Oracle Coherence in-memory data grid which is often used to achieve high-scalability of web applications. Therefore I am thinking about Ruby client for Coherence and potentially using Coherence as cache solution in Ruby on Rails applications.
• Java in database. Recently I did some experiments with Java stored procedures in Oracle database – and the main reason is that it could provide integration of Oracle database with other systems that have Java based API. I already did experiments with creating Oracle client for RabbitMQ messaging system.
• Oracle object types. Many Oracle products (like Spatial Data option) are using Oracle object types for storing data. Currently these object data types are not supported by Ruby ActiveRecord and DataMapper ORMs. Need to do investigation how they could be supported and how to use Ruby e.g. for accessing spatial data in Oracle database.

Oracle Magazine’s Developer of the Year

And finally during Oracle OpenWorld annual Oracle Magazine Editors’ Choice Awards 2009 were published. And it was pleasant surprise for me that in this year I got Oracle Magazine’s Developer of the Year award. Thanks to Oracle people who promoted me and thanks for congratulations that I received :) Here is my picture and profile from the latest Oracle Magazine:

Photo © Delmi Alvarez / Getty Images

During the last months many new features have been implemented for ActiveRecord Oracle enhanced adapter which are now included in Oracle enhanced adapter version 1.2.2. You can find full list in change history file, here I will tell about the main ones.

Documentation

Now Oracle enhanced adapter has improved RDoc documentation for all public methods. So you can go to RDoc documentation of installed gem or go and view published documentation on-line.

Schema definition

There are many new features in schema definition methods that you can use in migration files:

• When you use add_index then ActiveRecord is automatically generating index name using format index_table_name_on_column1_and_column2_… which previously could cause Oracle errors as Oracle identifiers should be up to 30 characters long. Now default index names are automatically shortened down to 30 or less characters (of course you can always use also :name option to specify shortened version by yourself).
• Now adapter is ignoring :limit option for :text and :binary columns (as in Oracle you cannot specify limit for CLOB and BLOB data types). Previously it could cause errors if you tried to migrate Rails application from e.g. MySQL where :text and :binary columns could have :limit in schema definition.
• If you define :string column with* :limit option then it will define VARCHAR2 column with size in characters and not in bytes (this makes difference if you use UTF-8 with language where one character might be stored as several bytes). This is expected behavior from ActiveRecord that you define maximum string size in UTF-8 characters.
• Now you can use add_foreign_key and remove_foreign_key to define foreign key constraints in migrations (see RDoc documentation for details). Syntax and some implemenatation for foreign key definition was taken from foreigner Rails plugin as well as some ideas taken from active_record_oracle_extensions plugin.
• add_foreign_key definitions will be also extracted in schema.rb by rake db:schema:dump task. Therefore they will be also present in test database when you will recreate it from schema.rb file.
• Foreign keys are also safe for loading of fixtures (in case you are still using them instead of factories :)). disable_referential_integrity method is implemented for Oracle enhanced adapter which is called by ActiveRecord before loading fixtures and which disables all currently active foreign key constraints during loading of fixtures.
• You can use add_synonym and remove_synonym to define database synonyms to other tables, views or sequences. add_synonym definitions will also be extracted in schema.rb file.
• It is possible to create tables with primary key trigger. There will be no difference in terms how you would create new records in such table using ActiveRecord but in case you have also need to do direct INSERTs into the table then it will be easier as you can omit primary key from INSERT statement and primary key trigger will populate it automatically from corresponding sequence.
• ActiveRecord schema dumper is patched to work correctly when default table prefixes or suffixes are used – they are now removed from schema.rb dump to avoid duplicate prefixes and suffixes when recreating schema from schema.rb.

Legacy schema support

Some features which can support “weird” legacy database schemas:

• If you are using ActiveRecord with legacy schema which have tables with triggers that populate primary key triggers (and not using default Rails and Oracle enhanced adapter conventions) then you can use set_sequence_name :autogenerated in class definition to tell adapter to omit primary key value from INSERTs.
• You can use ActiveRecord also with tables that you can access over database link. To do that you need to define local synonym to remote table (and also remote sequence if you want to insert records as well) and then use local synonym in set_table_name in class definition. Previously adapter could not get remote table columns, now it will get table columns also over database link.
  But still you cannot specify remote table (like “table_name@db_link”) directly in set_table_name as table_name will be used as column prefix in generated SQL statements where “@db_link” will not be valid syntax.
  And when you define local synonyms then please use the new add_synonym feature :)

Connection options

• cursor_sharing option default value is changed from “similar” to “force” – please read explanation in discussion group post what it is and why the new default value is recommended choice.
• When using JRuby and JDBC you can set TNS_ADMIN environment variable to tnsnames.ora directory and then use TNS database alias in database.yml file (specify just database: option and remove host: option). This might be useful for more complex TNS connection definitions, e.g. connection to load balanced Oracle RAC.
• Adapter will not raise error if it cannot locate ojdbc14.jar* file. So either put it in $JRUBY_HOME/lib or ensure that it will be loaded by application server. Would love to hear feedback from people who are using this adapter with JRuby to find out if this behaves well now :)

Logging

• Now you can get PL/SQL debugging information into your ActiveRecord log file. Use dbms_output.put_line in your PL/SQL procedures and functions (that are called from ActiveRecord models) and in your ActiveRecord model use connection.enable_dbms_output and connection.disable_dbms_output around your database calls to get dbms_output logging information into ActiveRecord log file. But please use it just in development environment with debug log level as in production it would add too much overhead for each database call. And this feature also requires that you install ruby-plsql gem.

As you see this probably is the largest “point” release that I have had :) Thanks also to other contributors which patches were included in this release.

As always you can install Oracle enhanced adapter on any Ruby platform (Ruby 1.8.6 / 1.8.7 or Ruby 1.9.1 or JRuby) with

gem install activerecord-oracle_enhanced-adapter

If you have any questions please use discussion group or post comments here.

Oracle Database 10g is not yet officially supported on new Mac OS X 10.6 Snow Leopard but thanks to comments at my previous tutorial I managed to do Oracle 10g installation on fresh Mac OS X Snow Leopard.

If you have upgraded from Leopard with Oracle 10g installation to Snow Leopard then most probably Oracle 10g should work fine and you should not do anything. These instructions are just for fresh installation of Snow Leopard.

And also please take in mind that Oracle 10g on Snow Leopard is not supported yet by Oracle and therefore please do not run critical production applications on it :)

So here are my updated Oracle 10g installation instructions for Snow Leopard.

Initial preparation

At first you need Xcode tools installed on your Mac OS X.

Then you need to create oracle user as well as increase default kernel parameters. Open Terminal and switch to root user:

sudo -i

Create oinstall group and oracle user (I used group and user number 600 to ensure that they do not collide with existing groups and users):

dscl . -create /groups/oinstall
dscl . -append /groups/oinstall gid 600
dscl . -append /groups/oinstall passwd "*"
dscl . -create /users/oracle
dscl . -append /users/oracle uid 600
dscl . -append /users/oracle gid 600
dscl . -append /users/oracle shell /bin/bash
dscl . -append /users/oracle home /Users/oracle
dscl . -append /users/oracle realname "Oracle software owner"
mkdir /Users/oracle
chown oracle:oinstall /Users/oracle

Change password for oracle user:

passwd oracle

Change default kernel parameters:

vi /etc/sysctl.conf

and enter values recommended by Oracle:

kern.sysv.semmsl=87381
kern.sysv.semmns=87381
kern.sysv.semmni=87381
kern.sysv.semmnu=87381
kern.sysv.semume=10
kern.sysv.shmall=2097152
kern.sysv.shmmax=2197815296
kern.sysv.shmmni=4096
kern.maxfiles=65536
kern.maxfilesperproc=65536
net.inet.ip.portrange.first=1024
net.inet.ip.portrange.last=65000
kern.corefile=core
kern.maxproc=2068
kern.maxprocperuid=2068

Oracle DB installation scripts have reference to Java version 1.4.2 which is not present on Snow Leopard. The easiest way to fix it is to create symbolic link to newer version of Java:

sudo ln -s /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0 /System/Library/Frameworks/JavaVM.framework/Versions/1.4.2

After this reboot your computer so that these new kernel parameters would be taken into effect.

After reboot you need to log in as new “Oracle software owner” user (as now Snow Leopard has stricter control for access to X11 display and therefore I couldn’t manage to start Oracle installation just from terminal).

Open Terminal application and set shell settings in .bash_profile

vi .bash_profile

and enter

export DISPLAY=:0.0
export ORACLE_BASE=$HOME
umask 022
ulimit -Hn 65536
ulimit -Sn 65536

As you see I prefer to install all Oracle related files under home directory of oracle user therefore I am setting ORACLE_BASE to home directory. And also include ulimit settings – I forgot to do this initially and got strange TNS service errors because of that.

Now execute this script so that these settings are applied to current shell:

. ./.bash_profile

Now download db.zip installation archive and place it somewhere and unzip it:

mkdir Install
cd Install
# download db.zip to this directory
unzip db.zip
cd db/Disk1

Now you are ready to start installation. In Snow Leopard you need to pass -J-d32 option to installation script to force to run Java in 32-bit mode as some native libraries are 32-bit:

./runInstaller -J-d32

Installation

In installation wizard I selected the following options:

• Standard Edition – as I don’t need additional features of Enterprise Edition
• Install Software Only – we will need to do some fixes before database creation

In the middle of installation you will get error message “Error in invoking target ‘all_no_orcl ipc_g ihsodbc32’ …” (message truncated). Please do not press anything and switch to Terminal application.

cd ~/oracle/product/10.2.0/db_1/rdbms/lib
vi ins_rdbms.mk

and in this file you need to search for line containing HSODBC_LINKLINE (in vi enter /HSODBC_LINKLINE) and comment out this line with putting @# @ in front of it:

#	$(HSODBC_LINKLINE)

and save changed file.

In this way we disable failing compilation of library which is anyway not needed for our Oracle DB installation.

After that you can switch back to Oracle installation application and press Retry.

At the end of installation you will be instructed to run one shell script from root. To do that open new tab in Terminal and execute (substitute “username” with your login name):

su - username
sudo /Users/oracle/oracle/product/10.2.0/db_1/root.sh

Hopefully installation will complete successfully.

Creation of database

Switch back to Terminal tab with oracle user and add the following lines to .bash_profile of oracle user:

export ORACLE_HOME=/Users/oracle/oracle/product/10.2.0/db_1
export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin

and execute it

. ~/.bash_profile

Now you need to modify $ORACLE_HOME/jdk/bin/java script and change ...java -Xbootclasspath... to ...java -d32 -Xbootclasspath.... This is necessary to force netca and dbca utilities to run in 32-bit mode.

Now you need to do the major installation hack :) Unfortunately the main oracle executable binary when compiled under Snow Leopard is giving core dumps when starting Oracle database and currently the only way how I managed to fix it is to replace this executable file with the one which was compiled previously under Leopard. So you need to download it in trust me that it is correct :)

cd $ORACLE_HOME/bin
curl -O http://rayapps.com/downloads/oracle_se.zip
unzip oracle_se.zip
chmod ug+s oracle
rm oracle_se.zip

(If you installed Oracle Enterprise Edition then please substitute oracle_se.zip with oracle_ee.zip)

Now you can run Network Configuration Assistant

netca

and select all default options to create listener and wait until you get confirmation message that listener is configured and started.

After that you can run Database Configuration Assistant

dbca

and select

• Create a Database
• General Purpose
• Specify orcl as Global Database Name and SID (or set it to something different if you need)
• Specify password for SYS and SYSTEM users
• I selected also Sample Schemas
• and in Character Sets I selected Use Unicode (AL32UTF8)

At the end of installation I tried to use Password Management to unlock additional schemas but it didn’t work – so you need to unlock other sample schemas if needed using sqlplus.

At the end of installation verify if you can connect to newly created database

sqlplus system@orcl

I hope that my fixes will help you as well and you will be able to connect to database.

If you want to unlock other sample users then do it from sqlplus, e.g.:

alter user hr account unlock identified by hr;

Further instructions are the same as for Leopard and there are no more changes.

Change listener to listen on localhost

As I need this Oracle database just as local development database on my computer then I want to change the listener so that it would listen just on localhost port 1521:

vi $ORACLE_HOME/network/admin/listener.ora

and change it to:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /Users/oracle/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /Users/oracle/oracle/product/10.2.0/db_1)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Then also change ORCL alias definition in $ORACLE_HOME/network/admin/tnsnames.ora to:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

After this change restart listener and try to connect with sqlplus to verify that these changes are successful.

Automatic startup of Oracle database

If you want that Oracle database is started automatically when your computer is booted then you need to create the following startup script. Start terminal and switch to root.

At first edit /etc/oratab and change N to Y at the end of line for ORCL database – this will be used by dbstart utility to find which databases should be started automatically.

Then create startup script for Oracle database:

mkdir /Library/StartupItems/Oracle
cd /Library/StartupItems/Oracle
vi Oracle

and enter the following:

#!/bin/sh

# Suppress the annoying "$1: unbound variable" error when no option
# was given
if [ -z $1 ] ; then
  echo "Usage: $0 [start|stop|restart] "
  exit 1
fi

# source the common startup script
. /etc/rc.common

# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for the installation
ORACLE_HOME=/Users/oracle/oracle/product/10.2.0/db_1
DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME DYLD_LIBRARY_PATH

# change the value of ORACLE to the login name of the
# oracle owner at your site
ORACLE=oracle

PATH=$PATH:$ORACLE_HOME/bin

# Set shell limits for the Oracle Database
ulimit -Hu 2068
ulimit -Su 2068
ulimit -Hn 65536
ulimit -Sn 65536

StartService()
{
  ConsoleMessage "Starting Oracle Databases"
  su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
}

StopService()
{
  ConsoleMessage "Stopping Oracle Databases"
  su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
}

RestartService()
{
  StopService
  StartService
}

RunService "$1"

and then make this script executable

chmod a+x Oracle

and in addition create properties file:

vi StartupParameters.plist

with the following contents:

{
  Description     = "Oracle Database Startup";
  Provides        = ("Oracle Database");
  Requires        = ("Disks");
  OrderPreference = "None";
}

Now you can verify that these scripts are working. Open new terminal and try

sudo /Library/StartupItems/Oracle/Oracle stop

to stop the database and

sudo /Library/StartupItems/Oracle/Oracle start

to start again the database. And later you can reboot your computer also to verify that Oracle database will be started automatically.

Hide oracle user from login window

After computer reboot you probably noticed that now you got oracle user in initial login window. To get rid of it execute this from terminal:

sudo defaults write /Library/Preferences/com.apple.loginwindow HiddenUsersList -array-add oracle

What next?

Now when you have Oracle database installed you would need some development tools that you could use to access the database. Here are some links:

• Oracle SQL Developer – free Oracle GUI tool that supports Mac OS X as well
• If you would like to use Ruby and Ruby on Rails then check out my tutorial how to setup Ruby and Oracle client on Snow Leopard

Please comment if you find any issues with Oracle Database 10g installation on Snow Leopard using this tutorial.

Introduction

Mac OS X Snow Leopard is out and many Rubyists are rushing to upgrade to it. The main difference for Ruby after upgrading to Snow Leopard is that Ruby installation has been changed from 32-bit to 64-bit program and version has changed from 1.8.6 to 1.8.7. And it means that all Ruby gems with C extensions should be reinstalled and recompiled using 64-bit external libraries.

After upgrading to Snow Leopard the first thing to do is to follow instructions on official Ruby on Rails blog. After that follow instructions below.

Installing 64-bit Oracle Instant Client for Intel Mac

Download Oracle Instant Client 64-bit version. Download “Instant Client Package – Basic”, “Instant Client Package – SDK” and “Instant Client Package – SQL*Plus”.

Unzip downloaded archives and move it where you would like to have it – I am keeping it in /usr/local/oracle/instantclient_10_2 (if you have previous 32-bit Oracle Instant Client in this directory then delete it beforehand). Then go to this directory and make symbolic links for dynamic libraries

sudo ln -s libclntsh.dylib.10.1 libclntsh.dylib
sudo ln -s libocci.dylib.10.1 libocci.dylib

Then I recommend to create and place somewhere your tnsnames.ora file where you will keep your database connections definitions – I place this file in directory /usr/local/oracle/network/admin.

Then finally you need to set up necessary environment variables – I place the following definitions in my .bash_profile script:

export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient_10_2"
export SQLPATH="/usr/local/oracle/instantclient_10_2"
export TNS_ADMIN="/usr/local/oracle/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export PATH=$PATH:$DYLD_LIBRARY_PATH

Use your path to Oracle Instant Client if it differs from /usr/local/oracle/instantclient_10_2. And as you see I also define NLS_LANG environment variable – this is necessary if your database is not in UTF8 encoding but in Ruby you want to get UTF-8 encoded strings from the database. Specifying this NLS_LANG environment variable you will force that Oracle Instant Client will do character set translation.

After these steps relaunch Terminal application (so that new environment variables are set), specify database connection in tnsnames.ora file and try if you can access your database with sqlplus from command line.

Install ruby-oci8 gem

The latest versions of ruby-oci8 are available as Ruby gems and therefore I recommend to install it as a gem and not to compile and install as library (as I have recommended previously in my blog).

If you previously installed ruby-oci8 as a library then I recommend to delete it from Ruby installation. Go to /usr/lib/ruby/site_ruby/1.8 directory and remove oci8.rb file as well as remove oci8lib.bundle compiled library from either universal-darwin9.0 or universal-darwin10.0 subdirectory.

Now install ruby-oci8 with the following command:

sudo env DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH ARCHFLAGS="-arch x86_64" gem install ruby-oci8

It is important to pass DYLD_LIBRARY_PATH environment variable to sudo (as otherwise ruby-oci8 gem installation will not find Oracle Instant Client) as well as specify ARCHFLAGS to compile C extension just for 64-bit platform as otherwise it will try to compile both for 32-bit and 64-bit platform.

Now try

ruby -rubygems -e "require 'oci8'; OCI8.new('scott','tiger','orcl').exec('select * from dual') do |r| puts r.join(','); end"

or similar (replacing username, password or database alias) to verify that you can access Oracle database from ruby.

That’s it! Please write in comments if something is not working according to these instructions.

What is DataMapper?

DataMapper is Ruby Object/Relational Mapper that is similar to ActiveRecord (component of Ruby on Rails) but still it handles several things differently than ActiveRecord.

I got interested in DataMapper because I liked better some of its design decisions when compared with ActiveRecord. And in particular DataMapper architecture can suite better if you need to work with legacy Oracle database schemas – that is the area where I use Ruby on Rails a lot and for these purposes I also created Oracle enhanced adapter for ActiveRecord.

But as there were no Oracle adapter available for DataMapper I needed to create one :) I started to work on Oracle adapter for DataMapper after the RailsConf and now it is passing all DataMapper tests on all Ruby platforms – MRI 1.8, Ruby 1.9 and JRuby 1.3.

Why DataMapper for Oracle database?

If you would like to learn main differences between DataMapper and ActiveRecord then please start with this overview and this summary of benefits.

Here I will mention specific benefits if you would like to use DataMapper with Oracle database.

Model properties

In DataMapper you always specify in model class definition what Ruby “type” you would like to use for each model attribute (or property as called in DataMapper):

class Post
  include DataMapper::Resource
  property :id,         Serial
  property :title,      String
  property :post_date,  Date
  property :created_at, DateTime
  property :updated_at, Time
end

The main benefit for that is that you can explicitly define when to use Ruby Time, Date or DateTime class which is stored as DATE (or sometimes as TIMESTAMP) in Oracle database. In addition you can define your own custom DataMapper types and define how to serialize them into database.

Composite primary keys

DataMapper core library supports composite primary keys for models. If you use ActiveRecord then there is an option to use additional composite_primary_keys gem but it regularly breaks with latest ActiveRecord versions and quite often it also might break in some edge cases. In DataMapper composite primary keys are defined quite simple:

class City
  include DataMapper::Resource
  property :country,   String, :key => true
  property :name,      String, :key => true
end

Legacy schemas

DataMapper is quite useful when you want to put Ruby models on top of existing Oracle schemas. It is possible to provide different database field name for property or provide custom sequence name for primary keys:

class Post
  include DataMapper::Resource
  property :id, Serial, :field => "post_id", :sequence => "post_s"  
end

You can also define one model that can be persisted in two different repositories (e.g. databases or schemas) and use different naming conventions in each repository:

class Post
  include DataMapper::Resource
  repository(:old) do
    property :id, Serial, :field => "post_id", :sequence => "post_s"
  end
  repository(:default) do
    property :id, Serial
  end
end

As a result DataMapper can be used also for data migration between different databases.

Bind variables

ActiveRecord always generates SQL statements for execution as one single string. Therefore Oracle enhanced adapter always initializes Oracle session with setting cursor_sharing=‘similar’. It instructs Oracle always to take all literals (constants) from SQL statement and replace them with bind variables. It reduces the number of unique SQL statements generated but also it is some overhead for Oracle optimizer.

DataMapper always passes all statement parameters separately to corresponding database adapter and therefore it is possible for Oracle adapter to pass all parameters as bind variables to Oracle.

CLOB and BLOB values inserting and selecting

As for ActiveRecord all inserted values should be passed as literals in INSERT statement it was not possible to insert large CLOB and BLOB values directly in INSERT statement. Therefore ActiveRecord Oracle enhanced adapter did separate call-backs for inserting any CLOB or BLOB data after INSERT of other data. In DataMapper it is possible to insert all data at once as CLOB and BLOB data are passed as bind variables.

DataMapper also handles better lazy loading of large columns. So if you define property as Text then by default it will not be selected from database – it will be selected separately only when you use it. Typically it could reduce amount of data that needs to be sent from database to application as Text properties are quite often not needed in e.g. all web pages.

Wny not DataMapper?

If you are fine with ActiveRecord default conventions and you don’t have any issues that I listed previously then probably ActiveRecord is good enough for you and you shouldn’t change to DataMapper. There are of course much more Rails plugins that work with ActiveRecord but not yet with DataMapper. And DataMapper is still much less used and therefore there might some edge cases where it is not tested and you will need to find the issue causes by yourself.

But if you like to try new things then please try it out – and also DataMapper community is quite friendly and helpful and will help to solve any issues :)

Installation of DataMapper Oracle adapter

So if you have decided to try to use DataMapper with Oracle database then follow the instructions how to install it.

Oracle support is done for current development version 0.10.0 of DataMapper – therefore you will need to install the latest versions from GitHub (they are still not published as gems on RubyForge).

DataMapper with Oracle adapter can be used both on MRI 1.8.6 (I am not testing it on 1.8.7) and Ruby 1.9.1 as well as on JRuby 1.3. And currently installation is tested on Mac OS X and Linux – if there is anyone interested in Windows support then please let me know.

MRI 1.8.6 or Ruby 1.9.1

At first you need to have the same preconditions as for ActiveRecord:

• Oracle Instant Cient
• ruby-oci8 gem, version 2.0.2 or later

If you are using Mac then you can use these instructions for installation.

Now at first it is necessary to install DataObjects Oracle driver – DataObjects library is unified interface to relational databases (like SQLite, MySQL, PostgreSQL, Oracle) that DataMapper uses to access these databases.

At first validate that you have the latest version of rubygems installed and install necessary additional gems:

gem update --system
gem install addressable -v 2.0

As I mentioned currently you need to install the latest version from GitHub (at first create and go to directory where you would like to store DataMapper sources):

git clone git://github.com/datamapper/extlib.git
cd extlib
git checkout -b next --track origin/next
rake install
cd ..
git clone git://github.com/datamapper/do.git
cd do
git checkout -b next --track origin/next
cd data_objects
rake install
cd ../do_oracle
rake compile
rake install
cd ../..

Now if DataObjects installation was successful you can install DataMapper. UPDATE: Oracle adapter is now in “next” branch of DataMapper so now you need to install it form there:

git clone git://github.com/datamapper/dm-core.git
cd dm-core
git checkout -b next --track origin/next
rake install

Now start irb and test if you can connect to Oracle database (change database name, username and password according to your setup):

require "rubygems"
require "dm-core"
DataMapper.setup :default, "oracle://hr:hr@xe"

and try some basic DataMapper operations (I assume that you don’t have posts table in this schema):

class Post
  include DataMapper::Resource
  property :id,     Serial, :sequence => "posts_seq"
  property :title,  String
end
DataMapper.auto_migrate!
p = Post.create(:title=>"Title")
Post.get(p.id)
Post.auto_migrate_down!

JRuby

At first I assume that you have already installed JRuby latest version (1.3.1 at the moment).

Then you need to place Oracle JDBC driver ojdbc14.jar file in JRUBY_HOME/lib directory (other option is just to put somewhere in PATH).

All other installation should be done in the same way – just use “jruby -S gem” instead of “gem” and “jruby -S rake” instead of “rake” and it should install necessary gems for JRuby.

In addition before installing do_oracle gem you need to install do_jdbc gem (which contains general JDBC driver functionality):

# after installation of data_objects gem
cd ../do_jdbc
jruby -S rake compile
jruby -S rake install
# continue with do_oracle installation

Other DataMapper gems

DataMapper is much more componentized than ActiveRecord. Here I described how to install just the main dm-core gem. You can see the list of other gems in DataMapper web site.

To install additional DataMapper gems you need to

git clone git://github.com/datamapper/dm-more.git
cd dm-more
git checkout -b next --track origin/next
cd dm-some-other-gem
rake install

Questions?

This was my first attempt to describe how to start to use DataMapper with Oracle. If you have any questions or something is not working for you then please write comments and I will try to answer and fix any issues in these instructions.

I have released ruby-plsql gem (Ruby API for Oracle PL/SQL procedure calls) new version 0.3.0 which includes several new features.

Ruby 1.9.1

Probably the most important is support for Ruby 1.9.1 – now you can use both Oracle enhanced adapter and ruby-plsql gem on all three major Ruby plaforms:

• MRI 1.8.6 with ruby-oci8 1.0.x library or gem
• Ruby/YARV 1.9.1 with ruby-oci8 2.0 library or gem (currently just trunk version of ruby-oci8 2.0 contains the last bug fixes for Ruby 1.9.1)
• JRuby (so far tested with 1.1.6) with JDBC Oracle driver

ActiveRecord connection

In addition usage of ruby-plsql gem in Ruby on Rails project is simplified. Now you can include in environment.rb or some initializer file just:

plsql.activerecord_class = ActiveRecord::Base

and you don’t need to specify plsql.connection anymore – it will always use current ActiveRecord connection. This is also useful when ActiveRecord reestablishes connection to database as you don’t need to reestablish plsql connection in this case.

In addition if you use several different connections to Oracle database then you can assign to plsql.activerecord_class also different class that inherits from ActiveRecord::Base and has connection to different database.

Database time zone

Also you can also specify in which timezone DATE values are stored in database:

plsql.default_timezone = :local

or

plsql.default_timezone = :utc

This will affect how DATE values (without timezone) will be converted to Time or DateTime values (with timezone), default selection is :local timezone. If you have set plsql.activerecord_class then the value will be taken from ActiveRecord::Base.default_timezone.

BLOB support

You can now use BLOB data type for input and output parameters and function return values.
I remind you that also NUMBER, VARCHAR2, DATE, TIMESTAMP and CLOB data types are supported,

Synonym support

Now you can also use private and public database synonyms to functions or procedures or packages.
E.g. if ORA_LOGIN_USER is public database synonym to SYS.LOGIN_USER function then instead of

plsql.sys.login_user

you can use

plsql.ora_login_user

Installation

To install the gem as always do

sudo gem install ruby-plsql

or call the correct gem command version for JRuby or Ruby 1.9.1.

Source code of ruby-plsql is located at GitHub where you can find usage examples in RSpec tests.

UPDATE: Created instructions how to install Oracle 10g on Mac OS X Snow Leopard

Couple days ago Oracle developers on Mac OS X received Easter present – finally Oracle Database 10g was released for Mac OS X 10.5 Intel platform. This download includes installation guide for Mac OS X but as any Oracle installation guide it is quite long and contains a lot of unnecessary information for first time install as well as does not contain some necessary information.

Therefore I am posting here my shorter tutorial how to install it. And this tutorial is targeted to developers who want to install local Oracle database for development needs on their MacBook, iMac or Mac Pro.

Initial preparation

If you are a developer then I suppose you already have Xcode tools installed which are required also for Oracle installation. And I tried these steps on Mac OS X latest version 10.5.6.

Then you need to create oracle user as well as increase default kernel parameters. Open Terminal and switch to root user:

sudo -i

Create oinstall group and oracle user (I used group and user number 600 to ensure that they do not collide with existing groups and users):

dscl . -create /groups/oinstall
dscl . -append /groups/oinstall gid 600
dscl . -append /groups/oinstall passwd "*"
dscl . -create /users/oracle
dscl . -append /users/oracle uid 600
dscl . -append /users/oracle gid 600
dscl . -append /users/oracle shell /bin/bash
dscl . -append /users/oracle home /Users/oracle
dscl . -append /users/oracle realname "Oracle software owner"
mkdir /Users/oracle
chown oracle:oinstall /Users/oracle

Change password for oracle user:

passwd oracle

Change default kernel parameters:

vi /etc/sysctl.conf

and enter values recommended by Oracle:

kern.sysv.semmsl=87381
kern.sysv.semmns=87381
kern.sysv.semmni=87381
kern.sysv.semmnu=87381
kern.sysv.semume=10
kern.sysv.shmall=2097152
kern.sysv.shmmax=2197815296
kern.sysv.shmmni=4096
kern.maxfiles=65536
kern.maxfilesperproc=65536
net.inet.ip.portrange.first=1024
net.inet.ip.portrange.last=65000
kern.corefile=core
kern.maxproc=2068
kern.maxprocperuid=2068

After this reboot your computer so that these new kernel parameters would be taken into effect. After reboot open again Terminal and now login as oracle user:

su - oracle

Set shell settings in .bash_profile

vi .bash_profile

and enter

export DISPLAY=:0.0
export ORACLE_BASE=$HOME
umask 022
ulimit -Hn 65536
ulimit -Sn 65536

As you see I prefer to install all Oracle related files under home directory of oracle user therefore I am setting ORACLE_BASE to home directory. And also include ulimit settings – I forgot to do this initially and got strange TNS service errors because of that.

Now execute this script so that these settings are applied to current shell:

. ./.bash_profile

Now download db.zip installation archive and place it somewhere and unzip it:

mkdir Install
cd Install
# download db.zip to this directory
unzip db.zip
cd db/Disk1

Now you are ready to start installation:

./runInstaller

Installation

In installation wizard I selected the following options:

• Advanced Installation – so that I can change some default options
• Standard Edition – as I don’t need additional features of Enterprise Edition
• Create Database / General Purpose
• Global database name: orcl, SID: orcl
• Character set: UTF-8 AL32UTF8
• Create database with sample schemas
• Selected “Use the same password for all the accounts” – do not specify default “manager” password as it will not be allowed :)
• Password Management – selected this to unlock necessary sample accounts (e.g. HR schema account that I use as default test schema)

At the end of installation you will be instructed to run one shell script from root.
Hopefully installation will complete successfully.

Additional oracle user settings

If you will use oracle user later then add the following lines to .bash_profile of oracle user:

export ORACLE_HOME=/Users/oracle/oracle/product/10.2.0/db_1
export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=ORCL
PATH=$PATH:$ORACLE_HOME/bin

After this relogin as oracle user and verify listener status:

lsnrctl status

and if it is down then start it with

lsnrctl start

and verify if you can connect to Oracle database with sample user (that I unlocked during installation)

sqlplus hr/hr@orcl

If it fails then do some investigation :)

Change listener to listen on localhost

As I need this Oracle database just as local development database on my computer then I want to change the listener so that it would listen just on localhost port 1521:

vi $ORACLE_HOME/network/admin/listener.ora

and change the contents of the file to:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /Users/oracle/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /Users/oracle/oracle/product/10.2.0/db_1)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

Then also change ORCL alias definition in $ORACLE_HOME/network/admin/tnsnames.ora to:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

After this change restart listener and try to connect with sqlplus to verify that these changes are successful.

Automatic startup of Oracle database

If you want that Oracle database is started automatically when your computer is booted then you need to create the following startup script. Start terminal and switch to root.

At first edit /etc/oratab and change N to Y at the end of line for ORCL database – this will be used by dbstart utility to find which databases should be started automatically.

Then create startup script for Oracle database:

mkdir /Library/StartupItems/Oracle
cd /Library/StartupItems/Oracle
vi Oracle

and enter the following:

#!/bin/sh

# Suppress the annoying "$1: unbound variable" error when no option
# was given
if [ -z $1 ] ; then
  echo "Usage: $0 [start|stop|restart] "
  exit 1
fi

# source the common startup script
. /etc/rc.common

# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for the installation
ORACLE_HOME=/Users/oracle/oracle/product/10.2.0/db_1
DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME DYLD_LIBRARY_PATH

# change the value of ORACLE to the login name of the
# oracle owner at your site
ORACLE=oracle

PATH=$PATH:$ORACLE_HOME/bin

# Set shell limits for the Oracle Database
ulimit -Hu 2068
ulimit -Su 2068
ulimit -Hn 65536
ulimit -Sn 65536

StartService()
{
  ConsoleMessage "Starting Oracle Databases"
  su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
}

StopService()
{
  ConsoleMessage "Stopping Oracle Databases"
  su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
}

RestartService()
{
  StopService
  StartService
}

RunService "$1"

and then make this script executable

chmod a+x Oracle

and in addition create properties file:

vi StartupParameters.plist

with the following contents:

{
  Description     = "Oracle Database Startup";
  Provides        = ("Oracle Database");
  Requires        = ("Disks");
  OrderPreference = "None";
}

Now you can verify that these scripts are working. Open new terminal and try

sudo /Library/StartupItems/Oracle/Oracle stop

to stop the database and

sudo /Library/StartupItems/Oracle/Oracle start

to start again the database. And later you can reboot your computer also to verify that Oracle database will be started automatically.

Hide oracle user from login window

After computer reboot you probably noticed that now you got oracle user in initial login window. To get rid of it execute this from terminal:

sudo defaults write /Library/Preferences/com.apple.loginwindow HiddenUsersList -array-add oracle

What next?

Now when you have Oracle database installed you would need some development tools that you could use to access the database. Here are some links:

• Oracle SQL Developer – free Oracle GUI tool that supports Mac OS X as well
• If you would like to use Ruby and Ruby on Rails then check out my tutorial how to setup Ruby and Oracle client
• If you would like to use PHP then you can check out this tutorial

Please comment if you find any issues with Oracle Database 10g installation using this tutorial.

So far if you wanted to use Ruby on Rails on Oracle database you needed to use different adapters depending on the Ruby platform that you wanted to use. If you used original MRI (Matz Ruby interpreter) 1.8.6 then hopefully you were using Oracle enhanced adapter. But if you wanted to use JRuby then you needed to use JDBC adapter that is maintained by JRuby team (and which sometimes might work differently than Oracle enhanced adapter). And if you wanted to use new Ruby 1.9.1 then you were out of luck as no adapter supported it.

Therefore I wanted to announce great news that ActiveRecord Oracle enhanced adapter 1.2.0 is released and it supports all three major Ruby platforms!

• Use Oracle enhanced adapter on MRI 1.8.6 with ruby-oci8 1.0.x library or gem
• Use Oracle enhanced adapter on JRuby (so far tested with 1.1.6) with JDBC Oracle driver
• Use Oracle enhanced adapter on Ruby/YARV 1.9.1 with ruby-oci8 2.0 library or gem

This provides you with much more flexibility to develop on one Ruby platform but deploy on another and on all three platforms you can use the same additional functionality that Oracle enhanced adapter provides on top of standard ActiveRecord functionality.

And during testing of Oracle enhanced adapter on all platforms additional milestone was achieved – Oracle enhanced adapter passes 100% ActiveRecord unit tests! But to be honest I need to tell that I needed to patch quite many unit tests for Oracle specifics as not all SQL that runs on MySQL is also valid on Oracle. I published my patched branch of ActiveRecord unit tests at my GitHub fork of Rails – you can clone the repository and verify by yourself.

So please try out new version of Oracle enhanced adapter on any Ruby platform:

gem install activerecord-oracle_enahnced-adapter

If you have any questions please use discussion group or post comments here. In nearest future I will also add more instructions how to install Oracle enhanced adapter on JRuby and Ruby 1.9.1 at GitHub wiki page.

I’m glad to see that there are many users of ActiveRecord Oracle enhanced adapter and therefore comments in this blog is not anymore the best way how to communicate with Oracle enhanced adapter users. Therefore I created several other information exchange places which I hope will be more effective.

The latest addition is wiki pages on GitHub where I put usage description and examples as well as some troubleshooting hints that previously were scattered in README file and different blog posts. This is the first place where to look for information about Oracle enhanced adapter. And if you are GitHub user then you can correct mistakes or add additional content also by yourself.

If you have some question or you would like to discuss some feature then you can use Google discussion group. I will use this discussion group also for new release announcements as well so subscribe to it if you would like to get Oracle enhanced adapter news.

If you would like to report some bug or new feature (and patch would be greatly appreciated) then please use Lighthouse issue tracker.

And source code of Oracle enhanced adapter is still located in Github repository. If you are GitHub user then you can watch it or even fork it and experiment with some new features.

And BTW I just released Oracle enhanced adapter version 1.1.9 with some new features and it has been also tested with latest Rails 2.2 release.

On last day of Oracle OpenWorld I did my unconference presentation – Using Ruby on Rails with legacy Oracle databases.

As I did not know if anyone will come to listen to it I was glad that six people attended (including Kuassi Mensah from Oracle who is helping to promote Ruby support inside Oracle). And on the previous day I also managed to show parts of my presentation to Rich and Anthony from Oracle AppsLab team.

I published my slides on Slideshare:

And I published my demo project on GitHub:

Thanks to all Oracle people who recognize my work on Ruby and Oracle integration and I hope that our common activities will increase number of Ruby and Rails projects on Oracle :)

On last day of Oracle OpenWorld I did my unconference presentation – Using Ruby on Rails with legacy Oracle databases.

As I did not know if anyone will come to listen to it I was glad that six people attended (including Kuassi Mensah from Oracle who is helping to promote Ruby support inside Oracle). And on the previous day I also managed to show parts of my presentation to Rich and Anthony from Oracle AppsLab team.

I published my slides on Slideshare:

And I published my demo project on GitHub:

Thanks to all Oracle people who recognize my work on Ruby and Oracle integration and I hope that our common activities will increase number of Ruby and Rails projects on Oracle :)

In the previous post I wrote that I didn’t quite like drag-and-drop development style in JDeveloper that I experienced during Oracle OpenWorld hands-on session.

Today I was in Oracle BI Answers & Dashboard hands-on session and during this session I was also drag-and-dropping to create reports, charts and business intelligence dashboards. But in this case I liked it as it seemed natural way how to create such reports.

The main difference why I liked it was that I got immediate feedback how the end report will look like – after each change I could immediately see and test real report with real data. And such immediate feedback is key prerequisite for interactive analytical reporting development.

In JDeveloper case I needed to compile and build everything and restart local application server after each change to see real results from any change – and it took at least 10 seconds for just sample “hello world” application. I assume that this lag will be even longer in larger real projects. Probably it is not so long time but when you compare it to 1 second feedback time then anything larger seems long.

I also visited Oracle demo grounds where I discussed my concerns regarding JDeveloper drag-and-drop development style. At the end we reached common understanding that JDeveloper and ADF framework is really good for former Oracle Forms developers who do not want to learn HTML, CSS and Javascript and auto-generated applications could be quite OK for internal enterprise applications. But if you want to build advanced web applications you still need to learn and be expert in these web technologies (HTML, CSS and Javascript).

Today was my first day at Oracle OpenWorld and here are my first impressions from the sessions I attended.

The first one was Building Web 2.0 Social Applications in Ruby on Rails with BEA AquaLogic Interaction by Chris Bucchere where he explained how they built social application for BEA conference participants. And it was interesting to see also some code examples from this application that he presented.

And then after that I participated in three Oracle Develop hands-on sessions about Oracle SOA suite and WebCenter. I signed up for these sessions because I thought that they will be more technical and code-intensive compared to other sessions.

As a result I spent three hours in Oracle JDeveloper but all I was doing was dragging-and-dropping and filling some pop-up dialog boxes. I did not write single line of Java code during these workshops. And as a result I had just vague idea why all the dragged components worked together.

Having experience of coding in Ruby and trying to create small and beautiful code this drag-and-drop development did not feel quite natural for me. And I am afraid that this drag-and-drop development approach will create bunch of developers who will not be able to create real code anymore.

The other consequences of this drag-and-drop style coding is that it is hard to version control the resulting generated code and it is also much harder to unit test such generated code. Probably this is the reason why I could not find any OpenWorld session about unit testing and test-driven development.

Am I missing some hidden beauty of drag-and-drop development? Or am I spoiled with beauty of Ruby?

I just returned from RailsConf Europe in Berlin and attended a lot of good Ruby and Rails related sessions.

I also tried to submit there my session proposal about using Rails with Oracle but as there were too many good proposals and as my topic was with too narrow topic then it was not accepted. Therefore I used opportunity to give 5 minutes presentation about Oracle enhanced adapter in RejectConf where anybody could present anything.

Here are my slides that I used in this presentation. And at least one participant was interested in this topics as he uses Rails and Oracle and did not know about my adapter.

My next public appearance is planned at Oracle OpenWorld unconference where I have recerved time slot on Thursday, September 25th at 10am. Please come there if you are attending Oracle OpenWorld and are interested in how to use Ruby on Rails with your legacy Oracle databases.

Short information about latest enhancements in ActiveRecord Oracle enhanced adapter:

• Oracle enhanced adapter is now compatible with composite_primary_keys gem which is quite useful if you are working with legacy databases.
• Adapter now is also working correctly with Rails 2.1 partial_updates enabled. Previously I mentioned that you needed to disable partial_updates when using CLOB/BLOB columns. Now the issue is found and fixed and partial_updates are working with CLOB/BLOB columns.
• Support for other date and time formats when assigning string to :date or :datetime column. For example, if you would like to assign strings with format dd.mm.yyyy to date and datetime columns then add the following configuration options:
  

  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.string_to_date_format = "%d.%m.%Y"
  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.string_to_time_format = "%d.%m.%Y %H:%M:%S"
  

To get the new release of Oracle enhanced adapter do as always:

sudo gem install activerecord-oracle_enhanced-adapter

Warning: this post will be unrelatead to Ruby :)

For many years our company is using tool for Oracle E-Business Suite setup management that we initially built for our own purposes but now also we are offering to other Oracle partners and customers.

As I was bored to do demonstrations of it over and over again I finally decided to create a screencast of this tool (it’s 22 MB large). And I think that my first experiment in screencasting is not too bad :)

Previously screencasting was quite hard stuff as there was not so many good tools for that. In this case I used ScreenFlow tool which was released this year (and is available just on Mac OS X Leopard) and I have to say that it is much more better and easier compared to any other tools that I have seen earlier. So I am strongly recommending it to others who are interested in screencasting.

In some Ruby on Rails projects I am putting ActiveRecord interface on top of existing legacy databases. It is quite easy to specify legacy table names and primary keys in ActiveRecord models so that ActiveRecord would generate correct SQL SELECT statements to read data from legacy databases.

But it is more difficult to insert, update and delete in legacy databases using ActiveRecord. When using Oracle legacy databases then quite often they have exposed PL/SQL APIs for writing to tables and you are typically not allowed to directly modify tables with INSERT, UPDATE and DELETE statements.

Therefore I created support for custom create, update and delete methods in the version 1.1.3 of ActiveRecord Oracle enhanced adapter which uses also my ruby-plsql gem.

Let’s look at the following example. Assume that we have the following table:

CREATE TABLE test_employees (
  employee_id   NUMBER(6,0),
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25),
  hire_date     DATE,
  salary        NUMBER(8,2),
  version       NUMBER(15,0),
  create_time   DATE,
  update_time   DATE
)

And we have the following PL/SQL API package that should be used to write to this table:

CREATE OR REPLACE PACKAGE test_employees_pkg IS
  PROCEDURE create_employee(
      p_first_name    VARCHAR2,
      p_last_name     VARCHAR2,
      p_hire_date     DATE,
      p_salary        NUMBER,
      p_employee_id   OUT NUMBER);
  PROCEDURE update_employee(
      p_employee_id   NUMBER,
      p_first_name    VARCHAR2,
      p_last_name     VARCHAR2,
      p_hire_date     DATE,
      p_salary        NUMBER);
  PROCEDURE delete_employee(
      p_employee_id   NUMBER);
END;

Then we define ActiveRecord model in the following way:

class TestEmployee < ActiveRecord::Base
  set_primary_key :employee_id
  # should return ID of new record
  set_create_method do
    plsql.test_employees_pkg.create_employee(
      :p_first_name => first_name,
      :p_last_name => last_name,
      :p_hire_date => hire_date,
      :p_salary => salary,
      :p_employee_id => nil
    )[:p_employee_id]
  end
  # return value is ignored
  set_update_method do
    plsql.test_employees_pkg.update_employee(
      :p_employee_id => id,
      :p_first_name => first_name,
      :p_last_name => last_name,
      :p_hire_date => hire_date,
      :p_salary => salary
    )
  end
  # return value is ignored
  set_delete_method do
    plsql.test_employees_pkg.delete_employee(
      :p_employee_id => id
    )
  end
end

And as a result we can use this model in the same way as other ActiveRecord models:

@employee = TestEmployee.create(
  :first_name => "First",
  :last_name => "Last",
  :hire_date => Date.today
)
@employee.reload
@employee.first_name = "Second"
@employee.save!
@employee.destroy

And all writing to the database will be done using defined API procedures.

Currently this functionality is embedded into Oracle enhanced adapter but if somebody needs it also for other databases this functionality could easily be extraced from the adapter.

To get the new release of Oracle enhanced adapter just do:

sudo gem install activerecord-oracle_enhanced-adapter

Source code of Oracle enhanced adapter is located at GitHub and you can submit bug reports and enhancement requests in Lighthouse.

If you still have not voted for my session proposal “Using Ruby on Rails with Oracle E-Business Suite” then please do so :) Voting deadline is near – July 13th.

In any case I am going to be at Oracle OpenWorld conference in San Francisco in September. So if some of my blog readers will also be there and would like to have some chat with me about Oracle and Ruby then please let me know.

I just released new version of ActiveRecord Oracle enhanced adapter which includes several new enhancements which could be useful if you use ActiveRecord with legacy Oracle databases:

• Added ignore_table_columns option for class definitions
  You can specify which table (or view) columns should be ignored by ActiveRecord – these could be either columns which you do not need in Rails application or which have currently unsupported data types. For example:
  

  class Employee < ActiveRecord::Base
    ignore_table_columns  :phone_number, :hire_date
  end
  

• Added support for TIMESTAMP columns
  You can create tables with :timestamp data type which will create TIMESTAMP columns and you can access values from TIMESTAMP columns. Unfortunately due to current ruby-oci8 limitations when you will retrieve TIMESTAMP values it will be without fractional seconds (but if you pass Time value with fractional seconds then it will be stored in database).
• NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT independent DATE and TIMESTAMP columns support
  By default Oracle adapter (and enhanced adapter as well) changes NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT to ‘DD-MON-YYYY HH24:MI:SS’. But if you are working with some legacy database which requires different NLS date settings then now you can change these settings to different ones. You can put in some initialization file some other NLS settings, e.g.:
  

  ActiveRecord::Base.connection.execute %q{alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'}
  ActiveRecord::Base.connection.execute %q{alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS'}
  

  
  and ActiveRecord will continue to work correctly with date values.

Also one Rails 2.1 related issue was solved. Previously Oracle enhanced adapter always added after_save callback to ActiveRecord session store which stores BLOB data in the database (as in Rails 2.0 there was issue that this callback was not added). Now in Rails 2.1 this callback is added by ActiveRecord therefore Oracle enhanced adapter checks if this callback is already added or not. So now this should work correctly both in Rails 2.0 and Rails 2.1.

Also I have noticed that in some Rails 2.1 applications Oracle adapter is failing on ActiveRecord session store updates when partial updates are enabled. If you also have such issue then put this into your initialization file which will work as workaround until I will find what is causing this problem:

CGI::Session::ActiveRecordStore::Session.partial_updates = false

To get the new release just do:

sudo gem install activerecord-oracle_enhanced-adapter

Source code of Oracle enhanced adapter is located at GitHub. And you can submit bug reports and enhancement requests in Lighthouse. There you can see also some identified enhancements – please add comments to them if you also would like to have them. Then it will be easier for me to select what to include in next version of adapter.

Some time ago I created ruby-plsql gem which provides simple Ruby API for Oracle PL/SQL stored procedures.

Initially this gem supported just MRI with ruby-oci8 library which provides connectivity to Oracle database as this was my main development and production environment for Ruby & Oracle applications. But as JRuby is fast growing alternative Ruby deployment platform and as it can be integrated into Oracle Fusion middleware platform (e.g. Oracle Mix is running on JRuby on Oracle Fusion middleware) then I planned to support JRuby as well.

I started to work on JRuby support during RailsConf JRuby hackfest and initially this did not seem very hard task for me. But as I did not know JDBC very well it took me much more time than initially planned. And unfortunately JDBC is also much less powerful compared to ruby-oci8 library for construction of dynamic PL/SQL calls. In addition I needed to strugle with Ruby and JDBC data type mappings which differs from Ruby and ruby-oci8 data type mappings.

But finally I have completed JRuby support and released ruby-plsql gem version 0.2.0. And good news are that from usage perspective ruby-plsql behaves identically on MRI and JRuby – at least my RSpec tests are telling so.

To install this gem on JRuby execute

sudo jruby -S gem install ruby-plsql

or

sudo jgem install ruby-plsql

depending on how you have installed JRuby on your computer.

Source code of ruby-plsql is located on GitHub where you can find usage examples in RSpec tests.

As many others I also got interested in new mod_rails deployment solution for Rails applications. And when I read how to use it for development environment needs I decided to try it out.

As you probably know I am using Mac for development and using Oracle database for many Rails applications. So if you do it as well then at first you need to setup Ruby and Oracle on your Mac.

After that I installed and did setup of mod_rails according to these instructions and these additional notes.

One additional thing that I had to do was to change the user which will be used to run Apache httpd server as otherwise default www user did not see my Rails applications directories. You should do it in /etc/apache2/httpd.conf:

User yourusername
Group yourusername

And then I started to fight with the issue that ruby which was started from mod_rails could not load ruby-oci8 library as it could not find Oracle Instant Client shared library. And the reason for that was that mod_rails launched ruby with very minimal list of environment variables. E.g. as DYLD_LIBRARY_PATH environment variable was not specified then ruby-oci8 could not find Oracle Instant Client libraries.

The issue is that there is no documented way how to pass necessary environment variables to mod_rails. Unfortunately mod_rails is ignoring SetEnv settings from Apache httpd.conf file. Therefore I needed to find some workaround for the issue and finally I did the following solution.

I created executable script file /usr/local/bin/ruby_with_env:

#!/bin/bash
export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient_10_2:$DYLD_LIBRARY_PATH"
export SQLPATH=$DYLD_LIBRARY_PATH
export TNS_ADMIN="/usr/local/oracle/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"
/usr/bin/ruby $*

and then in Apache httpd.conf file I changed RailsRuby line to

RailsRuby /usr/local/bin/ruby_with_env

As a result in this way I was able to specify necessary environment variables before Ruby and Rails was started and after this change ruby-oci8 libraries were successfully loaded.

You can use this solution also on Linux hosts where you will deploy Rails applications in production.

Currently I still have issue with mod_rails that it fails to execute RMagick library methods (which is compiled with ImageMagick). I get strange errors in Apache error_log:

The process has forked and you cannot use this CoreFoundation functionality safely. You MUST exec().
Break on __THE_PROCESS_HAS_FORKED_AND_YOU_CANNOT_USE_THIS_COREFOUNDATION_FUNCTIONALITY___YOU_MUST_EXEC__() to debug.
[error] [client ::1] Premature end of script headers:

When I was running the same application with Mongrel then everything was running correctly. If anyone has any ideas what could be the reason please write some comment.

In all Ruby on Rails on Oracle projects where I am using original ActiveRecord Oracle adapter I always create some “monkey patches” of Oracle adapter to support my needs. I have written about several of these patches in this blog (see 1, 2, 3).

As such monkey patches are not easily maintainable and reusable between projects I decided to fork existing Oracle adapter and create my own ActiveRecord Oracle “enhanced” adapter where I will add all my enhancements.

Today I released first version (1.1.0) of Oracle enhanced adapter on RubyForge as well as source code is available at GitHub.

To install Oracle enhanced adapter execute (should be available soon):

sudo gem install activerecord-oracle_enhanced-adapter

To use it you need to use “oracle_enhanced” as the adapter name in database.yml configuration file instead of “oracle”. In addition I recommend to create config/initializers/oracle_enhanced.rb file where to put any adapter configuration options that you can see below.

Initial version contains the following enhancements:

• Improved perfomance of schema dump methods when used on large data dictionaries
• Added LOB writing callback for sessions stored in database (see previous post)
• Added emulate_dates_by_column_name option
  Set the option below and as a result columns with DATE in their name will be emulated as Date (and not as Time which is default for DATE columns in database)
  

  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates_by_column_name = true
  

• Added emulate_integers_by_column_name option
  Set the option below and as a result number columns with ID at the end of column always will be emulated as Fixnum (useful if in legacy database column type is specified just as NUMBER without precision information which by default is mapped to BigDecimal Ruby type)
  

  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_integers_by_column_name = true
  

• Added emulate_booleans_from_strings option
  Set the option below and as a result CHAR, VARCHAR2 columns or VARCHAR2 columns with FLAG or YN at the end of their name will be emulated as booleans (and “Y” and “N” will be used to store true and false values). This is useful for legacy databases where Rails default convention of NUMBER for boolean values cannot be used (e.g. if you are using Oracle E-Business Suite where booleans are stored as “Y” and “N”).
  

  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans_from_strings = true
  

If you need to override how date, integer or boolean columns are identified then you can redefine class methods is_date_column?, is_integer_column? and is_boolean_column? definitions in OracleEnhancedAdapter class.

Please comment if you find these enhancements useful in your projects and also what other Oracle adapter enhancements you need in your Ruby on Rails on Oracle projects.

mix.oracle.com team created opportunity to suggest and vote for sessions at Oracle OpenWorld conference. Therefore I will also try this opportunity and will suggest topic which is covered by many posts in this blog – I proposed session Using Ruby on Rails with Oracle E-Business Suite.

So if you would like me to present this topic in Oracle OpenWorld conference then please go to mix.oracle.com and vote for it!

UPDATE: New version of instructions for Snow Leopard is available here.

Introduction

We waited for it long, long time and finally it has arrived – Oracle Instant Client for Intel Mac. I was lucky to test beta version of the client already for the last couple of weeks and so far everything was working OK. Therefore as the final version is now available to everybody I am here rewriting my previous instructions on how to get Ruby working with Oracle on Mac.

Installing Oracle Instant Client for Intel Mac

At first you need to download Oracle Instant Client for Intel Mac. Download “Instant Client Package – Basic” and “Instant Client Package – SDK” and also I suggest “Instant Client Package – SQL*Plus” if you would like to have command line sqlplus utility.

Unzip downloaded archives and move it where you would like to have it – I am keeping it in /usr/local/oracle/instantclient_10_2. Then go to this directory and make symbolic links for dynamic libraries

sudo ln -s libclntsh.dylib.10.1 libclntsh.dylib
sudo ln -s libocci.dylib.10.1 libocci.dylib

Then I recommend to create and place somewhere your tnsnames.ora file where you will keep your database connections definitions – I place this file in directory /usr/local/oracle/network/admin.

Then finally you need to set up necessary environment variables – I place the following definitions in my .bash_profile script:

export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient_10_2"
export SQLPATH="/usr/local/oracle/instantclient_10_2"
export TNS_ADMIN="/usr/local/oracle/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export PATH=$PATH:$DYLD_LIBRARY_PATH

Use your path to Oracle Instant Client if it differc from /usr/local/oracle/instantclient_10_2. And as you see I also define NLS_LANG environment variable – this is necessary if your database is not in UTF8 encoding but in Ruby you want to get UTF-8 encoded strings from the database. Specifying this NLS_LANG environment variable you will force that Oracle Instant Client will do character set translation.

After these steps relaunch Terminal application (so that new environment variables are set), specify database connection in tnsnames.ora file and try if you can access your database with sqlplus from command line.

Ruby installation

If you are using Leopard then I assume that you are using preinstalled Ruby which is the simplest option. I tried to compile Ruby from sources on Mac OS X Leopard but when I compared performance then original Ruby was a little bit faster on some benchmarks and therefore I sticked with original one.

Compile and install ruby-oci8

Download the latest version of ruby-oci8 (version 1.0.0 at time of writing this post).

As Oracle Instant Client is available just for Intel i386 architecture you need to change in file /usr/lib/ruby/1.8/universal-darwin9.0/rbconfig.rb line 17 to:

'-arch i386'

Then go to directory where you extracted ruby-oci8 source and execute ruby-oci8 standard installation sequence:

ruby setup.rb config
make
sudo make install

After that you can change back file /usr/lib/ruby/1.8/universal-darwin9.0/rbconfig.rb line 17 to:

'-arch ppc -arch i386'

Now try

ruby -r oci8 -e "OCI8.new('scott', 'tiger','orcl').exec('select * from emp') do |r| puts r.join(','); end"

or similar to verify that you can access Oracle database from ruby.

That’s it! Please write in comments if something is not working according to these instructions.

I gave short presentation about “Using Ruby with Oracle” at European Ruby conference Euruko 2008. You can download presentation slides at their site. My collegue took a video of my presentation so probably after some time I will post it as well :)

Either because of this presentation or maybe just because more people are interested in Ruby on Oracle the number of visits to this blog is fast growing during the last days. Which makes me more motivated to do more investigations in Ruby and Oracle area.

One area of further research could be standardization of different ActiveRecord Oracle adapter patches – otherwise now I have different patches in each project and this becomes quite hard to manage.

In several projects I have used Ruby and Rails to access legacy Oracle databases which have both tables with data as well as PL/SQL packages with lot of existing business logic. Sometimes it is easier just to redo business logic in Ruby but sometimes you need to reuse existing PL/SQL packages and procedures.

Let’s use this simple PL/SQL function as an example:

CREATE OR REPLACE FUNCTION test_uppercase
  ( p_string VARCHAR2 )
  RETURN VARCHAR2
IS
BEGIN
  RETURN UPPER(p_string);
END test_uppercase;

If you are using ruby-oci8 library to connect to Oracle then you can call this PL/SQL procedure from Ruby in the following way (more details can be found in ruby-oci8 documentation):

conn = OCI8.new("hr","hr","xe")
cursor = conn.parse <<-EOS
BEGIN
  :return := test_uppercase(:p_string);
END;
EOS
cursor.bind_param(':p_string',"xxx",String)
cursor.bind_param(':return',nil,String,4000)
cursor.exec
puts cursor[':return']
cursor.close

This does not look like Ruby-style as it is too long and complex code which just calls one simple PL/SQL function :(

Wouldn’t it be much nicer if you could get the same result with the following code?

plsql.connection = OCI8.new("hr","hr","xe")
puts plsql.test_uppercase('xxx')

This idea served as inspiration to create ruby-plsql gem which would provide such nice Ruby API to access existing PL/SQL procedures and functions. Here are other examples how you can use it.

Call procedure with named parameters:

plsql.test_uppercase(:p_string => 'xxx')

Call procedure with specified schema:

plsql.hr.test_uppercase('xxx')

Call procedure from specified package in specified schema:

plsql.hr.test_package.test_uppercase('xxx')

Call procedure with output arguments:

plsql.test_copy("abc", nil, nil) # returns output arguments { :p_to => "abc", :p_to_double => "abcabc" }

To install this gem execute

sudo gem install ruby-plsql

As I mentioned before it also requires ruby-oci8 library to access Oracle. I have plans to create JRuby / JDBC support in the future versions of this gem.

Current limitation is that this API support just NUMBER, VARCHAR2, DATE and TIMESTAMP types for PL/SQL procedures which are dynamically mapped to Ruby Fixnum/Bignum/Float, String, DateTime and Time types.

If you find this gem interesting then please let me know in comments what additional features you would like to have for it.

When using Ruby on Rails on Oracle I noticed that my database migration tasks are much slower than on MySQL.

I just found the cause for that – rake db:schema:dump task was taking very long time on my Oracle databases (and this task is executed at the end of rake db:migrate). As a result of this task Oracle Rails adapter is executing

SELECT LOWER(table_name) FROM user_tables

statement. If your database contains a lot of other schemas with a lot of tables (as in my case I deploy Rails user schema in Oracle E-Business Suite database) then this statement is quite slow (it took more than 10 seconds in my case).

As all Rails tables are located in Rails user schema then you can get the same result with

SELECT LOWER(table_name) FROM all_tables WHERE owner = SYS_CONTEXT('userenv','session_user')

which now executes much faster than original statement.

So you can make the following patch for Oracle Rails adapter to make this change

module ActiveRecord
  module ConnectionAdapters
    class OracleAdapter
      def tables(name = nil)
        select_all("select lower(table_name) from all_tables where owner = sys_context('userenv','session_user')").inject([]) do | tabs, t |
          tabs << t.to_a.first.last
        end
      end
    end
  end
end

and include it in environment.rb file or in some separate patch file which you load at Rails startup.

P.S. I have published all my patches for Oracle Rails 2.0 adapter. You can download this file, place it in Rails application lib directory and then just include

require "oracle_adapter_20_patch"

in your environment.rb file. Look at comments in this file to find out what each patch is doing.

As I started to explore Rails 2.0 I tried to migrate one application to Rails 2.0 which is using Oracle as a database. Here are some initial tips for Rails 2.0 on Oracle that I found out.

Oracle adapter is no more included in Rails 2.0 so you need to install it separately. It is also not yet placed on gems.rubyforge.org therefore you need to install it with:

sudo gem install activerecord-oracle-adapter --source http://gems.rubyonrails.org

The next issue that you will get is error message “select_rows is an abstract method”. You can find more information about it in this ticket. As suggested I fixed this issue with the following Oracle adapter patch that I call from anvironment.rb file:

module ActiveRecord
  module ConnectionAdapters
    class OracleAdapter
      def select_rows(sql, name = nil)
        result = select(sql, name)
        result.map{ |v| v.values}
      end
    end
  end
end

And then I faced very strange behaviour that my Rails application was not working with database session store – no session data was saved. When I changed session store to cookies then everything worked fine.

When I continued investigation I found out that the issue was that for each new session new row was created in “sessions” table but no session data was saved in “data” column. As “data” column is text field which translates to CLOB data type in Oracle then it is not changed in Oracle adapter by INSERT or UPDATE statements but with special “write_lobs” after_save callback (this is done so because in Oracle there is limitation that literal constants in SQL statements cannot exceed 4000 characters and therefore such hack with after_save callback is necessary). And then I found that class CGI::Session::ActiveRecordStore::Session (which is responsible for database session store) does not have this write_lobs after_save filter. Why so?

As I understand now in Rails 2.0 ActiveRecord class definition sequence has changed – now at first CGI::Session::ActiveRecordStore::Session class is defined which inherits from ActiveRecord::Base and only afterwards OracleAdapter is loaded which adds write_lobs callback to ActiveRecord::Base but at this point it is not adding this callback to already defined Session class. As in Rails 1.2 OracleAdapter was loaded together with ActiveRecord and before Session class definition then there was no such issue.

So currently I solved this issue with simple patch in environment.rb file:

class CGI::Session::ActiveRecordStore::Session 
  after_save :write_lobs
end

Of course it would be nicer to force that OracleAdapter is loaded before CGI::Session::ActiveRecordStore::Session definition (when ActionPack is loaded). If somebody knows how to do that please write a comment :)

I am using Ruby on Rails to publish data from existing “legacy” application on Oracle database which already have existing complex data model. I am defining additional database views on existing legacy data to which I grant select rights to Rails schema. And I am using Rails conventions when defining these views – view names as pluralized / underscored version of Rails model name, ID column as primary key and %_ID columns as foreign keys.

Typically this works quite well and I can use Rails find methods to automatically generate SQL SELECTs from these views. But for some legacy data I got the problem with Oracle number type mapping to Ruby integer type.

Rails standard convention for database NUMBER type mapping is the following:

• NUMBER with specified scale and without precision (e.g. NUMBER) is mapped to :integer
• NUMBER with specified scale and with precision (e.g. NUMBER) is mapped to :decimal
• NUMBER without scale and precision (just NUMBER) is mapped to :decimal

If primary keys and foreign keys in legacy tables are defined as e.g. NUMBER then everything is OK and they will be mapped to :integer in Rails. But if primary keys or foreign keys in legacy tables are defined as NUMBER then they will be mappec to :decimal in Rails.

And what happens if e.g. primary key is mapped to :decimal in Rails? Then, for example, you get that customer.id is equal to “123.0” and you get ugly URLs like “/customers/123.0”.

One workaround is to use customer.id.to_i everywhere but it is quite annoying. Therefore I created patch for Oracle adapter (this is tested with Rails 1.2.3) which always sets data type as :integer for all primary keys (column name ID) and foreign keys (column name like %_ID). This includes also date columns patch that I wrote about previously.

module ActiveRecord::ConnectionAdapters
  class OracleColumn
    def simplified_type(field_type)
      return :boolean if OracleAdapter.emulate_booleans && field_type == 'NUMBER(1)'
      case self.name
        # RSI: treat columns which end with 'date' as ruby date columns
        when /date$/i then :date
        # RSI: removed 'date' from regex
        when /time/i then :datetime
        # RSI: treat id columns (primary key) as integer
        when /^id$/i then :integer
        # RSI: treat _id columns (foreign key) as integer
        when /_id$/i then :integer
        else super
      end
    end
  end

  # RSI: patch to change selected results NUMBER to integer for primary and foreign keys
  class OracleAdapter
    def select(sql, name = nil)
      cursor = execute(sql, name)
      cols = cursor.get_col_names.map { |x| oracle_downcase(x) }
      rows = []

      while row = cursor.fetch
        hash = Hash.new

        cols.each_with_index do |col, i|
          hash[col] =
            case row[i]
            when OCI8::LOB
              name == 'Writable Large Object' ? row[i]: row[i].read
            when OraDate
              (row[i].hour == 0 and row[i].minute == 0 and row[i].second == 0) ?
              row[i].to_date : row[i].to_time
            else row[i]
            end unless col == 'raw_rnum_'
          # RSI: patch - convert to integer if column is ID or ends with _ID
          hash[col] = hash[col].to_i if (col =~ /^id$/i || col =~ /_id$/i) && hash[col]
        end

        rows << hash
      end

      rows
    ensure
      cursor.close if cursor
    end
  end  
end

I have not yet verified this with Rails 2.0. And probably I will collect all my Oracle adapter patches and will release it as plugin. Is anybody interested in this?

I was developing Ruby on Rails application that access data from existing Oracle E-Business Suite application and I wanted to add to this application possibility for users to authenticate themselves by their existing Oracle E-Business Suite user names and passwords.

Oracle is not publicly providing algorythms which are used for user passwords encryption (which are stored in FND_USER table) and therefore some googling was necessary. I was lucky to find Jira eBusiness Suite Authenticator with full source code in Java which also included all Oracle E-Business Suite password encryption / decryption functions. Actually it seems that the >1000 lines source code is decompiled from Oracle Java classes as there are no comments and variable names don’t tell anything.

But as I did not have any better source material I started Ruby rewrite of this code and happily managed to finish it in couple of days. As a result I created Oracle EBS Authentication plugin and you can find out in README file how to install it.

Usage of this plugin is quite simple – here is an example:

database_name = ActiveRecord::Base.connection.current_database
authenticator = OracleEbsAuthentication::Authenticator.new(database_name)
if authenticator.validate_user_password(login, password)
  # user authenticated
  if authenticator.user_reponsibilities.include? "System Administrator"
    # user has System Administrator role
  end
  # ...
else
  # authentication failed
end

BTW Oracle EBS password encryption approach has quite a lot of weaknesses. So if you provide SQL*Net access to your Oracle EBS database then anyone with valid Oracle EBS user name and password can decrypt APPS database user password and also can decrypt passwords of all other users. You can have a deeper look in plugin source code to understand how it can be done :)

If you are using Rails 1.2.3 with Oracle database then you might find that text attributes (which map to Oracle CLOB data type) get invalid default values – e.g. you might find that when you create new record it will get “empty_clob()” as default text attribute value.

I found out that this issue is corrected in current edge Rails. As I still primarily use Rails 1.2.3 I created the following patch according to the changes that are done in edge Rails. You can put it in environment.rb file or better put into a separate file and require it in environment.rb file.

# RSI: text defaults handling from http://dev.rubyonrails.org/ticket/7344 & http://dev.rubyonrails.org/changeset/6090
module ActiveRecord::ConnectionAdapters
  class OracleAdapter
    def quote(value, column = nil) #:nodoc:
      # RSI: patched
      if value && column && [:text, :binary].include?(column.type)
        %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()}
      else
        super
      end
    end

    def columns(table_name, name = nil) #:nodoc:
      (owner, table_name) = @connection.describe(table_name)
      table_cols = <<-SQL
        select column_name as name, data_type as sql_type, data_default, nullable,
               decode(data_type, 'NUMBER', data_precision,
                                 'FLOAT', data_precision,
                                 'VARCHAR2', data_length,
                                  null) as limit,
               decode(data_type, 'NUMBER', data_scale, null) as scale
          from all_tab_columns
         where owner      = '#{owner}'
           and table_name = '#{table_name}'
         order by column_id
      SQL
      select_all(table_cols, name).map do |row|
        limit, scale = row['limit'], row['scale']
        if limit || scale
          row['sql_type'] << "(#{(limit || 38).to_i}" + ((scale = scale.to_i) > 0 ? ",#{scale})" : ")")
        end
        # clean up odd default spacing from Oracle
        if row['data_default']
          row['data_default'].sub!(/^(.*?)\s*$/, '\1')
          row['data_default'].sub!(/^'(.*)'$/, '\1')
          # RSI: patched
          row['data_default'] = nil if row['data_default'] =~ /^(null|empty_[bc]lob\(\))$/i
        end
        OracleColumn.new(oracle_downcase(row['name']),
                         row['data_default'],
                         row['sql_type'],
                         row['nullable'] == 'Y')
      end
    end

    # RSI: added
    def add_column_options!(sql, options) #:nodoc: 
      # handle case of defaults for CLOB columns, which would otherwise get "quoted" incorrectly 
      if options_include_default?(options) && (column = options[:column]) && column.type == :text 
        sql << " DEFAULT #{quote(options.delete(:default))}"  
      end 
      super
    end

  end
end

UPDATE: New version of this instruction for Intel Macs with Leopard is available here.
New version of instructions for Snow Leopard is available here.

Introduction

I have been using Oracle technologies for many years but just some time ago discovered Ruby and Rails. As I had ideas how to make Ruby on Rails frontends for existing Oracle based systems I started to explore how to use Ruby on Rails together with Oracle databases.

Just recently I switched from PC notebook to MacBook Pro and unfortunately found out that Oracle Instant Client is not yet released for Intel Macs. As there is no promise when it will be released and as I could not wait for that I decided to make old PowerPC version of Oracle Instant Client to run on Intel Mac. As I didn’t find any good description how to do that I decided to write description of it by myself – hopefully it will help others.
h3. Universal or “fat” binary Ruby

The first thing is that you need to get “universal” or “fat” binary installation of Ruby – it means that it contains both Intel and PowerPC (PPC) binary code. You will need PPC version of Ruby when you will work with Oracle and you will need Intel version of Ruby when you will do other things. PPC code is running in emulation mode on Intel processors and therfore is slower as well as there are some compatibility issues with some other libraries (I will mention later what issues I found out).

It is possible to get and install precompiled Ruby either from MacPorts or using Ruby One-Click Installer. But I prefer to compile Ruby from source code as it gives me more control what is installed and where. I used Hivelogic guide to compile Ruby from source code.

Here are my additional notes what I changed to Hivelogic guide to make “fat” binaries.

Installing “readline”

At first you need to install “readline” shared libraries. At first I tried to make them from “readline” library source code but I always got just Intel binaries. Therefore I found and downloaded “readline” library installer package with universal binaries and installed it. If you previously installed Intel binary of “readline” library then it is better to restart Mac to ensure that new dynamic library will be loaded.

Compiling Ruby

Next you need to download and extract Ruby 1.8.6 source code.

Before running “configure” command I made the following change in “configure” file:

# Choose a default set of architectures based upon platform.
case "$target_os" in
darwin*)
    TARGET_ARCHS="*ppc i386*"
    ;;

You can specify this also on command line but this change ensures that you will not forget it :) Then you need to run “configure” script with additional parameter at the end:

./configure --prefix=/usr/local --enable-pthread --with-readline-dir=/usr/local \
--enable-shared --enable-fat-binary

After running “make” hopefully you will get everything compiled and you should get “fat” ruby binary. You can verify it with the following command and should see the following result:

$ file ruby
ruby: Mach-O universal binary with 2 architectures
ruby (for architecture ppc):    Mach-O executable ppc
ruby (for architecture i386):   Mach-O executable i386

If you do not see that ruby binary contains both ppc and i386 executables then something went wrong. If it is OK then you can do “sudo make install” to install binaries in target directories.

Make PPC and “fat” versions of Ruby

As you will need to be able to force to run PPC version of Ruby later then we need to extract PPC executable in a separate file and store original “fat” binary in another file:

ditto -arch ppc /usr/local/bin/ruby /usr/local/bin/ruby_ppc
mv /usr/local/bin/ruby /usr/local/bin/ruby_fat

Then I recommend to create simple scripts that will help you to switch between “fat” and PPC versions of Ruby:

ppc_ruby.sh:

#!/bin/bash
sudo ln -fs /usr/local/bin/ruby_ppc /usr/local/bin/ruby

fat_ruby.sh:

#!/bin/bash
sudo ln -fs /usr/local/bin/ruby_fat /usr/local/bin/ruby

So when you need to have PPC version of Ruby then run “ppc_ruby.sh” script and when you need “fat” version (which will actually run Intel binary) then run “fat_ruby.sh” script.

Install Oracle Instant Client for PPC

Install Oracle Instant Client according to the following description.

Compile and install ruby-oci8

I used the following description as a basis but some additional changes were needed for ruby-oci8-1.0.0-rc3 compilation.

Open ruby-oci8 README file and find section “=== Intel Mac” where are described what to do to compile ruby-oci8 on Intel Mac.

Before running any ruby scripts you need to run ppc_ruby.sh script to switch to PPC binary.

If you compiled “fat” Ruby from source code then you need to modify file /usr/local/lib/ruby/1.8/fat-darwin8.10.1/rbconfig.rb according to README file (make backup of file before modifications). If you installed Ruby from binary distribution then find where is located your rbconfig.rb file.

• find lines with CONFIG[“CFLAGS”] and CONFIG[“LDFLAGS”] and CONFIG[“ARCH_FLAG”]
• remove “-arch i386” if present (was not present in my case)
• and add “-arch ppc” to all of these lines.

Make and install ruby-oci8 and if it finishes successfully then restore rbconfig.rb file from backup.

Try

ruby -r oci8 -e "OCI8.new('scott', 'tiger','orcl').exec('select * from emp') do |r| puts r.join(','); end"

or similar to verify that you can access Oracle database from ruby.

Congratulations! You have managed to connect to Oracle DB from Ruby on Intel Mac! If you got some issues at some point then please write it in comments and I will try to help with that.

Post installation notes

If you previously installed (or if you will install in future) any Ruby gems with native extensions (e.g. Mongrel or MySQL C API) then you need to reinstall these gems with “fat” Ruby (i.e. running “fat_ruby.sh” script and then “sudo gem install”). It will ensure that all gems will also have “fat” native extensions so that you can run them both in PPC Ruby and Intel Ruby.

Known issues with PPC Ruby

So far I have got the following issues with running PPC version of Ruby:

• Capistrano is failing to make SSH connection when running in PPC Ruby (I was testing with new Capistrano 2.0). Therefore you need always to switch to “fat” Ruby before running “cap deploy”.
• As I also wanted that MySQL is running under PPC I got some issues with MySQL native extensions compilation – I solved them using the following description.
• ruby-ldap connections were failing when running in PPC Ruby within Ruby on Rails. I managed to fix it by putting “require ldap” as a first line in config/environment.rb file.

As you probably have noticed Oracle has just one DATE type for table columns which is supposed both for storing just dates and dates with time. From the other side Ruby has different classes Date and Time. If you are using Rails then Rails tries to guess from the database column types what are the classes for corresponding object attributes in Ruby. And as both dates and dates with time appear as DATE columns Rails has difficulties to guess whether it should be Ruby Date or Ruby Time.

Current Rails Oracle adapter has the following workaround implemented:

# * Oracle uses DATE or TIMESTAMP datatypes for both dates and times.
#   Consequently some hacks are employed to map data back to Date or Time
#   in Ruby. If the column_name ends in _time it's created as a Ruby Time.
#   Else if the hours/minutes/seconds are 0, I make it a Ruby Date. Else
#   it's a Ruby Time. This is a bit nasty - but if you use Duck Typing
#   you'll probably not care very much. In 9i and up it's tempting to
#   map DATE to Date and TIMESTAMP to Time, but too many databases use
#   DATE for both. Timezones and sub-second precision on timestamps are
#   not supported.

This workaround is problematic if you have date columns which can be NULL. In this case Rails cannot determine if this is date or datetime column. And if you use standard scaffolds or, for example, ActiveScaffold, it will use datetime_select helper and not date_select helper for this column – which means that you will be asked to specify also time for new dates.

Therefore, as I typically name all date columns with _DATE at the end, I created a patch which makes all such columns to be handled as Ruby Date attributes:

# RSI: OracleAdapter patch - treat columns which end with 'date' as ruby date type
module ActiveRecord::ConnectionAdapters
  class OracleColumn
    def simplified_type(field_type)
      return :boolean if OracleAdapter.emulate_booleans && field_type == 'NUMBER(1)'
      case self.name
        # RSI: treat columns which end with 'date' as ruby date columns
        when /date$/i then :date
        # RSI: removed 'date' from regex
        when /time/i then :datetime
        else super
      end
    end
  end
end

Include this in environment.rb file or put it into separate file in e.g. lib directory and require it in environment.rb file.