Oracle, Timesten and PL/SQL support

I thought to post about another new interest of mine, TimestTen, as I’ve worked with it in past and I have become a fan of it, especially after Oracle bought the company.

Oracle has announced that TimesTen in-memory database will support PL/SQL in the upcoming release. That’s in 11gR2, where TimesTen is named the “in-memory database cache”.

I’m happy to see the deep level of integration Oracle is doing with it. It looks like both classic Oracle RDBMS and the TimesTen based code will have a (partially) shared PL/SQL code base.
There’s already a package called UTL_IDENT in Oracle 11.1.0.7 which stores couple variables used for conditional compilation depedent on the database product the packages are installed:

SQL> select dbms_metadata.get_ddl('PACKAGE', 'UTL_IDENT') from dual;

DBMS_METADATA.GET_DDL('PACKAGE','UTL_IDENT')
--------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYS"."UTL_IDENT" is

  /* A typical usage of these boolean constants is

         $if utl_ident.is_oracle_server $then
           code supported for Oracle Database
         $elsif utl_ident.is_timesten $then
           code supported for TimesTen Database
         $end
   */

  is_oracle_server     constant boolean := TRUE;
  is_oracle_client     constant boolean := FALSE;
  is_timesten          constant boolean := FALSE;

end utl_ident;
/

It looks that there will be at least some utility packages which share common code base with classic Oracle RDBMS and just use different code internally if needed, based on the UTL_IDENT variables.

This was a nice surprise as such approach promises to have full PL/SQL utilities in TimesTen cache and no discrepancy between the PL/SQL utility capabilities available in different platforms. So (I hope) there will be just one PL/SQL, not one PL/SQL version for Oracle and other for the cache.
Back before 9i days, it was quite annoying to hit the SQL interface discrepancies between PL/SQL and native SQL engines. So there’s hope that there are gonna be less porting issues between Oracle classic RDBMS and TimesTen cache (both for application developers and TimesTen coders as only low level interfaces need to be changed).

Well, at least in theory it’s like that… But I hope Oracle/TimesTen coders will do a good job with this as I’ve worked with some algorithmic trading apps doing extreme transaction processing and in this world you don’t even like wasted microseconds as this directly translates to lost trading opportunities and lost potential revenue. The legacy solutions for such apps are usually some in-memory data stores, often built in house and lots of C++ code around it for procedural work. The data is then loaded / dumped to some disk-based RDBMS asynchronously (as the multi-millisecond response times of a classic RDBMS take ages!!!)

I’m waiting to get my hands on the 11gR2 (as everyone who’s reading this blog I suspect :), but especially the integrated in-memory database cache engine as it promises to make the life easier for a lot of algorithmic trading systems developers (and also the ability adapt quicker to ever-changing markets and growing complexity of products for investment banks and trading houses).

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

4 Responses to Oracle, Timesten and PL/SQL support

  1. ;) Actually I’m waiting for Timesten to support PL/SQL.
    I hope it’s a good… I’d like to migrate old system to use it.

    About Oracle Database 11gR2, that make me sure to upgrade more 11gR1.

  2. jean says:

    Well, I was afraid that this product disappeared when Oracle bought it.
    I tested it “by mistake”
    and my hope is to have an access from an sqlplus client.
    It should then open a new transparent solution to create a cache on a site before accessing to a central Oracle db.

  3. demir says:

    I want to write function over oracle 11g. This function gets nvarchar2(50) as parameter and return cursor. Not return ref cursor. Can you help me?

  4. Amir Riaz says:

    Thanks God they have heard.
    I have implemented a solution using times tens using times tens as cache. Luckly Tom kytes learned about it and really appreciated it. here is the thread where we discuss this architecture and asked for a pl/sql solution back in 2007
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:676698900346506951

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>