ruby-plsql-spec gem and code coverage reporting

Raimonds Simanovskis - Mon, 2010-10-04 16:00

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:

  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

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:

  RETURN 'test_profiler';
    RETURN 'others';

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'

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.

Categories: Development

Oracle enhanced adapter 1.3.1 and how to use it with Rails 3

Raimonds Simanovskis - Wed, 2010-09-08 16:00

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'

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

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:

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

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:

  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:

  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:

  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:

  adapter: oracle_enhanced
  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:

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

Categories: Development

Moving blog from to Jekyll

Raimonds Simanovskis - Sun, 2010-08-08 16:00
Why to move?

This blog was hosted for several years on as it was the easiest way to host a blog when I started. But recently I was not very satisfied with it because of the following reasons:

  • I include code snippets in my blog posts quite often and several times I had issues with code formatting on I used MarsEdit to upload blog posts but when I read previous posts back then quite often my < and > symbols were replaced with &lt; and &gt;.
  • I would prefer to write my posts in Textile and not in plain HTML (I think it could be possible also with but it was not obvious to me).
  • I didn’t quite like CSS design of my site and wanted to improve it but I prefer minimalistic CSS stylesheets and didn’t want to learn how to do design CSS specific for Wordpress sites.
  • Wordpress site was too mainstream, I wanted something more geeky :)

When I do web app development then I use TextMate for HTML / CSS and Ruby editing (sometime I use CSSEdit when I need to do more CSS editing), I use Textile for wiki-style content editing in my apps, I use git for version control, I use Ruby rake for build and deployment tasks. Wouldn’t it be great if I could use the same toolset for writing my blog?

What is Jekyll?

I had heard about Jekyll blogging tool several times and now I decided that it is the time to start to use it. Jekyll was exactly matching my needs:

  • You can write blog posts in Textile (or in Markdown)
  • You can design HTML templates and CSS stylesheets as you want and use Liquid to embed dynamic content
  • You can store all blog content in git repository (or in any other version control system that you like)
  • And finally you use jekyll Ruby gem to generate static HTML files that can be hosted anywhere

So it sounds quite easy and cool therefore I started migration.

Migration Initial setup

I started my new blog repository using canonical example site from Jekyll’s creator. You just need to remove posts from _posts directory and start to create your own.

Export from

At first I needed to export all my existing posts from I found helpful script which processes export and creates Textile source files for Jekyll as well as comments import file for Disqus (more about that later). It did quite good job but I needed anyway to go manually through all posts to do the following changes:

  • I needed to manually change HTML source for lists to Textile formatted lists (export file conversion script converted just headings to Textile formatting) as otherwise they were not looking good when parsed by Textile formatting.
  • I needed to wrap all code snippets with Jekyll code highlighting tags (which uses Pygments tool to generate HTML) – as previously I had not used consistent formatting style I could not do that by global search & replace.
  • I needed to download all uploaded images from and put them in images directory.
CSS design

As I wanted to create more simple and maintainable CSS stylesheets I didn’t just copy previous CSS files but manually picked just the parts I needed. And now as I had full control over CSS I spent a lot of time improving my previous design (font sizes, margins, paddings etc.) – but now at least I am more satisfied with it :)


As all final generated pages are static there is no standard way how to do typical dynamic pages like list of posts with selected tag. But the good thing is that I can create rake tasks that can re-generate all dynamic pages as static pages whenever I do some changes to original posts. I found some examples that I used to create my rake tasks for tag pages and tag cloud generation.

Related pages

Previously was showing some automatically generated related posts for each post. Initially it was not quite obvious how to do it (as site.related_posts was always showing the latest posts). Then I found that I need to turn on lsi option and in addition install GSL library (I installed it with homebrew) and RubyGSL (as otherwise related posts generation was very slow).


The next issue is that in static HTML site you cannot store comments and you need to use some hosted commenting system. The most frequently commenting system in Jekyll sites is Disqus and therefore I decided to use it as well. It took some time to understand how it works but it provides all necessary HTML snippets that you need to include in your layout templates and then it just works.

Previously mentioned script also included possibility to import my existing comments from into Disqus. But that was not quite as easy as I hoped:

  • Disqus API that allows to add comments to existing post that is found by URL is not creating new discussion threads if they do not exist. Therefore I needed at first to open all existing pages to create corresponding Disqus discussion threads.
  • As in static HTML case I do not have any post identifiers that could be used as discussion thread identifiers I need to ensure that my new URLs of blog posts are exactly the same as the old ones (in my case I needed to add / at the end of URLs as URL without ending / will be considered as different URL by Disqus).
  • There was issue that some comments in export file had wrong date in URL (it was in cases when draft of post was prepared earlier than post was published) and I needed to fix that in export file.

So be prepared that you will need to import and then delete imported comments several times :)

RSS / Atom feeds

If you have existing subscribers to your RSS or Atom feed then you either need to use the same URL for new feed as well or to redirect it to the new feed URL. In my case I created new Feedburner feed and redirected old feed URL to the new one in .htaccess file.

Other URL mappings

In my case I renamed categories to tags in my blog posts and URLs but as these old category URLs were indexed by Google and were showing on to Google search results I redirected them as well in .htaccess file.


If you want to allow search in your blog then the easiest way is just to add Google search box with sitesearch parameter.


Previously I used standard analytics pages to review statistics, now I added Google Analytics for that purpose.


Finally after all migration tasks I was ready to deploy my blog into production. As I had account at Dreamhost I decided that it is good enough for static HTML hosting.

I created rake tasks for deployment that use rsync for file transfer and now I can just do rake deploy to generate the latest version of site and transfer it to hosting server.

After that I needed to remap DNS name of to new location and wait for several hours until this change propogated over Internet.

Additional HTML generation speed improvements

When I was doing regular HTML re-generation using jekyll I noticed that it started to get quite slow. After investigation I found out that the majority of time went on Pygments execution for code highlighting. To fix this issue I found jekyll patches that implemented Pygments results caching and I added it as ‘monkey patch’ to my repository (it stores cached results in _cache directory). After this patch my HTML re-generation happens instantly.

My blog repository

I published ‘source code’ of my blog on GitHub so you can use it as example if I convinced you to migrate to Jekyll as well :)

The whole process took several days but now I am happy with my new “geek blogging platform” and can recommend it to others as well.

Categories: Development

APEX 4.0 Enhancements: Validating Form Data

Anthony Rayner - Thu, 2010-08-05 07:48
Oracle Application Express 4.0 introduces lots of big new features; websheets, dynamic actions, plug-ins, RESTful web services, team development, updated charts, the list goes on. But there are also many enhancements to existing functionality that we hope will help to simplify the overall process of developing applications in APEX. One such area, and the focus of this post is how data is validated. This post will give you an overview of what's changed with validations and how these changes will make your daily development life a little easier.

Item-Centric Validation
Historically in APEX if you want to validate data input on a page, you create a validation. The validation is a separate component that you need to define and maintain. Now in APEX 4.0, the actual item can handle some simple validation of the data it receives. For example, all item's (both native to APEX and plug-ins) now have a 'Value Required' attribute. By setting this to 'Yes', APEX will automatically validate a value has been entered and raise an error if not, no separate validation required.

Additional to this 'Value Required' validation, some item types also validate their data, based on how the item is defined. For example, the new 'Number' item type, which you can use for handling numeric data contains settings for 'Minimum Value' and 'Maximum Value'. When these are defined, APEX will automatically validate the data received based on these settings and raise appropriate errors.

Settings for the new 'Number' item, settings highlighting are automatically validated

The new datepicker item in APEX 4.0 also supports this type of automatic validation. Additional to the 'Value Required' setting (available for all items), the datepicker also allows setting 'Format Mask', 'Minimum Date', 'Maximum Date' and 'Year Range'. In doing so, APEX will again automatically validate the data received based on these settings and raise appropriate errors.

Settings for the new 'Date' item, settings highlighting are automatically validated

Plug-in items may also contain automatic validations, depending on whether the plug-in author has coded in this support.

You also have a couple of ways of customising the error message that is displayed by APEX when these validations fail, in terms of content and position. To override the default error messages, please refer to this section of the user guide. This details all of the 'Text Messages' that you would need to define in your applications to override the defaults. If you want to change where the error is displayed on screen, please see the 'Default Error Display Location' attribute available via 'Edit Application Properties' on the application homepage.

Finally, debug mode has also been enhanced to show when these validations are executed and whether they passed or failed.

Item based validations offer a more logical approach to validating data and requires fewer moving parts, which means less to define and less to maintain. Of course, for other more complex situations a separate validation will still be required, but this greatly simplifies some of the more common, simple scenarios.

Button-Centric Validation Exclusion
Again, historically in APEX if you don't want a validation to fire when certain buttons are pressed, you would define that logic in the validation. Let's take an example. If you have a typical 'Form' page used for inserting, updating and deleting data, you may want your validations to fire for insert and update, but not for delete. This would involve going through each validation and setting some condition such as where REQUEST != 'DELETE' or similar, to prevent the validation from firing. Now, in APEX 4.0, the actual button can be defined to either 'Execute Validations' or not.

Specify that pressing the button should not cause any validations to fire.

This is much easier, all you need to do is set this at button level and that's it, no item, plug-in or custom validations will fire. Wizard created forms will set this up for you automatically, so when creating these types of forms, the 'Create' and 'Save' buttons execute validations and the 'Delete' and 'Cancel' buttons do not.

You can also override this at validation level by setting the 'Always Execute' validation attribute to 'Yes' (defaults to 'No'). This could be useful for example if you want to always execute a security check, regardless of any button exclusions.

Debug mode has again been enhanced here to show if validations are prevented from firing because of the button setting.

Tabular Form Validations
APEX 4.0 now also supports declarative validation of tabular form data. Before APEX 4.0, there was no declarative support for validating this type of form and you would have to do a lot of manual PL/SQL to validate your data. Currently, tabular form validations only support a subset of what's available with page item validation, but do cater for some of the more common scenarios (required values, type checks and string comparisons). We are looking to extend this in a future release of APEX.

Error Message Label Placeholders
This is small but one of my favourites. When defining an error message that displays when a validation fails, if the validation is associated with a specific page item, you can now use the #LABEL# placeholder to dynamically reference the associated item's label.

Use #LABEL# instead of hard-coding the associated item label text.

So instead of having to duplicate the label text in the error message (and have to remember to change it if you change the item's label) as was historically the case, just use the #LABEL# placeholder and that's it. Again, less to define and less to maintain. An equivalent placeholder is also available for the new tabular form validations, #COLUMN_HEADER#.

Upgrading Applications
So finally, what about your existing APEX applications that have been long since built, where you want to take advantage of some of these new features. Well, take a look at the 'Upgrade Application' feature available via the 'Utilities' menu from the application homepage. This assists you in upgrading your application to use some of the new features in APEX 4.0.

Of particular relevance to validations are the following upgrade types:
  • Update Text Field Item to Number Field Item, where appropriate - Locates where you have an unconditional 'Is Numeric' validations on 'Text Field' items and upgrades them to use the 'Number' item type with in-built numeric checking. Also removes the now redundant separate validation.
  • Update Value Required item attribute to Yes, where appropriate - Locates where you have unconditional 'Not Null' validations on items and sets those item's 'Value Required' attribute to 'Yes'. Also removes the now redundant separate validation.
  • Numeric, Required and Date Picker Item updates based upon conditional validations - Just locates where you have conditional validations for 'Is Numeric', 'Not Null' or 'Is Valid Date' on 'Text Field' items, for your manual review. So you can determine if the validation can be replaced with some item settings and button exclusions.

So quite a few little enhancements that hopefully add up to easier and more intuitive data validation. Good luck with your new APEX 4.0 style validations and let us know what you think!

Many thanks to Patrick Wolf for reviewing this post and filling in the gaps.
Categories: Development

APEX 4.0 - Do you want to know more?

Anthony Rayner - Tue, 2010-08-03 07:32
Do you want to ask the Vice President of Database Tools at Oracle and original developer of Application Express a question about Oracle Application Express 4.0?

Mike Hichwa is going to be interviewed by Oracle Profit Magazine on APEX, so if you have something you want to ask, questions are being collected for consideration via Twitter. Tweet your questions to @OracleProfit, with the hash tag #askprofit. Selected submissions will receive a 1GB flash drive — and be printed in the November issue of Profit Magazine.
Categories: Development

We have moved!

Inside the Oracle Optimizer - Sat, 2010-07-31 12:57
You might have been wondering why things had gone so quiet on the Optimizer development team's blog Optimizer Magic over the last few months. Well the blog has moved to All of the old articles have moved too and we plan to be a lot more active at our new home, with at least one new post every month.

Categories: DBA Blogs, Development

Report filtering with APEX 4.0, dynamic action style

Anthony Rayner - Wed, 2010-07-21 02:45
I've just got back from the APEX SIG event in Birmingham. As ever, the event was well organised and attended and it's always good to catch up with the extremely knowledgable and enthusiastic UK APEX community. If you haven't yet attended one of these events then I'd definitely recommend them, you will undoubtedly learn something new, make some great contacts and enjoy the day! The next event is planned for 3rd November at the Oracle City Office in London, so if you're interested in coming along, please see here.

Down to business. During the event I was asked whether dynamic actions in APEX 4.0 can handle Ajax based filtering of report data. The answer is yes, so thought I'd make the most of the train journey home to blog about how. The approach is slightly different depending on whether you're dealing with classic or interactive report regions. In this post, I will detail the steps to achieve this with Interactive Report Regions (IRRs), fully declaratively without coding 1 line of JavaScript.

You can also see this working in my sample application here. This example shows a report on the EMP table, with quick filters available to filter by department or job via Ajax, partial page refresh.

The following steps assume that you have the EMP and DEPT tables in your current schema.

With IRRs, the basic approach involves the following 4 steps:
  • Create a page with a report region, ensuring the page item filters are referenced in the SQL.
  • Create the page items for filtering.
  • Create the dynamic action to fire whenever any of the page item filters change value, to refresh the IRR.
  • Define the IRR to save these items values in session state after it is refreshed.

Now let's walk through these steps in detail.
  • Create the page
    1. Within an application, click the 'Create Page' button.
    2. Select 'Report', click 'Next'.
    3. Select 'Interactive Report', click 'Next'.
    4. For 'Page Attributes', enter 'Employee Report' for 'Page Name' and 'Region Name'. Importantly, you must also ensure that the 'Region Template' is set to some template that contains the #REGION_STATIC_ID# substitution string, otherwise this example will not work (dynamic actions need this ID to be able to perform the refresh). Most of the new themes now default to an appropriate template when creating new IRRs (as defined by the new Theme 'Region Default' for 'Interactive Reports'). However if you're using an old theme then you may need to select an appropriate template (try 'Region without Buttons and Title'). Also make a note of the page number (I'll assume page 1 for these steps) and click 'Next'.
    5. For 'Tab Attributes', leave as default and click 'Next'.
    6. For 'Enter a SQL SELECT Statement', enter the following query (referencing the page number noted in step 4 when defining the item names) and click 'Next':

      select e.empno,
      from emp e,
      dept d
      where e.deptno = d.deptno
      and e.deptno = nvl(:P1_DEPTNO, e.deptno)
      and e.job = nvl(:P1_JOB, e.job)
    7. Click 'Finish'.
    8. For purposes of this example, we're going to switch off the standard 'Search' bar to keep things clean. You can do this by right clicking on the 'Employee Report' in tree view and selecting 'Edit Report Attributes'. Then in the 'Search Bar' region, select 'No' for 'Include Search Bar' and 'Apply Changes'.
    If you now click 'Run Page', you'll see the basic report page as been created. We now need to add the page item filters.
  • Create the page items
    1. Go to edit the page. We'll create these items in a separate 'Quick Filters' region above the IRR. In 'Tree View', right click on the region position where the IRR has been created (by default this is 'Body (3)' and select 'Create'.
    2. Select 'HTML' and click 'Next'.
    3. Select 'HTML' and click 'Next'.
    4. For 'Title', specify 'Quick Filters', for 'Region Template' select 'Report Filter - Single Row' and amend the 'Sequence' to be say 5, so that this appears before the IRR on the page, click 'Next'.
    5. For 'Region Source', leave blank and click 'Create Region'. This has created a blank region, that will be the container for our filter items. We'll create 2 filters, to filter the employee report by the job and department columns respectively.
    6. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
    7. Select 'Select List', click 'Next'.
    8. For 'Item Name', enter 'P1_DEPTNO', click 'Next'.
    9. For 'Item Attributes', accept defaults and click 'Next'.
    10. For 'Settings', accept defaults and click 'Next'.
    11. Ensure 'Display Null Value' is 'Yes'.
    12. For 'Null Display Value' enter '- Show All -'.
    13. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
    14. For 'List of Values Query', enter the following query and click 'Next':

      select dname d,
      deptno r
      from dept
      order by 1
    15. For 'Source' accept defaults and click 'Create Item'.
    16. Now for the job select list. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
    17. Select 'Select List', click 'Next'.
    18. For 'Item Name', enter 'P1_JOB', click 'Next'.
    19. For 'Item Attributes', accept defaults except for the 'Begin on New Line' attribute. We want to set this to 'No', so that the filters appear on the same line in the 'Quick Filters' region. Click 'Next'.
    20. For 'Settings', accept defaults and click 'Next'.
    21. Ensure 'Display Null Value' is 'Yes'.
    22. For 'Null Display Value' enter '- Show All -'.
    23. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
    24. For 'List of Values Query', enter the following query and click 'Next':

      select distinct job d,
      job r
      from emp
      order by 1
    25. For 'Source' accept defaults and click 'Create Item'.
    If you run the page now, you'll see all the basic page components are created, the regions and the items, but selecting different values from the filters has no effect. We now need to bring these to life.
  • Create the dynamic action
    1. Go to edit the page. Right click on the 'Dynamic Actions' tree node and select 'Create'.
    2. Select 'Advanced' for the 'Implementation'. We need 'Advanced' here because 'Standard' only supports the 'Show', 'Hide', 'Disable' and 'Enable' actions.
    3. For 'Name', enter 'QUICK FILTER REFRESH', click 'Next'.
    4. For 'When', leave the event as 'Change' and for 'Item(s)', enter 'P1_DEPTNO,P1_JOB'. Leave 'Condition' as 'No Condition'. These selections define the dynamic action will fire whenever either the department or job select list's value changes. Click 'Next'.
    5. For 'Action', select 'Refresh' under the 'Component' category in the select list. The 'Refresh' action currently supports IRRs, classic reports, all item types with cascading LOV support and may also support item or region plug-ins, depending on whether the plug-in author has coded the plug-in to support this (the plug-in documentation should state if this is supported). Charts in APEX 4.0 are not yet supported.
    6. Ensure the 'Fire on Page Load' checkbox is unchecked, there is no need to refresh the report when the page loads, it's already fresh. Click 'Next'.
    7. On the 'Affected Elements' page, we'll define what will be refreshed. Select 'Region' from the 'Selection Type' select list and 'Employee Report' from the 'Region' select list. Click 'Create'.
    If you now run the page, you'll notice that this still isn't working as expected. Actually, the dynamic action is firing, the report is being refreshed, but it is not being scoped by the filter selection. The problem is the values for the filter page items are not being saved to session state and are therefore not set when the report's SQL is executed. This is easy to fix.
  • Define the IRR to save these items values in session state after it is refreshed
    1. Right click on the 'Employee Report' and select 'Report Attributes'.
    2. Go the 'Advanced' and for 'Page Items to Submit', enter 'P1_DEPTNO,P1_JOB'. Click 'Apply Changes'.

So that's it, run the page and you will now see the filters are fully functional. Select different jobs and departments and see the report refresh, showing employees scoped by your selections. This example could be easily extended to have different item types used as the filters, not just select lists. A common example would be a slider component where you select a value or range of values, upon which the report is refreshed with rows specific to your selection. I will try and add that to my sample application when I can.

For classic reports, this is slightly more complicated than as outlined above, as there is no 'Page Items to Submit' attribute (yet!!), so you need to save the values in session state in a slightly different fashion. I will try and add that too when I can. (Hint: Instead of defining the IRR 'Page Items to Submit' as detailed in the last bullet, use the 'Execute PL/SQL Code' action within the dynamic action, to fire before the 'Refresh' action. Just leave the code as 'null;' and set the 'Page Items to Submit' attribute to your page items. This will work, but unfortunately issues 2 Ajax calls to do so, which is not optimal.)

Also, if you're interested in seeing more examples of refreshing reports with dynamic actions, I have a couple of others in the sample application:
  1. Refresh - Showing an alternative approach for user's to select their 'Saved Reports' in IRRs. Instead of using the default select list in the search region, this has been switched off in favour of a separate report on the left of the page containing the saved reports available to the current user. This report also shows a tooltip for the report description (if defined), when hovering over the report name. The dynamic action is used to refresh this saved report list, so if a user saves a new report or deletes a report, the report list is updated. Again this is all via Ajax and does not require any manual JavaScript coding.
  2. Refresh 2 - Showing how report row deletion can be handled via Ajax with dynamic actions. This example does require a couple of places within dynamic actions where a line of JavaScript is required.

To learn more about these examples, please download the application, install in your workspace and have a closer look.
Categories: Development

JDeveloper Overview and (book) Review

Java 2 Go! - Sat, 2010-07-17 16:55
Dear visitors, I will pretend I forgot that I'm about a year without posting anything and I will go straight to the subject that I owe to you: The review of the book "Processing XML documents with...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

APEX4 Password Strength Meter Item Plugin

Oracle Apex Notebook - Fri, 2010-07-16 10:26
I was doing some tests with the new plugin functionality coming with APEX4 and the result is this plugin I'm sharing with you today.I based the plugin on the "Ajax Password Strength Meter Script" that you can find following this link:'s a small plugin using jQuery that provides an easy interactive way to show the strength of a
Categories: Development

APEX 4.0 - New Dynamic Action Sample Application

Anthony Rayner - Thu, 2010-07-08 05:10
In Application Express 4.0, we have introduced a new feature called 'Dynamic Actions', which enables you to define client-side behaviour declaratively in APEX. In order to try and help people gain understanding in some of the possibilities this feature offers, I have created a new dynamic action sample application featuring many different examples of both native and plug-in dynamic actions.
  • You can access this application online here.
  • Alternatively, you can download this application and install it in your own workspace, which I would recommend so you can really see what's going on. Download here.

Please note: Currently the drag and drop example doesn't work in IE, I will try and fix this up when I have time.

Useful Links:
  • To sign up for a free workspace and try out APEX 4.0 for yourself, please click here.
  • To learn more about this and other new features in Oracle Application Express 4.0, please visit our New Features page.
  • Dynamic action official documentation
  • To follow the Oracle Dynamic Actions OBE, which offers an excellent starting point, click here.

I welcome any feedback you may have and will try and blog some more about some of the specific examples when I can.

Categories: Development

Oracle enhanced adapter 1.3.0 is Rails 3 compatible

Raimonds Simanovskis - Sun, 2010-06-20 16:00

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


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.

Categories: Development

Please vote for my Ruby session proposals at Oracle OpenWorld

Raimonds Simanovskis - Wed, 2010-06-16 16:00

oow2010.pngI 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 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 with your 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!

Categories: Development

How to upgrade your Dell’s BIOS directly from Ubuntu

Java 2 Go! - Mon, 2010-05-24 23:27
I know this post is totally off topic but I faced this same issue last week and I’m pretty sure this will be very handy for a lot of people out there. So why not share it, right?! Many people...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Micromanaging Memory Consumption

Java 2 Go! - Mon, 2010-05-24 22:34
by Eduardo Rodrigues As we all know, specially since Java 5.0, the JVM guys have been doing good job and have significantly improved a lot of key aspects, specially performance and memory management,...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

The easy-small-simple-quick-step-by-step-how-to article on AspectJ you’ve been looking for is right here

Java 2 Go! - Fri, 2010-05-07 20:24
by Eduardo Rodrigues That’s right. Have you ever spent hours of your precious time googling the Web trying to find an easy, small, simple, quick and step-by-step tutorial, article or sample on how to...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

How to write a simple yet “bullet-proof” object cache

Java 2 Go! - Fri, 2010-05-07 19:49
…continued from a previous post, by Eduardo Rodrigues As promised, in this post, I’ll explain how we solved the 2nd part of the heap memory exhaustion problem described in my previous post: the skin...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

The X (Path) File

Java 2 Go! - Fri, 2010-05-07 19:48
by Eduardo Rodrigues This week I came across one of those mysterious problems where I had some test cases that needed to verify the content of some DOM trees to guarantee that the test went fine. So,...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Oracle + Sun + Iron Man 2: Awesome!

Java 2 Go! - Fri, 2010-04-09 15:22
A cool Iron Man 2 teaser...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Don’t be smart. Never implement a resource bundle cache!

Java 2 Go! - Sun, 2010-03-14 06:09
by Eduardo Rodrigues Well, first of all, I’d like to apologize for almost 1 year of complete silence. Since I’ve transferred from Oracle Consulting in Brazil to product development at the HQ in...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development


Subscribe to Oracle FAQ aggregator - Development