ruby-plsql 0.4.1 - support for package variables, views, dbms_output and more

Raimonds Simanovskis - Sun, 2010-01-03 16:00

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:

  varchar2_variable VARCHAR2(50);
  number_variable NUMBER(15,2);
  string_constant CONSTANT  VARCHAR2(10) := 'constant';
  integer_constant CONSTANT INTEGER := 1;

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 :)


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

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')

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 ='debug.log', 'w')
STANDARD package procedures

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

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.

Categories: Development

Oracle PL/SQL unit testing with Ruby

Raimonds Simanovskis - Thu, 2009-11-26 16:00
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:

   string_in   IN   VARCHAR2,
   start_in    IN   INTEGER,
   end_in      IN   INTEGER
   l_start PLS_INTEGER := start_in;
   IF l_start = 0
      l_start := 1;
   END IF;
   RETURN (SUBSTR (string_in, l_start, end_in - l_start + 1));

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'
  it "should be correct with zero start value" do
    plsql.betwnstr('abcdefg', 0, 5).should == 'abcde'
  it "should be correct with way big end value" do
    plsql.betwnstr('abcdefg', 5, 500).should == 'efg'
  it "should be correct with NULL string" do
    plsql.betwnstr(nil, 5, 500).should be_nil

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:

 PROCEDURE award_bonus (
  emp_id NUMBER, sales_amt NUMBER) AS
  commission    REAL;
  comm_missing  EXCEPTION;
  SELECT commission_pct INTO commission
    FROM employees2
      WHERE employee_id = emp_id;
  IF commission IS NULL THEN
    RAISE comm_missing;
    UPDATE employees2
      SET salary = NVL(salary,0) + sales_amt*commission
        WHERE employee_id = emp_id;
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

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 => '',
      :hire_date =>,
      :job_id =>[:job_id],
      :commission_pct => nil,
      :salary => nil
    plsql.employees2.insert employee
    get_employee employee[:employee_id]
  # Select employee by primary key
  def get_employee(employee_id)
    plsql.employees2.first :employee_id => employee_id

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/)
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

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

Categories: Development

More Oracle data types supported by ruby-plsql gem

Raimonds Simanovskis - Tue, 2009-11-24 16:00

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 p_employee.first_name || ' ' || p_employee.last_name;

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:

  street    VARCHAR2(50),
  city      VARCHAR2(50),
  country   VARCHAR2(50)
  type            VARCHAR2(10),
  phone_number    VARCHAR2(50)
  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 p_employee.first_name || ' ' || p_employee.last_name;

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 FUNCTION test_sum (p_numbers IN t_numbers)
  l_sum   NUMBER(15) := 0;
  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;
    RETURN l_sum;

And from Ruby side:

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

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

  l_cursor  SYS_REFCURSOR;
  OPEN l_cursor FOR
  SELECT * FROM test_employees ORDER BY employee_id;
  RETURN l_cursor;

can be called from Ruby in the following way:

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

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:

  RETURN test_employees%ROWTYPE
  l_record  test_employees%ROWTYPE;
  FETCH p_cursor INTO l_record;
  RETURN l_record;

which can be called from Ruby

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

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.


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:

  ( p_boolean BOOLEAN )
  RETURN p_boolean;
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 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 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 records
plsql.employees.update(:first_name => 'Second', :where => {:employee_id => 1})
                      # UPDATE employees SET first_name = 'Second' WHERE employee_id = 1
# 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 :first, "SELECT ..."
# or :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


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.


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 :)

Categories: Development

Oracle Open World 2009 Summary

Inside the Oracle Optimizer - Wed, 2009-10-28 13:09
We had a great time talking to our users at Open World 2009 both at our Demogrounds booth and at our two sessions. We received a lot of interesting questions during the Optimizer Roundtable discussion, but we did not get to answer all of them due to time constraints. We plan to address the questions we received (both answered and unanswered) in future blog posts... so stay tuned. If you didn't get to attend the discussion, but have a question about the Optimizer, submit it through the email link above.

For those of you who did not get a chance to stop by our Demogrounds booth, here's a recap of the new features that we talked about. Many of the topics have already been covered in earlier blog posts.
These topics are focused on well-known pain points from earlier versions of Oracle. But we also have plenty of new optimizations in Oracle 11gR1 and 11gR2. Stay tuned for details about some of our new optimizations.

Categories: DBA Blogs, Development

Notes from Oracle OpenWorld 2009

Raimonds Simanovskis - Mon, 2009-10-19 16:00

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).


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

Categories: Development

Open World Recap and New White papers

Inside the Oracle Optimizer - Fri, 2009-10-09 14:32
The Optimizer group has two session and a demo station in the Database campground at this year's Oracle Open World. We will give a technical presentation on What to Expect from the Oracle Optimizer When Upgrading to Oracle Database 11g and host an Oracle Optimizer Roundtable.

The technical session, which is on Tuesday Oct 13 at 2:30 pm, gives step by step instructions on how to use the new 11g features to ensure your upgrade goes smoothly and without any SQL plan regressions. This session is based on our latest white papers, Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer and SQL Plan Management in Oracle Database 11g.

The roundtable, which is on Thursday Oct. 15th at 10:30 am, will give you a first hand opportunity to pose you burning Optimizer and statistics questions directly to a panel of our leading Optimizer developers. In fact if you plan to attend the roundtable and already know what questions you would like to ask, then please send them to us via email and we will be sure to include them. Other wise, you can hand in your questions at our demo station at any stage during the week, or as you enter the actual session. Just be sure to write your questions in clear block capitals!

We look forward to see you all at Open world.

Categories: DBA Blogs, Development

New features in ActiveRecord Oracle enhanced adapter version 1.2.2

Raimonds Simanovskis - Sun, 2009-09-27 16:00

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.


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 :)
  • 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.

Categories: Development

How to install Oracle Database 10g on Mac OS X Snow Leopard

Raimonds Simanovskis - Sun, 2009-09-13 16:00

sl_oracle.jpgOracle 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:


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
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 installation archive and place it somewhere and unzip it:

mkdir Install
cd Install
# download to this directory
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

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

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:


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/

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 ORACLE_SID=orcl

and execute it

. ~/.bash_profile

Now you need to modify $ORACLE_HOME/jdk/bin/java script and change -Xbootclasspath... to -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 :)

curl -O
chmod ug+s oracle

(If you installed Oracle Enterprise Edition then please substitute with

Now you can run Network Configuration Assistant


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


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_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)
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

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

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (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:


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

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

# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for the installation

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


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

  ConsoleMessage "Starting Oracle Databases"
  su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"

  ConsoleMessage "Stopping Oracle Databases"
  su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"


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/ 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:

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

Categories: Development

How to setup Ruby and Oracle Instant Client on Snow Leopard

Raimonds Simanovskis - Sat, 2009-09-05 16:00

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"

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';'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.

Categories: Development

Out Now!! Application Express 3.2.1

Anthony Rayner - Wed, 2009-08-26 04:28
The Oracle Application Express 3.2.1 patch set is now available for download and provides not only fixes to the following bugs, but also some additional functionality and considerations as summarised by Joel and detailed in the patch set notes.

You can get hold of it by either:
  • Downloading the full version from OTN.

  • Download the patch set 8548651 from METALINK.

If you're upgrading from any APEX version pre-3.2, then you'll need to use the full OTN release. Otherwise if you're upgrading from 3.2, then you only need the patch set.

Also in this patch set, we have included an additional documentation chapter, entitled Accessibility in Oracle Application Express. This aims to provide information for users who are accessing Oracle Application Express utilizing only a keyboard or Freedom Scientific's screen reader JAWS. It details the current accessibility issues in APEX and shows workarounds where they are possible. (We hope to address a number of these issues in APEX 4.0.)

I would be very interested to hear from anyone who uses APEX with keyboard only, screen reader or other assistive technology to get feedback on how we can hopefully get better at being accessible to our users with disabilities. Also if you use APEX to build applications that have strict accessibility requirements and have feedback on your experiences then I would love to hear from you also.

Please drop me an email at the email address in my profile if you would like to talk about this.


Categories: Development

Oracle JHeadstart 11.1.1 Technical Preview 2 Now Available

JHeadstart - Tue, 2009-08-11 23:17

Oracle JHeadstart 11.1.1 Technical Preview 2 is now available for download (build
This release is compatible with JDeveloper 11 release 1 (, as well as with the two JDeveloper Boxer releases ( and
Customers who own a JHeadstart supplement option license can download it from the Consulting Supplement Option portal.

An evaluation version of JHeadstart 11.1.1 is not yet available. We plan to make an evaluation version available in the fall of calendar year 2009.

In addition to many small enhancements, the following features have been added to the second technical preview:

  • Support for Stretching Layouts: JHeadstart now fully supports stretching options for ADF Faces Rich Client components.
    Through a new group-level property Enable Stretching, you can stretch the generate group content to fill the available space. Whether or not stretching
    will actually occur depends on some additional conditions that must be met. See the help description of this new group property in the application definition editor, and the
    JHeadstart Developer's guide for more info.

  • New Table Overflow Styles: The Table Overflow Style group property has two new allowable values: 'Right with Splitter' and 'Below with Splitter', which are
    useful settings when you want to generate multiple panes with stretching layouts that adjust based on the position of the splitter.

  • New Allowable Values for Same Page Display Position: The Same Page Display Position group property has two new allowable values: 'At the Right of Parent Group with Splitter'
    and 'Below Parent Group with Splitter', which are useful settings when you want to generate multiple panes with stretching group layouts that adjust
    based on the position of the splitter.
  • New Allowable Values for Region Container Layout: The Layout property of a region container has two new allowable values: 'Horizontal with Splitter'
    and 'Vertical with Splitter', which are useful settings when you want to generate multiple panes with stretching region layouts that adjust
    based on the position of the splitter.
  • Ability to Add Bindings to PageDefinition using Velocity Templates: It is now possible to add bindings and iterators to the page definition using
    custom velocity templates. Here are some examples:


    The custom binding macro simply contains the XML you want to add to the page definition. This macro can be defined within the same
    velocity template.
  • Flex items: The 10.1.3 functionality of Flex Items (items added dynamically at runtime) is now re-implemented for release 11. The region that
    contains the flex items is now a reusable task flow, providing you with more flexibility and re-use of code.

  • File up/download: File upload, file download and display of images is now supported in release 11. It supports Intermedia (ORDSYS) column types, as well
    as BLOB column types

  • XML Reformatting: There is a new application-level property Reformat all XML Files which will reformat all generated XML files (except for the page definitions),
    giving you more readable and consistent files accross your application.

  • Upgrading from 10.1.3: When opening a 10.1.3 application definition file, JHeadstart will migrate this to release 11 upon your approval.

Click here for a list of features added in the first technical preview:

For a complete list of all existing features in JHeadstart 10.1.3, use this link.

Categories: Development

Understanding DBMS_STATS.SET_*_PREFS procedures

Inside the Oracle Optimizer - Tue, 2009-08-11 21:25
In previous Database releases you had to use the DBMS_STATS.SET_PARM procedure to change the default value for the parameters used by the DBMS_STATS.GATHER_*_STATS procedures. The scope of any changes that were made was all subsequent operations. In Oracle Database 11g, the DBMS_STATS.SET_PARM procedure has been deprecated and it has been replaced with a set of procedures that allow you to set a preference for each parameter at a table, schema, database, and Global level. These new procedures are called DBMS_STATS.SET_*_PREFS and offer a much finer granularity of control.

However there has been some confusion around which procedure you should use when and what the hierarchy is among these procedures. In this post we hope to clear up the confusion. Lets start by looking at the list of parameters you can change using the DBMS_STAT.SET_*_PREFS procedures.


As mentioned above there are four DBMS_STATS.SET_*_PREFS procedures.





The DBMS_STATS.SET_TABLE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for the specified table only.

The DBMS_STATS.SET_SCHEMA_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the existing objects in the specified schema. This procedure actually calls DBMS_STATS.SET_TABLE_PREFS for each of the tables in the specified schema. Since it uses DBMS_STATS.SET_TABLE_PREFS calling this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF values for all parameters.

The DBMS_STATS.SET_DATABASE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the user defined schemas in the database. This procedure actually calls DBMS_STATS.SET_TABLE_PREFS for each of the tables in each of the user defined schemas. Since it uses DBMS_STATS.SET_TABLE_PREFS this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF values for all parameters. It is also possible to include the Oracle owned schemas (sys, system, etc) by setting the ADD_SYS parameter to TRUE.

The DBMS_STATS.SET_GLOBAL_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless there is a table preference set or the parameter is explicitly set in the DBMS_STATS.GATHER_*_STATS command. Changes made by this procedure will affect any new objects created after it has been run as new objects will pick up the GLOBAL_PREF values for all parameters.

With GLOBAL_PREFS it is also possible to set a default value for one additional parameter, called AUTOSTAT_TARGET. This additional parameter controls what objects the automatic statistic gathering job (that runs in the nightly maintenance window) will look after. The possible values for this parameter are ALL,ORACLE, and AUTO. ALL means the automatic statistics gathering job will gather statistics on all objects in the database. ORACLE means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, sytem, etc) Finally AUTO (the default) means Oracle will decide what objects to gather statistics on. Currently AUTO and ALL behave the same.

In summary, DBMS_STATS obeys the following hierarchy for parameter values, parameters values set in the DBMS_STAT.GATHER*_STATS command over rules everything. If the parameter has not been set in the command we check for a table level preference. If there is no table preference set we use the global preference.
Categories: DBA Blogs, Development

Will the Optimizer Team be at Oracle Open World 2009?

Inside the Oracle Optimizer - Thu, 2009-07-23 20:18
With only two and a half months to go until Oracle Open World in San Francisco, October 11-15th, we have gotten several requests asking if we plan to present any session at the conference.

We have two session and a demo station in the Database campground at this year's show. We will give a technical presentation on What to Expect from the Oracle Optimizer When Upgrading to Oracle Database 11g and the Oracle Optimizer Roundtable.

The technical session, which is on Tuesday Oct 13 at 2:30 pm, gives step by step instructions and detailed examples of how to use the new 11g features to ensure your upgrade goes smoothly and without any SQL plan regressions.

The roundtable, which is on Thursday Oct. 15th at 10:30 am, will give you a first hand opportunity to pose you burning Optimizer and statistics questions directly to a panel of our leading Optimizer developers. In fact if you plan to attend the roundtable and already know what questions you would like to ask, then please send them to us via email and we will be sure to include them. Other wise, you can hand in your questions at our demo station at any stage during the week, or as you enter the actual session. Just be sure to write your questions in clear block capitals!

We look forward to seeing you all at Oracle Open World.

Categories: DBA Blogs, Development

Initial version of DataMapper Oracle adapter

Raimonds Simanovskis - Mon, 2009-07-20 16:00

datamapper.jpgWhat 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

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
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"  

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"
  repository(:default) do
    property :id, Serial

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://
cd extlib
git checkout -b next --track origin/next
rake install
cd ..
git clone 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://
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
p = Post.create(:title=>"Title")

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://
cd dm-more
git checkout -b next --track origin/next
cd dm-some-other-gem
rake install

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.

Categories: Development

2 members JHeadstart Team in Oracle Innovation Showcase

JHeadstart - Thu, 2009-07-16 00:07

We are honored that 2 of our JHeadstart Team members are included in the Conversations with Oracle Innovators of the Oracle Innovation Showcase.

Full credit to Steven, who is the driving power behind the JHeadstart innovations!

Categories: Development

OTNs APEX Developer Competition 2009

Anthony Rayner - Wed, 2009-07-01 05:22
Are you the...

  ...travelling type?  Fancy winning a free ticket for Oracle OpenWorld in San Francisco (October 11 - 15) to meet with like minded APEX enthusiasts and learn more about APEX and other Oracle technology?

Or maybe more the...

  ...bragging type?  How would the words 'Oracle Application Express Developer Competition Winner 2009' look on your CV? It does have a certain ring to it, don't you think?

Or even the...

  ...academic type?   What about the prospect of furthering your understanding of APEX by paging through your winning copy of 'Pro Oracle Application Express'?

Whatever your reasons, enter the OTN 'Oracle Application Express Developer Competition 2009' by submitting an APEX application that stands out from the crowd and you could be in with the opportunity of winning one of these great prizes or accolades!

For more information, including submission guidelines, all important judging criteria and registration details, please visit the OTN page and David Peake's related post. Entries close 24 August, 2009.

Good luck!
Categories: Development

Approaches to "UPSERT"

Kenneth Downs - Mon, 2009-06-29 20:33

This week in the Database Programmer we look at something called an "UPSERT", the strange trick where an insert command may magically convert itself into an update if a row already exists with the provided key. This trick is very useful in a variety of cases. This week we will see its basic use, and next week we will see how the same idea can be used to materialize summary tables efficiently.


The idea behind an UPSERT is simple. The client issues an INSERT command. If a row already exists with the given primary key, then instead of throwing a key violation error, it takes the non-key values and updates the row.

This is one of those strange (and very unusual) cases where MySQL actually supports something you will not find in all of the other more mature databases. So if you are using MySQL, you do not need to do anything special to make an UPSERT. You just add the term "ON DUPLICATE KEY UPDATE" to the INSERT statement:

insert into table (a,c,b) values (1,2,3)
    on duplicate key update
     b = 2,
     c = 3

The MySQL command gives you the flexibility to specify different operation on UPDATE versus INSERT, but with that flexibility comes the requirement that the UPDATE clause completely restates the operation.

With the MySQL command there are also various considerations for AUTO_INCREMENT columns and multiple unique keys. You can read more at the MySQL page for the INSERT ... ON DUPLICATE KEY UPDATE feature.

A Note About MS SQL Server 2008

MS SQL Server introduced something like UPSERT in SQL Server 2008. It uses the MERGE command, which is a bit hairy, check it out in this nice tutorial.

Coding a Simpler UPSERT

Let us say that we want a simpler UPSERT, where you do not have to mess with SQL Server's MERGE or rewrite the entire command as in MySQL. This can be done with triggers.

To illustrate, consider a shopping cart with a natural key of ORDER_ID and SKU. I want simple application code that does not have to figure out if it needs to do an INSERT or UPDATE, and can always happily do INSERTs, knowing they will be converted to updates if the line is already there. In other words, I want simple application code that just keeps issuing commands like this:


We can accomplish this by a trigger. The trigger must occur before the action, and it must redirect the action to an UPDATE if necessary. Let us look at examples for MySQL, Postgres, and SQL Server.

A MySQL Trigger

Alas, MySQL giveth, and MySQL taketh away. You cannot code your own UPSERT in MySQL because of an extremely severe limitation in MySQL trigger rules. A MySQL trigger may not affect a row in a table different from the row originally affected by the command that fired the trigger. A MySQL trigger attempting to create a new row may not affect a different row.

Note: I may be wrong about this. This limitation has bitten me on several features that I would like to provide for MySQL. I am actually hoping this limitation will not apply for UPSERTs because the new row does not yet exist, but I have not had a chance yet to try.

A Postgres Trigger

The Postgres trigger example is pretty simple, hopefully the logic is self-explanatory. As with all code samples, I did this off the top of my head, you may need to fix a syntax error or two.

CREATE OR REPLACE FUNCTION orderlines_insert_before_F()
    result INTEGER; 
    -- Find out if there is a row
    result = (select count(*) from orderlines
                where order_id = new.order_id
                  and sku      = new.sku

    -- On the update branch, perform the update
    -- and then return NULL to prevent the 
    -- original insert from occurring
    IF result = 1 THEN
        UPDATE orderlines 
           SET qty = new.qty
         WHERE order_id = new.order_id
           AND sku      = new.sku;
        RETURN null;
    END IF;
    -- The default branch is to return "NEW" which
    -- causes the original INSERT to go forward
    RETURN new;


-- That extremely annoying second command you always
-- need for Postgres triggers.
CREATE TRIGGER orderlines_insert_before_T
   before insert
   EXECUTE PROCEDURE orderlines_insert_before_F();
A SQL Server Trigger

SQL Server BEFORE INSERT triggers are significantly different from Postgres triggers. First of all, they operate at the statement level, so that you have a set of new rows instead of just one. Secondly, the trigger must itself contain an explicit INSERT command, or the INSERT never happens. All of this means our SQL Server example is quite a bit more verbose.

The basic logic of the SQL Server example is the same as the Postgres, with two additional complications. First, we must use a CURSOR to loop through the incoming rows. Second, we must explicitly code the INSERT operation for the case where it occurs. But if you can see past the cruft we get for all of that, the SQL Server exmple is doing the same thing:

CREATE TRIGGER upsource_insert_before
ON orderlines
    DECLARE @new_order_id int;
    DECLARE @new_sku      varchar(15);
    DECLARE @new_qty      int;
    DECLARE @result       int;

    DECLARE trig_ins_orderlines CURSOR FOR 
            SELECT * FROM inserted;
    OPEN trig_ins_orderlines;

    FETCH NEXT FROM trig_ins_orderlines
     INTO @new_order_id

    WHILE @@Fetch_status = 0 
        -- Find out if there is a row now
        SET @result = (SELECT count(*) from orderlines
                        WHERE order_id = @new_order_id
                          AND sku      = @new_sku
        IF @result = 1 
            -- Since there is already a row, do an
            -- update
            UPDATE orderlines
               SET qty = @new_qty
             WHERE order_id = @new_order_id
               AND sku      = @new_sku;
            -- When there is no row, we insert it
            INSERT INTO orderlines 
            UPDATE orderlines

        -- Pull the next row
        FETCH NEXT FROM trig_ins_orderlines
         INTO @new_order_id

    END  -- Cursor iteration

    CLOSE trig_ins_orderlines;
    DEALLOCATE trig_ins_orderlines;

A Vague Uneasy Feeling

While the examples above are definitely cool and nifty, they ought to leave a certain nagging doubt in many programmers' minds. This doubt comes from the fact that an insert is not necessarily an insert anymore, which can lead to confusion. Just imagine the new programmer who has joined the team an is banging his head on his desk because he cannot figure out why his INSERTS are not working!

We can add a refinement to the process by making the function optional. Here is how we do it.

First, add a column to the ORDERLINES table called _UPSERT that is a char(1). Then modify the trigger so that the UPSERT behavior only occurs if the this column holds 'Y'. It is also extremely import to always set this value back to 'N' or NULL in the trigger, otherwise it will appear as 'Y' on subsequent INSERTS and it won't work properly.

So our new modified explicit upsert requires a SQL statement like this:


Our trigger code needs only a very slight modification. Here is the Postgres example, the SQL Server example should be very easy to update as well:

   ...trigger declration and definition above
   IF new._upsert = 'Y'
      result = (SELECT.....);
      _upsert = 'N';
      result = 0;
   END IF; of trigger is the same

The UPSERT feature gives us simplified code and fewer round trips to the server. Without the UPSERT there are times when the application may have to query the server to find out if a row exists, and then issue either an UPDATE or an INSERT. With the UPSERT, one round trip is eliminated, and the check occurs much more efficiently inside of the server itself.

The downside to UPSERTs is that they can be confusing if some type of explicit control is not put onto them such as the _UPSERT column.

Next week we will see a concept similar to UPSERT used to efficiently create summary tables.

Categories: Development


Subscribe to Oracle FAQ aggregator - Development