Development

Screencast of Oracle E-Business Suite setup management tool

Raimonds Simanovskis - Thu, 2008-07-24 16:00

URRA.pngWarning: this post will be unrelatead to Ruby :)

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

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

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

Categories: Development

More APEX sessions at Oracle OpenWorld 2008

Oracle Apex Notebook - Mon, 2008-07-21 11:35
According to Oracle OpenWorld Blog, the top 25 voted sessions at Oracle Mix will be presented at Oracle OpenWorld 2008. Among the 25 winners there is a good representation of APEX, meaning that APEX community is very active. The winners, APEX related are: Rank: 1 (113 votes) Security: Writing Custom Authentication Schemes for Oracle Application Express (Oracle APEX).  Submitted by: Raj Mattamal
Categories: Development

Oracle OpenWorld 2008

Pawel Barut - Thu, 2008-07-17 15:44
Written by Paweł Barut
It's only 2 month left to Oracle OpenWorld 2008. Few news about this conference and my first time ever participation. Last week I've registered for OOW2008 as Blogger. Yesterday I've received confirmation email - I was accepted. I'm really looking forward to be in San Francisco and meet with all Oracle geeks, enthusiast and other bloggers.
I still need to apply for US Visa, and organize my trip. And of course choose sessions in which I would like to participate.

There is also one good news for people in Eastern Europe, Post Soviet Republics, Middle East & Asia. People from those countries can get discount on registration fee.
Attendees will need to enter EMKT as their priority code during "Step 2" of the registration process.

The countries from the EE, CIS & MEA regions which qualify for the discount are:

African Operations (except South Africa), Albania, Armenia, Azerbaijan, Belarus, Bosnia & Herzegovina, Bulgaria, Croatia, Czech Republic, Cyprus, Estonia, FYR Macedonia, Georgia, Hungary, Iraq, Iran, Jordan, Kazakhstan, Kyrgyzstan, Latvia, Lebanon, Lithuania, Malta, Moldova, Montenegro, Oman, Palestine, Poland, Romania, Russia, Serbia, Slovakia, Slovenia, Syria, Tajikistan, Turkey, Turkmenistan, Ukraine, Uzbekistan, and Yemen.

Do not wait. Register for Oracle OpenWorld 2008 !

Paweł

--
Related Articles on Paweł Barut blog:

Categories: Development

Dynamic Date Filtering in Interactive Reports

Anthony Rayner - Wed, 2008-07-16 06:26
Update: Please note, in Application Express 4.0, it is now possible to define a 'Row Filter', where you can reference SYSDATE directly from the filter. For further information, please see the 'Adding a Row Filter' section of the user guide.

Introduction...
Whilst the filtering part of interactive reports currently supports a fair number of options, there isn't currently the facility to specify a dynamic date filter using SYSDATE. A common use case for this would be a dashboard report where you want to display all the overdue items in a dataset. In the interactive report filter definition, it would look something like this...


But unfortunately, we do not currently support the use of SYSDATE in the Expression field. So what can you do? I will discuss 3 possible workarounds for achieving this.

Note: Method 1 does not require any changes from a developer perspective, methods 2 and 3 require some developer work.

How...
Method 1
Use the 'Is in the last' operator, and then specify some arbitrarily large amount (100 years).


This could then be saved by the user as a 'Named Report' for future use. Although this is the quickest and easiest method, it isn't very intuitive for the user to come up with and also it's not 100% water tight to use this filter, as it might be possible that a date is entered outside of the arbitrary range specified.



Method 2

It's possible to specify filters on interactive reports via the URL. This uses the 'IR%' prefix in the 'Item Names' argument in the URL syntax, as detailed in the documentation. This method involves dynamically provisioning a link to the interactive report page and could be implemented by creating a SQL report to look like a menu, like this:

1) Add a region of type 'SQL Report' and for the 'Region Source', specify:
select sysdate from dual
2) Ensure the following is set during the wizard creation:
- Report Template: template: [theme number].Borderless
3) Go the 'Report Attributes' page and specify the following:
- Pagination Scheme: No pagination.
- Untick the 'Show' checkbox for the SYSDATE column.
- Click 'None' for 'Headings Type'.
3) Add a column link to the report by going the 'Report Attributes' page and clicking the 'Add Column Link' link from the 'Tasks' menu on the right. For this column link, specify the following values:
- Link Text: 'Overdue Reviews'
- Page: 2
- Clear Cache: '2,CIR'
- Item 1 Name: IRLT_REVIEW_DATE
- Item 1 Value: #SYSDATE#
Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.

This generates a link like this:


Of particular interest here are...
  • Clear Cache = '2,CIR'
    This clears the cache for page 2, and also uses the new 'CIR' parameter, which clears all settings that could already have been applied to the interactive report (including any default report settings).

  • Item Names = 'IRLT_REVIEW_DATE'
    This can be broken down as follows:
    • 'IR': This is used to set interactive report filters dynamically over the URL.

    • 'LT': The characters directly after the 'IR' prefix are equivalent to the 'Operator' value in the interactive report 'Filter' dialog, so in this case 'LT' specifies a 'Less than' operator. Note: You could also use 'LTE' which would specify a 'Less than or equal to' operator. See here for more options.

    • 'REVIEW_DATE': Then after the underscore, this is the column name on which to apply the filter, equivalent to the 'Column' value in the interactive report 'Filter' dialog. In this case we are filtering on the 'REVIEW_DATE' column.


This method works well, but does require some sort of separate menu or link to go to the interactive report, which steps out of the normal use-case for interactive reports.


Method 3
The final method requires the developer to code a derived column to calculate if the record is overdue in the report SQL. A snippet something like:
(case 
when review_date < sysdate then 'Yes'
else
'No'
end) review_overdue
Once this has been done by the developer, the user could then choose to apply a filter on this column as shown in the screenshot:


This could then be saved by the user as a 'Named Report' for future use.


Note: With methods 1 and 3, the developer could also default these reports for users, saving the report as 'Default Report Settings' in the 'Save Report' dialog. This means that when any user logs into and sees this report, they will default to these filters. One consideration here though, when the developer saves the 'Default Report Settings', you cannot specify a 'Name' for the report, so the user would see the filter as it is coded without a named tab.


Conclusion...
So method 3 would be my preferred choice, because it works and I believe provides the most user friendly solution for the users whilst keeping within the standard use-case for interactive reports. One consideration of this approach though would be that no index would be used on that column (see comments).


A couple of questions for the community...
  • Would you find it useful to be able to filter by SYSDATE?

  • Would you find it useful to be able to filter not just by a value, but by another column value?

  • What else would you like to see incorporated into Interactive Reports to make them even better?


Further Reading...
General overview and information on interactive reports
Marcie Young's Advanced Interactive Report Tutorial
APEX documentation page on dynamic interactive report filtering via the URL
David Peake's (APEX PM) blog post on 'Advanced Interactive Reports

Categories: Development

New job...

Anthony Rayner - Thu, 2008-07-10 07:59
Just a quick note to say I've recently made a commitment to Application Express and joined the APEX development team here at Oracle. I am very excited to work for a cracking team on a great product and look forward to blogging much more about it in the future.

Anthony.
Categories: Development

Custom ActiveRecord create, add and delete methods for legacy databases

Raimonds Simanovskis - Wed, 2008-07-09 16:00

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

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

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

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

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

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

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

Then we define ActiveRecord model in the following way:

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

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

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

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

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

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

sudo gem install activerecord-oracle_enhanced-adapter

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

Categories: Development

Vote for my session proposal at Oracle OpenWorld

Raimonds Simanovskis - Mon, 2008-07-07 16:00

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

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

Categories: Development

ActiveRecord Oracle enhanced adapter version 1.1.1 released

Raimonds Simanovskis - Fri, 2008-06-27 16:00

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

  • Added ignore_table_columns option for class definitions
    You can specify which table (or view) columns should be ignored by ActiveRecord – these could be either columns which you do not need in Rails application or which have currently unsupported data types. For example:
    class Employee < ActiveRecord::Base
      ignore_table_columns  :phone_number, :hire_date
    end
    
  • Added support for TIMESTAMP columns
    You can create tables with :timestamp data type which will create TIMESTAMP columns and you can access values from TIMESTAMP columns. Unfortunately due to current ruby-oci8 limitations when you will retrieve TIMESTAMP values it will be without fractional seconds (but if you pass Time value with fractional seconds then it will be stored in database).
  • NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT independent DATE and TIMESTAMP columns support
    By default Oracle adapter (and enhanced adapter as well) changes NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT to ‘DD-MON-YYYY HH24:MI:SS’. But if you are working with some legacy database which requires different NLS date settings then now you can change these settings to different ones. You can put in some initialization file some other NLS settings, e.g.:
    ActiveRecord::Base.connection.execute %q{alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'}
    ActiveRecord::Base.connection.execute %q{alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS'}
    

    and ActiveRecord will continue to work correctly with date values.

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

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

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

To get the new release just do:

sudo gem install activerecord-oracle_enhanced-adapter

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

Categories: Development

Why are some of the tables in my query missing from the plan?

Inside the Oracle Optimizer - Thu, 2008-06-26 17:35
We apologize for our brief hiatus from blogging. We've been busy working on improvements to the optimizer.

In 10gR2, we introduced a new transformation, table elimination (alternately called "join elimination"), which removes redundant tables from a query. A table is redundant if its columns are only referenced to in join predicates, and it is guaranteed that those joins neither filter nor expand the resulting rows. There are several cases where Oracle will eliminate a redundant table. We will discuss each case in turn.

Primary Key-Foreign Key Table Elimination

Starting in 10gR2, the optimizer eliminates tables that are redundant due to primary key-foreign key constraints. Consider the following example tables:

create table jobs
(
job_id NUMBER PRIMARY KEY,

job_title VARCHAR2(35) NOT NULL,
min_salary NUMBER,
max_salary NUMBER
);

create table departments
(
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50)
);
create table employees
(
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(50),
department_id NUMBER REFERENCES departments(department_id),
job_id NUMBER REFERENCES jobs(job_id)
);

and the query:

select e.employee_name
from employees e, departments d
where e.department_id = d.department_id;

In this query, the join to departments is redundant. The only column referenced in the query appears in the join predicate, and the primary key-foreign key constraint guarantees that there is at most one match in departments for each row in employees. Hence, the query is equivalent to:

select e.employee_name
from employees e
where e.department_id is not null;


The optimizer will generate this plan for the query:

-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

Note that the IS NOT NULL predicate is not necessary if the column has a NOT NULL constraint on it.

Starting in 11gR1, the optimizer will also eliminate tables that are semi-joined or anti-joined. Consider the following query:

select e.employee_id, e.employee_name
from employees e
where not exists (select 1
from jobs j
where j.job_id = e.job_id);


Since employees.job_id is a foreign key to jobs.job_id, any non-null value in employees.job_id must have a match in jobs. So only employees with null values for employees.job_id will appear in the result. Hence, this query is equivalent to:

select e.employee_id, e.employee_name
from employees e
where job_id is null;

and the optimizer can choose this plan:

-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."JOB_ID" IS NULL)

Suppose employees.job_id has a NOT NULL constraint:

alter table employees modify job_id not null;

In this case, there could not possibly be any rows in EMPLOYEES, and the optimizer could choose this plan:

-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
* 1 FILTER
2 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)

The "NULL IS NOT NULL" filter is a false constant predicate, that will prevent the table scan from even taking place.

Also in 11gR1, the optimization became available for ANSI compliant joins. For this query:

select employee_name
from employees e inner join jobs j
on e.job_id = j.job_id;

the optimizer can eliminate JOBS and produce this plan:

-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------

Outer Join Table Elimination

In 11gR1, a new form of table elimination was introduced for outer joins, which does not require PK-FK constraints. For the example, we require a new table and an addition to EMPLOYEES:

create table projects
(
project_id NUMBER UNIQUE,
deadline DATE,
priority NUMBER
);

alter table employees add project_id number;

Now consider a query that outer joins employees and projects:

select e.employee_name, e.project_id
from employees e, projects p
where e.project_id = p.project_id (+);

The outer join guarantees that every row in employees will appear at least once in the result. The unique constraint on projects.project_id guarantees that every row in employees will match at most one row in projects. Together, these two properties guarantee that every row in employees will appear in the result exactly once. Since no other columns from projects are referenced, projects can be eliminated, and the optimizer can choose this plan:

-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------

Why Would I Ever Write Such a Query?

All of the example queries in this post are very simple, and one would be unlikely to write a query where the join is so obviously unnecessary. There are many real world scenarios where table elimination may be helpful, including machine-generated queries and elimination of tables in views. For example, a set of tables might be exposed as a view, which contains a join. The join may be necessary to retrieve all of the columns exposed by the view. But some users of the view may only access a subset of the columns, and in this case, the joined table can be eliminated.

For example, consider the view:

create view employee_directory_v as
select e.employee_name, d.department_name, j.job_title
from employees e, departments d, jobs j
where e.department_id = d.department_id
and e.job_id = j.job_id;

This view might be exposed to a simple employee directory application. To lookup employee names by job title, the application issues a query:

select employee_name
from employee_directory_v
where department = 'ACCOUNTING';

Since the job_title column is not referenced, jobs can be eliminated from the query, and the optimizer can choose this plan:

--------------------------------------------
Id Operation Name
--------------------------------------------
0 SELECT STATEMENT
* 1 HASH JOIN
2 TABLE ACCESS FULL EMPLOYEES
* 3 TABLE ACCESS FULL DEPARTMENTS
--------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
3 - filter("D"."DEPARTMENT_NAME"='ACCOUNTING')

Known Limitations

There are currently a few limitations of table elimination:
  • Multi-column primary key-foreign key constraints are not supported.
  • Referring to the join key elsewhere in the query will prevent table elimination. For an inner join, the join keys on each side of the join are equivalent, but if the query contains other references to the join key from the table that could otherwise be eliminated, this prevents elimination. A workaround is to rewrite the query to refer to the join key from the other table (we realize this is not always possible).
Categories: DBA Blogs, Development

ruby-plsql gem now supports JRuby and Oracle JDBC driver

Raimonds Simanovskis - Wed, 2008-06-25 16:00

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

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

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

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

To install this gem on JRuby execute

sudo jruby -S gem install ruby-plsql

or

sudo jgem install ruby-plsql

depending on how you have installed JRuby on your computer.

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

Categories: Development

Using mod_rails with Rails applications on Oracle

Raimonds Simanovskis - Tue, 2008-05-20 16:00

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

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

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

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

User yourusername
Group yourusername

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

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

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

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

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

RailsRuby /usr/local/bin/ruby_with_env

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

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

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

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

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

Categories: Development

ORA-00904: "XMLROOT": invalid identifier

Pawel Barut - Sat, 2008-05-17 04:55
Written by Paweł Barut
Some time ago I've had noticed strange problem with XMLRoot function. I was installing application on production server and I've noticed that code:
SQL> select XMLRoot(xmltype('<a>a</a>'))
  2  from dual;
gives error:
select XMLRoot(xmltype('<a>a</a>'))
       *
Error in line 1:
ORA-00904: "XMLROOT": invalid identifier
WTF, it was running perfectly on development and test environment!
Quick search revealed that XMLROOT is function in XDB schema, which was missing in production environment. I've just copies source code for function from test environment and I could proceed further.
After some time, I've decided to check why this function was missing?
Quick search showed that function is created by script ?\demo\schema\order_entry\xdbUtilities.sql
Strange, well documented function is created only when you install demo schemas? Seems that there should be another explanation.
Then I've found that in documentation this function has 2 mandatory attributes, while my code has only one attribute. So there are 2 versions of XMLRoot function:
  1. SQL function; see documentation
  2. Simplified version created by demo in XDB schema - this version can be also used in PL/SQL

Conclusion:
my original code should look like that:
SQL> select XMLRoot(xmltype('<a>a</a>'), version '1.0', standalone yes)
  2  from dual;

XMLROOT(XMLTYPE('<A>A</A>'),VERSION'1.0',STANDALONEYES)
------------------------------------------------------------------------

<?xml version="1.0" standalone="yes"?>
<a>a</a>
This can run without XMLROOT function in XDB schema.

Hope this will help someone to save some time.
Cheers,Paweł

--
Related Articles on Paweł Barut blog:
Categories: Development

ActiveRecord Oracle enhanced adapter

Raimonds Simanovskis - Mon, 2008-05-12 16:00

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

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

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

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

sudo gem install activerecord-oracle_enhanced-adapter

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

Initial version contains the following enhancements:

  • Improved perfomance of schema dump methods when used on large data dictionaries
  • Added LOB writing callback for sessions stored in database (see previous post)
  • Added emulate_dates_by_column_name option
    Set the option below and as a result columns with DATE in their name will be emulated as Date (and not as Time which is default for DATE columns in database)
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates_by_column_name = true
    
  • Added emulate_integers_by_column_name option
    Set the option below and as a result number columns with ID at the end of column always will be emulated as Fixnum (useful if in legacy database column type is specified just as NUMBER without precision information which by default is mapped to BigDecimal Ruby type)
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_integers_by_column_name = true
    
  • Added emulate_booleans_from_strings option
    Set the option below and as a result CHAR, VARCHAR2 columns or VARCHAR2 columns with FLAG or YN at the end of their name will be emulated as booleans (and “Y” and “N” will be used to store true and false values). This is useful for legacy databases where Rails default convention of NUMBER for boolean values cannot be used (e.g. if you are using Oracle E-Business Suite where booleans are stored as “Y” and “N”).
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans_from_strings = true
    

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

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

Categories: Development

Session proposal for OOW - Using Ruby on Rails with Oracle E-Business Suite

Raimonds Simanovskis - Sat, 2008-05-03 16:00

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

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

Categories: Development

How to setup Ruby and new Oracle Instant Client on Leopard

Raimonds Simanovskis - Wed, 2008-04-23 16:00

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

Introduction

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

Installing Oracle Instant Client for Intel Mac

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

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

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

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

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

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

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

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

Ruby installation

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

Compile and install ruby-oci8

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

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

'-arch i386'

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

ruby setup.rb config
make
sudo make install

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

'-arch ppc -arch i386'

Now try

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

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

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

Categories: Development

Oracle CPUApr2008 for DB: 10.2.0.4 patch set and first install

Pawel Barut - Sun, 2008-04-20 08:40
Written by Paweł Barut
To my surprise CPUApr2008 is not available for latest patch-set of Oracle 10gR2 (10.2.0.4) that was released in first quarter 2008. In note that goes together with patch 6810189:
The following are the updated components of this patch set:
  * Up to January 2008 CPU is included in this patch set
...
Based on this I was expecting that CPUApr2008 will be also available for 10.2.0.4 patch-set. In note 552248.1 you will find then:

1.3 Database 10.2.0.4 Patch Set

The Database 10.2.0.4 Patch Set includes the CPUApr2008 content.

So the CPUApt2008 was available to all at least month before official release and was included in 10.2.0.4 patch set! Is this situation secure?


Anyway I had to install CPUApr2008. It's good that there is no need to recompile views this time. I've sucessfully installed it on test server, with minor problem. In installation log I've found one problem:

BEGIN emd_maintenance.recompile_invalid_objects; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYSMAN.EMD_MAINTENANCE" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"SYSMAN.EMD_MAINTENANCE"
ORA-06512: at line 1

Hmm... After reconnecting, this commend run without errors:
SQL> conn / as sysdba
Connected.
SQL> BEGIN sysman.emd_maintenance.recompile_invalid_objects; END;

PL/SQL procedure successfully completed.
After all, server runs ok, and I do not find any problems within applications running on this.


Cheers Paweł

--
Related Articles on Paweł Barut blog:
Categories: Development

Oracle Database 11g Release 2 is coming

Pawel Barut - Tue, 2008-04-15 15:52
Written by Paweł Barut
I've just received Oracle Partner Network newsletter with invitation to Oracle Database 11g Release 2 Roadshow. On this Roadshow Oracle is also going to find partners for beta testing. My guess: This year Oracle 11g R2 will be the main topic on Oracle Open World.
Schedule of Roadshow in Europe:
  • May 12, 2008: UK, Reading
  • May 13, 2008: Benelux, De Meern
  • May 14, 2008: Germany, Muenchen
  • May 15, 2008: Italy, Milan
  • May 16, 2008: Turkey, Istanbul
  • May 19, 2008: Czech Republic, Prague
  • May 20, 2008: Poland, Kraków
  • May 21, 2008: Sweden, Stockholm
  • May 22, 2008: Spain, Madrid
If you are OPN member then you probably also get invitation.
I've registered. I hope to get confirmation soon. I do not expect many technical details. I rather expect marketing speech. For me most important would be to meet people that work on new DB version, and become beta tester for 11gR2.

Cheers Paweł

--
Related Articles on Paweł Barut blog:
Categories: Development

Video from Euruko 2008

Raimonds Simanovskis - Fri, 2008-04-04 16:00

I made short video from Euruko 2008 conference where you can see Matz, Koichi, JRuby guys, DrNic ar me as well :)

I posted my presentation slides in my previous post.

Categories: Development

My presentation on using Ruby with Oracle at Euruko conference

Raimonds Simanovskis - Wed, 2008-04-02 16:00

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

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

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

Categories: Development

Which Temporary Tablespace is used for sorts?

Pawel Barut - Mon, 2008-03-24 12:34
Written by Paweł Barut
This time I will write about "Which Temporary Tablespace is used for sorts" ?. I was not wondering about this much, as usually there is only one Temporary tablespace in DB. Lets assume situation, as show on picture:
  • User A
    • Assigned to Temporary Tablespace TEMP_A
    • has table TA
  • User B
    • Assigned to Temporary Tablespace TEMP_B
    • has table TB
    • owns procedure PB (definer rights)
  • Both users has access rights for all above object (Select on Tables and Execute on Procedure)
So let's discuss some situations:
  1. User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
  2. User A executes procedure PB. Procedure PB opens cursor on table TB (or TA or any other). If disk sort is required then tablespace TEMP_B is used.
For me it was bit surprising. Especially that I did not find anywhere in Oracle documentation description for this behaviour:
TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments.
I was expecting that all sort segments will be created in tablespace that is assigned to that user. I was hopping to solve one of my issues that way. But it occurred that, sort segment is created by user B, because procedure PB uses user B rights. It is reasonable, as this is consistent with granting access to objects, and temporary objects are treated the same way as permanent ones. On the other hand select is run for user A - shouldn't TEMP_A be used in all cases? What is your opinion on that?

Cheers Paweł

--
Related Articles on Paweł Barut blog:
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development