360 Degree DB Programming

My updates are on Twitter and/or Facebook

2011-12-08T04:55:00.000-08:00

http://www.facebook.com/profile.php?id=814634920

@kmensah (on Twitter)

OOW 2011: What is Going on for JDBC, UCP, Java in the Database, Net Services, OCI, PHP, Ruby, Python, and Perl

2011-08-31T15:37:00.000-07:00

Oracle OpenWorld San Fransicso 2011

Sessions for DBA and/or application developers (Java, C, C++, PHP, Ruby, Python, Perl) looking for features and best practices to optimize networking and build best performing, scalable and highly-available applications with Oracle Database 11g.

Session: 14702

Java in the Database—The Sky's the Limit: Lessons from the Largest Deployment

Where: Marriott Marquis - Salon 8 -- When: 10/3/11, 15:30

---

BoF Session: 26220

Meet the Oracle JDBC and Oracle Universal Connection Pool Development Team

Where: Marriott Marquis - Salon 8 -- When: 10/3/11, 18:30

---

BoF Session: 26240

Meet the Oracle Database Clients Developers: C, C++, PHP, Python, Ruby, and Perl

Where: Marriott Marquis - Salon 8 -- When: 10/3/11, 19:30

---

Hands-on Lab - Session: 30082

Develop and Deploy High-Performance Web 2.0 PHP, Ruby, or Python Applications

Where: Marriott Marquis - Salon 10/11 -- When: 10/3/11, 17:00

---

Session: 13360

Optimize Java Persistence/Scale Database Access with JDBC and Oracle Universal Connection Pool (UCP)

Where: Marriott Marquis - Salon 8 -- When: 10/4/11, 17:00

---

Hands-on-lab - Session: 30021

Efficient Java Persistence with JDBC, Java Universal Connection Pool, and Java in the Database

Where: Marriott Marquis - Salon 10/11 -- When: 10/4/11, 13:15

---

Session: 14345

Net Services: Best Practices for Performance, Scalability, and High Availability

Where: Moscone South - 303 -- When: 10/5/11, 13:15

---

Session: 14703

Build, Deploy, and Troubleshoot Highly Performant, Highly Available Apps with Oracle Database

Where: Moscone South - 303 -- When: 10/5/11, 17:00

---

Session: 14704

PHP, Ruby, Python, and Perl: Develop and Deploy Mission-Critical Apps with Oracle Database 11g

Where: Marriott Marquis - Salon 8 -- When: 10/5/11, 11:45

Database Web Services Callout utility

2011-03-30T11:11:00.000-07:00

Following OTN migration to UCM, the link to the Wev Services Callout is broken; while i am fixing it, you can download it from here.

Here it the link again http://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip

OTN Dev Days Database Toronto

2011-03-24T16:00:00.000-07:00

Toronto: OTN Dev Days Database is coming to you; we are still taking registration and There Is Such A Thing As Free Lunch.

OTN Developers Days: Hands-on Oracle Database 11g App Dev

2011-02-18T17:41:00.000-08:00

OTN Dev Days Hands-on Oracle DB 11g is coming to you in Dallas (03/09th) , Toronto (03/30th) and Chicago (04/25th).
See you there.

Speaking at Confoo.ca 2011

2011-02-18T17:32:00.000-08:00

Speaking at Confoo.ca 2011 - here are my sessions
Develop & Deploy Mission Critical Web Applications and
Step by Step: GC Tuning in the HotSpot JVM
(replacing Charlie).

My upcoming 2-Day Hands-on Seminar in Kuala Lumpur

2010-10-21T10:06:00.000-07:00

If you can make it to Kuala Lumpur on Dec 20th, you don't want to miss http://ning.it/aDMfVN

My OOW interview you don't want to miss!

2010-10-06T15:26:00.000-07:00

On Online Applicayion upgrade, Java application failover, Query Change Notification, Java in the database, and Scripting languages.

Watch it here and enjoy.

OOW 2010: What is Going on for JDBC, Java in the Database, OCI, PHP, Ruby, Python, Perl and Net Services

2010-08-22T12:33:00.000-07:00

If you are a DBA or an application developer, here are some sessions for you at Oracle Open World 2010 San Francisco:

Session#: S316995
Developing, Deploying, and Diagnosing Applications on Oracle Database 11g
Track: Database Development
Date: 20-SEP-10
Time: 10:00 - 11:00
Venue: Hotel Nikko
Room: Bay View

Session#: S317092
End-to-End Java: Decide When to Adopt Java in the Database and Best Practices
Track: Database Development
Date: 20-SEP-10
Time: 13:00 - 14:00
Venue: Hotel Nikko
Room: Nikko Ballroom I

Session#: S317046
Best Practices and Trade-offs for Efficient and Reliable Java Persistence
Track: Database Development
Date: 21-SEP-10
Time: 08:00 - 09:00
Venue: Hotel Nikko
Room: Monterey I / II

Session#: S317002
Best Practices for Building High-Performance Applications with Oracle Database
Track: Database Development
Date: 21-SEP-10
Time: 11:30 - 12:30
Venue: Hotel Nikko
Room: Nikko Ballroom I

Session#: S317049
Oracle Net Services: Performance, Scalability, HA, and Security Best Practices
Track: Database
Date: 21-SEP-10
Time: 12:30 - 13:30
Venue: Moscone South
Room: Rm 302

Session#: S317007
PHP, Python, Ruby, and Perl in Enterprises: Advanced Tips for Agile Development
Track: Database Development
Date: 21-SEP-10
Time: 14:30 - 15:30
Venue: Hotel Nikko
Room: Golden Gate

Session#: S318542
Hands-On JDBC, Oracle Universal Connection Pool, and Java in the Database
Track: Database Development
Date: 22-SEP-10
Time: 13:00 - 14:00
Venue: Hilton San Francisco
Room: Imperial Ballroom A

Session#: S318543
Develop a PHP Web Application with Oracle Database 11g in One Hour
Track: Database Development
Date: 22-SEP-10
Time: 16:45 - 17:45
Venue: Hilton San Francisco
Room: Franciscan A / B / C / D

Session#: S318547
Develop a Python/Django Web Application with Oracle Database 11g in One HourTrack: Database Development
Date: 23-SEP-10
Time: 11:00 - 12:00
Venue: Hilton San Francisco
Room: Franciscan A / B / C / D

Session#: S318545
Develop a Ruby on Rails Web Application with Oracle Database 11g in One Hour
Track: Database Development
Date: 23-SEP-10
Time: 12:30 - 13:30
Venue: Hilton San Francisco
Room: Franciscan A / B / C / D

OTN Developer Days - Database Coming to You

2010-07-14T14:36:00.000-07:00

Free event (TISATAAFL) OTN Developer Days Database Coming to You: in Boston http://ning.it/aowUgZ, at Oracle HQ http://ning.it/aNz77V and in Vancouver http://ning.it/9X6y1Y.
Register now, seats fill up fast!

OTN Developer Days Database in Reston, VA

2010-05-07T09:01:00.000-07:00

After Disneyland Anaheim, the New Format of OTN Developer Days Database is Returning to Reston, VA - http://ning.it/c5Ze82

Speaking at Collaborate 2010

2010-04-18T22:02:00.000-07:00

En route for #C10 in Las Vegas - main speaker session 327 rm Surf D @ 11:45am and co-speaker session 336 Database App Dev @ 3:15pm - see you there.

Excuse my French!

2010-04-15T09:08:00.001-07:00

Video of my talk at the French Linux Open Source conf http://ning.it/bHAcUZ

Free event, May 5th: OTN Developer Days Database

2010-04-07T15:56:00.000-07:00

Disneyland is for Developers too; join us on May 5 @ the Orange County Disneyland Resort

Oracle: new superstar of Open Source?

2010-03-17T10:59:00.000-07:00

Oracle and yours truly in the French newspaper @ http://ning.it/cPidVr (see the last paragraph).

Speaking at Confoo

2010-01-24T18:56:00.000-08:00

My sessions
Oracle is Gold sponsor.

What is happening on Jan 13th in NYC?

2009-12-01T08:46:00.001-08:00

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=101955&src=6773869&src=6773869&Act=85

See you there

Presenting PHP, Net Services at DOAG

2009-11-16T13:07:00.000-08:00

http://bit.ly/1pmAxS
Look for

PHP Oracle Web Applications: Best Practices and Caching Strategies
Oracle Net Services – Best Practices for Database Performance and Scalability

Code|Works -- PHP Oracle Web Applications: Best Practices and Caching Strategies

2009-09-11T12:38:00.000-07:00

It's here : PHP Oracle Web Applications: Best Practices and Caching Strategies

I'll be speaking at NYC and DC while my complice, Chris Jones, will be speaking in Dallas and Atlanta.

Talk Locations & Times

Dallas, Sep 27 @ 09:00
Atlanta, Sep 29 @ 11:15
Washington/Baltimore, Oct 3 @ 11:15
New York, Oct 5 @ 13:15

See you there.

Oracle Open World 2009 Unconferences

2009-09-01T15:03:00.000-07:00

http://wiki.oracle.com/page/Oracle+OpenWorld+Unconference

I recommend
Tuesday, Oct 13th, 10:00am - Overlook II: Experiences on Java programming within the databases, tips & tricks, tools, open source libraries and more. (Oracle ACE Marcelo F. Ochoa)

Tuesday, Oct 13th, 2:00pm - Overlook I: Oracle adapters for Ruby ORMs - ActiveRecord and DataMapper (Raimonds Simanovskis)

Tuesday, Oct 13h, 2:00pm Overlook II: How to use native database REST Web Services in Oracle 11g by Marcelo F. Ochoa

Thursday, Oct 15th, 11:00am - Overlook I: Automate server installation and configuration with Chef (Raimonds Simanovskis)

Oracle Open World 2009 - Java, JDBC, C/C++, OCI, PHP, Ruby, Python and Net Services sessions

2009-08-24T18:41:00.000-07:00

Want to hear about the latest about JDBC, Java in the database, C, C++, OCI, PHP, Ruby/Rails, Python/Django, and Net Services at Oracle Open World?

Here are the technical sessions (mark your calendar and pre-register) :

S311371 Best Practices for High-Performance Applications with Oracle Database 11g -- Mon 10/12 17:30-18:30 Hilton Hotel Golden Gate

S311372 Tips and Techniques for Building Enterprise-Class C/C++ Applications -- Tues 10/13 14:30-15:30 Hilton Hotel Golden Gate

S311373 Agile Web Development: Ruby/Rails? and Python/Django? with Oracle Database 11g -- Sun 10/11 10:30-11:30 Hilton Hotel Golden Gate 1

S311378 Tips for Debugging and Troubleshooting Common Java/JDBC Applications Errors -- Tues 10/13 13:00-14:00 Hilton Hotel Golden Gate 2

S311381 Developing & Deploying the Largest Web-sites using PHP and Oracle Database 11g -- Tues 10/13 16:00-17:00 Hilton Hotel Golden Gate 2

S311395 Performance & Availability of Java applications using RAC and UCP -- Sun 10/11 13:15-14:15 Hilton Hotel Golden Gate 1

S311402 Speed & Space -- Performance and Memory Usage of Java in the Database -- Mon 10/12 11:30-12:30 Hilton Hotel Golden Gate 2

S311643 Oracle Net Services Best Practices for Database Performance and Scalability -- Thur 10/15 10:30-11:30 Moscone South Room 305

Here are the hands-on labs (mark your calendar and pre-register) :

S312327 Develop Painless Enterprise Web Applications using Ruby/Rails? and Oracle Database 11g -- Tue 10/13 11:30-12:30 Hilton Continental Parlor 1/2/3

S312328 Develop C/C++ Applications with Oracle Database 11g using Oracle Call Interface, Pro*C, ODBC -- Mon 10/12 14:30-15:30 Hilton Hotel Continental Parlor 1/2/3

S312329 Develop Scalable Applications using JDBC and Java in Oracle Database 11g -- Mon 10/12 16:00-17:00 Hilton Hotel Continental Parlor 1/2/3

S312330 Develop Painless Enterprise Web Applications using PHP and Oracle Database 11g -- Sun 10/11 14:30-15:30 Hilton Hotel Continental Parlor 1/2/3

S312331 Develop Painless Enterprise Web Applications using Python/Django? and Oracle Database 11g -- Sun 10/11 13:15-14:15 Hilton Hotel Continental Parlor 1/2/3

See you there.

DataMapper Oracle Adapter

2009-07-21T14:27:00.000-07:00

Raimonds just posted a step by step instructions for using DataMapper (Ruby ORM, alternative to ActiveRecord) with Oracle; check it out.

http://blog.rayapps.com/2009/07/21/initial-version-of-datamapper-oracle-adapter/

Your Take on Oracle JDBC Drivers

2009-07-16T09:55:00.001-07:00

The Oracle JDBC development team would like to hear your experience; please tell us what works and what does not work for you.
http://spreadsheets.google.com/embeddedform?key=rqx1Y0vD-zTDxJZpNG0j1bQ

PyOhio: free Ohio based Python Miniconf.

2009-07-14T14:15:00.000-07:00

PyOhio "The Free Ohio-based Python Miniconference" is on Saturday & Sunday July 25-26 at Ohio State University. On Sunday Catherine Devlin and Oracle Technology Network's Todd Trichler will have an Oracle/Python/Linux Tutorial.

Speaking at Kaleidoscope 2009 on Wed June 24h 2:45pm

2009-06-19T09:53:00.000-07:00

http://www.odtugkaleidoscope.com/
My talk is about: Web Scale Applications with PHP, Ruby and Oracle 2:45pm - 3:45pm

Hands-on Oracle Database 11g Applications Development

2009-05-01T09:20:00.000-07:00

http://www.oracle.com/go/?&Src=6635701&Act=229&pcode=NAMK08038812MPP033

Java Performance: The Return of the Usual Suspects (Updated)

2009-04-30T09:30:00.000-07:00

http://blogs.oracle.com/olaf/2009/04/java_performance_the_return_of.html

Book Review: Processing XML documents with Oracle JDeveloper 11g

2009-04-13T19:19:00.000-07:00

I am no XML expert but my first interest for the book stems from the fact that wether you like it or not, XML is everywhere; so better embrace it. The book furnishes a comprehensive coverage of all things related to XML including: creating and parsing an XML document, creating and validating an XML Schema, XPath , transforming XML with XSLT, JAXB, Oracle XML Developer Kit (XDK 11g), Oracle XML Publisher, comparing XML documents, converting XML to PDF or MS Excel, even Berkeley DB XML edition.

I have used JDeveloper for doing prorotyping work and proof of concepts related to JDBC, Java in the database, and Database Web Services but, and this is my second interest for the book, i have no idea that JDeveloper furnishes so much capabilities for processing XML documents.

I now have two good reasons to keep and use my copy of this book. For these reasons, i recommend it.

Oracle Database 10g on Mac OS/X

2009-04-11T12:19:00.000-07:00

Oracle Database 10g release 10.2.0.4 for Apple MAC OS X (Leopard 10.5.4) Intel x86-64 Available on OTN http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10204macsoft_x86-64.html

Java in the database podcast @ Java Posse

2009-03-23T14:04:00.000-07:00

A glimpse of my day job: Java in the database @ http://tinyurl.com/ccxb7s -- fwiw, the guy speaking is my boss.

Solving the C20K problem: PHP Performance and Scalability

2009-02-28T19:34:00.001-08:00

A summary of my talk at PHP Quebec @ http://conf.phpquebec.com

PHP is widely adopted for database-backed RIA.

PHP lacks true connection pool; can a commodity database server sustain 20000 concurrent users?
How to ensure availability in the face of a database server failure?
When it comes to scalability, more PHP code or more frameworks (i.e., classes) translates to more CPU power; how to simplify PHP programming and reduce the amount of PHP code using built-in Oracle database 11g mechanisms?

The Community Connect case study will be discussed.

360 Degree DB Programming: Read parts of my book on Google Books

2009-02-22T22:42:00.000-08:00

360 Degree DB Programming: Read parts of my book on Google Books

Gartner Case study: Community Connect use of Oracle, PHP, Database Resident Connection Pool, etc

2009-02-12T23:22:00.000-08:00

"CCI has been collaborating with Oracle's development group to improve memory utilization and reduce overhead across multiple nodes of DBMSs, using connection pooling called Oracle Database Resident Connection Pooling (DRCP). A connection pool is a cache of database connections maintained by the database so that the connections can be reused when the database receives future requests for data. Opening and maintaining a database connection for each user can be time consuming and waste resources. Open connections are placed in a pool to be reused."

http://mediaproducts.gartner.com/reprints/oracle/article60/article60.html

Oracle Python adapter ( cx_Oracle ) got DRCP

2008-12-19T09:31:00.000-08:00

The latest Oracle Python adapter (cx_Oracle) got Database Resident Connection Pool, Python 3 and many other goodies.
See more details.

Web Scale PHP Connection Broker

2008-12-11T06:41:00.000-08:00

My talk at the Forum PHP Paris

Read parts of my book on Google Books

2008-11-16T10:43:00.001-08:00

I just found out that parts of my book (fragments ranging from page 224 to page 374) has been scanned and available online.

The entire section on running Groovy in the database is available.

Enjoy!

Making frameworks suck less

2008-11-06T22:11:00.000-08:00

Terry Chay gave a great talk tonight at the San Francisco PHP Meetup on "making frameworks suck less"

Enjoy

Oracle PHP Video from the Singapore PHP Meetup

2008-10-29T22:38:00.001-07:00

Presented by my colleague Blair Layton; here is the video of the Singaporian PHP Meetup

http://blog.php.com.sg/archives/singapore-php-meetup-oct-2008-oracle

Our OOW Poscast on Dynamic Languages

2008-09-30T15:27:00.001-07:00

http://feeds.feedburner.com/~r/OTN_TechCasts/~3/402542480/6978780_Mensah_Jones_092408.mp3

See you at Oracle Develop Beijin, Mumbai, Moscow, and Prague

2008-09-30T10:11:00.000-07:00

From the success of Oracle Develop sessions at Oracle OpenWorld San Francisco, i anticipate that many of you who could not attend OOW SF will join our OD Winter tour
http://www.oracle.com/events/oracledevelop/index.html

See you there, Kuassi

What is Going on for Database Application Performance (OCI, C/C++, Java/JDBC) and Net Services

2008-08-31T12:02:00.001-07:00

Here are session and hands-on lab related to application performance from database perspective.
See other posts for my Java, JDBC, PHP and Ruby sessions and hands-on labs.

Session:

Best Practices for Developing Performant Applications for Oracle Database 11g Monday 09/22/2008 14:30 - 15:30 Marriott Salon 01
Best Practices for Deployment, Performance and Diagnosability of Oracle Net Services Thursday 09/25/2008 - 10:30 am Moscone South, Rm 104

Hands-on lab:

Hands-on Lab: Best Practices for Developing Performant Applications with Oracle Database 11g Monday 09/22/2008 16:00 - 17:00 Marriott Golden Gate B1

See you there

What is Going on for PHP and Ruby on Rails at Oracle OpenWorld, San Francisco, Sep 21st-25th

2008-08-31T11:45:00.000-07:00

Here are PHP, Ruby, Ruby on Rails sessions and hands-on labs that i am either delivering or coordinating (involved). See other posts for my Java/JDBC sessions and hands-on labs.

Sessions

Building and Deploying Web-Scale Social Networking Applications, Using PHP and Oracle Database 11g Tuesday 09/23/2008 13:00 - 14:00 Marriott Salon 04
Building and Deploying Web-Scale Social Networking Applications, Using Ruby on Rails and Oracle Database 11g Monday 09/22/2008 13:00 - 14:00 Marriott Salon 02

Hands-on Labs

Hands-on Lab: Web 2.0 Applications with Ruby/JRuby on Rails and Oracle Database 11g Tuesday 09/23/2008 14:30 - 15:30 Marriott Golden Gate B1
Hands-on Lab (Part 1): Web 2.0 Applications with PHP and Oracle Database 11g Sunday 09/21/2008 14:30 - 15:30 Marriott Golden Gate B1
Hands-on Lab (Part 2): Web 2.0 Applications with PHP and Oracle Database 11g Sunday 09/21/200815:45 - 16:45MarriottGolden Gate B1

See you there.

What is Going On for JDBC, Java in the Database, and UCP at Oracle OpenWorld, San Francisco, Sept 21-25th

2008-08-31T11:19:00.001-07:00

Here are JDBC, Java in the database and the new Universal Connection Pool (UCP) sessions and hands-on labs that i am either delivering or involved.
Look other posts on this blog for my other sessions and labs.

Sessions

Don't Roll the Dice: Secure Your Java/JDBC Applications with Oracle Advanced Security Tuesday 09/23/2008 11:30 - 12:30 in Marriott Salon 01
Tips for Troubbleshooting Common JDBC Applications Errors Tuesday 09/23/2008 14:30 - 15:30 Marriott Salon 14/15
Data- and Compute-Intensive Processing: Middle Tier or Database? Trade-offs and Case Study Monday 09/22/2008 11:30 - 12:30 Marriott Salon 01
Integrating Oracle Real Application Clusters from the Middle Tier with Oracle Universal Connection Pool Monday 09/22/2008 17:30 - 18:30 Marriott Nob Hill AB

Hands-on Labs

Hands-on Lab: Develop and Diagnose JDBC Programs and Java Classes in the Database Tuesday 09/23/200816:00 - 17:00 Marriott Golden Gate B1
Hands-on Lab: Best Practices for Developing Performant Applications with Oracle Database 11g Monday 09/22/200816:00 - 17:00 Marriott Golden Gate B1

See you there.

Hands-on Oracle Database 11g Application Development

2008-08-19T09:49:00.000-07:00

You don't want to miss this unique opportunity
http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=83729&src=6635701&src=6635701&Act=83

Because this is a hands-on event (laptop provided and pre-staged), space is limited.

Fwiw, you may win

- 1 iPod Touch
- $15 iTunes card
- 1 free OOW pass (does not include travel and expenses)
- books (from recommendations).

Oracle Symfony in PHP Minor

2008-07-13T08:15:00.000-07:00

Mladen Gogala is an Oracle DBA with PHP expertise.
Enjoy the article @
http://www.oracle.com/technology/pub/articles/gogala-symfony.html

PHP OCI8 1.3.3 Has Gone Production

2008-06-24T10:48:00.000-07:00

PHP's OCI8 1.3.3 brings support for Oracle's Database Resident Connection Pool (DRCP), Fast Application Notification (in RAC environment), Client Result Cache technologies giving PHP enterprise class scalability, performance, and high availability.

See the README in the OCI8 bundle and read the white paper http://www.oracle.com/technology/tech/php/pdf/php-scalability-ha-twp.pdf for more information.

New release of database-resident Lucene integration

2008-06-01T11:29:00.000-07:00

I am glad to report that Marcelo as releasd a new releae of its database-resident Lucene using the embedded Java VM in the Oracle database.
http://marceloochoa.blogspot.com/2008/06/new-binary-release-of-lucene-oracle.html

This is a perfect illustration of data-intensive-and-compute-intensive processing that i will be talking about in comign events.

Instant Client on MAC OS X

2008-04-23T18:56:00.000-07:00

10.2.0.4 Database Client (+ Instant Client) for Apple MAC OS X on Intel x86 is now available for download on OTN.
The client is supported on the latest OS release - MAC OS X Leopard 10.5.

Enjoy.

April San Francisco Java Meetup - Quercus: How to Run PHP on Java

2008-04-07T22:10:00.000-07:00

Similar to Jython, and JRuby, Quercus, a 100% Java implementation of PHP 5 allows running PHP applications on a Java VM.

Caucho (the maker of Resin) is positioning this as a PHP/Java bridge which

allows PHP applications to be deployed over a Java application server and leverage Java infrastructure: JDBC, JMX, Web Bean (JSR 299), EJB, etc. WordPress was deployed over Quercus in few minutes .
allows Java to use PHP as view component, in the face of the larger adoption of dynamic languages (PHP, Python, Ruby) for Web applications.

I have not tried but it must be possible to deploy your PHP applications with Quercus, OC4J and the Oracle database.

San Francisco Ruby Monthly Meetup: Open Social

2008-04-06T11:23:00.001-07:00

At this month's SF Ruby meetup:

Ryan Garver of ELC Technology presented Google's Open Social, touted as the next big thing on the web. Open Social aims at being a king of Facebook API for all social sites. Check it out.
Michael Slater and Christopher Haupt described how BuildingWebApps.com site, an information portal for Ruby on Rails developers, with articles, links database, and RSS feed processing. All built, of course, with Rails. This is a one stop portal for Ruby developers.

Kuassi

San Francisco PHP Meetup April: Symfony

2008-04-06T11:16:00.000-07:00

I attended this month's SF PHP Meetup in the city.

Dustin Whittle of Yahoo presented the Symfony framework (initiated by a french guy, oui!!).

The meetup was crowded and well received as you can read here. I like this framework because it borrows many patterns that people like in Prado, Rails, Django, and dynamic languages in general (i.e., straight PHP for templating).

Imo, the following capabilities may be extended with Oracle

ability to cache things in the database (alternatively to memcache, file system and other); may be extended with Oracle's Client Result cache (see my previous posting on this)
ability to use the database for Internationalization/Localization dictionary; may leverage Oracle built-in Internationalization/Localization

Kuassi

My presentations at PHP Quebec 2008

2008-03-23T10:44:00.000-07:00

What Oracle database 11g brings to PHP

Web-Scale PHP Connection Broker

Btw,
-join the Oracle+PHP Users group on http://mix.oracle.com
-join the Oracle+Ruby/Rails Users group on http://mix.oracle.com

Oracle Database and RESTFul Web Services

2008-02-22T14:28:00.000-08:00

The Oracle database 11g furnishes SOAP-based Native Database Web services however, RESTful Web services are having better traction than SOAP based Web Services.

My friend Marcelo has just posted the results of his new proof of concept.

Play with and enjoy.

Kuassi

Oracle Database 11g Client Result Cache Coming to Java

2008-02-19T10:42:00.000-08:00

Server Side Query Result Cache

"Caching, caching and caching” is a well-known secret for performance.
The Oracle database 11g furnishes Query Result Cache, which, when enabled, keeps query result sets in the database server memory for reuse by successive invocation of the same query until invalidated by changes to the underlying table(s); as result, the parsing and executing phases of the query are eliminated.
It also furnishes PL/SQL Function Result Cache[1], which is similar to the SQL query result cache but applied to PL/SQL Functions.

Client-Side Query Result Cache

Caching query result sets in the database memory still requires client/server network round-trip(s). The other well-known secret is "The fastest database access is no database access". You now understand the proliferation of middle-tier data caching frameworks such as open-source Memcache (PHP, Ruby, Java) or Oracle’s Coherence (JCache-compliant in memory distributed data grid), which are object-oriented distributed data caches.
Client Result Cache is SQL oriented[2] middle-tier or client-tier data caching feature of Oracle database 11g which, eliminates the network roundtrip incurred by the server-side query result cache.

In addition, Client Result Cache is synchronized with the database and is automatically invalidated.

Performance Proof Points
Running the Nile benchmark[3] with Client Result Cache enabled and simulating up to 3000 users results in

Up to 6.5 times less server CPU usage
15-22% response time improvement
7% improvement in mid-tier CPU usage

Enabling Client Result Cache

As of Oracle database 11g Release 1, Client Result cache is available only with OCI-based drivers or adapters including: PHP, JDBC-OCI, OCCI, ODP.Net, Pro*C/C++, Pro*COBOL, and ODBC.

1) Server (database):
Set CLIENT_RESULT_CACHE_SIZE (default 0, cache disabled)
2) Client (sqlnet.ora):
Set OCI_RESULT_CACHE_MAX_SIZE (optional); it overrides server cache size
3) Application code.
Explicitly specify which query to cache with a hint in the query string.
e.g., /*+ RESULT_CACHE */

That’s it!

JDBC Example – CLientRSCache.java

/*
* Client Query Result Cache (sorry for the code formatting)
*/
import the java.sql package
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

class CLientRSCache
{

public static void main (String args [])
throws SQLException
{
long start;
long end;
long elapsed;

String name = null;
OracleConnection conn = null;
OraclePreparedStatement pstmt = null;
OracleDataSource ods = new OracleDataSource();
// Set the URL (user name, and password)
String url = "jdbc:oracle:oci:scott/tiger@//localhost:1522:orcl11g";
ods.setURL(url);

String query = "select /*+ result_cache */ * from emp where empno < ?" conn.setImplicitCachingEnabled(true);

conn.setStatementCacheSize(1); // Cache of 1 Stmt

PreparedStatement pstmt ;

ResultSet rs;

for (int i = 1; i <= 10; i++) {

pstmt = conn.prepareStatement (query);

pstmt.setInt(1,7500);

// Set the start time

start = System.currentTimeMillis();

// Execute the query and retrieve the Result Set

rs = pstmt.executeQuery();

while (rs.next( ) )

{

// process result set

rs.close;

pstmt.close( ) ;

}

// Set the End time

end = System.currentTimeMillis();

elapsed = end - start;

// Print the time taken to prepare and execute query

// Note: the 1st invocation is nor cached

// Successive invocations are cached

// You may invalidate the result set using SQL*Plus.

System.out.println(" Iteration# “ + i + “ Time to prepare and execute the query ” + elapsed);

}

// Close the connection

conn.close();

}

Benefits of Client Result Cache

Easy to use
Transparent cache consistency with server side changes
Frees application developers from building a per-process result cache
Extends database-side query caching to middle-tier and client-tiers
Ensures better performance by eliminating round trips to the server
Improves server scalability by saving server resources
Transparent cache management
Concurrent access to result-sets optimizes memory management
RAC support

See more details on Client Result Cache in the Oracle Call Interface Programmer’s guide[4].
[1] See the Oracle database 11g documentation for more details on Query Result cache and PL/SQL Function Result Cache.
[2] It is currently not distributed, unlike Coherence or MemCache
[3] An end-to-end ecommerce application server benchmark
[4] http://www.oracle.com/pls/db111/to_pdf?pathname=appdev.111/b28395.pdf

Speaking at PHP Quebec 2008

2008-02-19T10:30:00.000-08:00

I'll be speaking at PHP Quebec 2008 during the following sessions

1) a lunch time keynote on Oracle database 11g Database Resident Connection Pool (DRCP)
"Web-Scale PHP Connection Broker"

2) PHPLab: a 3 hours BYOL technical workshop where database vendors rep will be asked any question.

If you plan to attend, let's have a free beer together.
Kuassi

PHP and Ruby on Rails: My OTN Podcast with Chris Jones

2007-11-21T13:36:00.000-08:00

My colleague Chris Jones and I discuss the re-built OCI8 driver, Database Resident Connection Pooling with Oracle Database 11g and the roadmap for RoR users.
http://streaming.oracle.com/ebn/podcasts/otn/6084483.mp3

PL/SQL Got Sleep Functionality

2007-11-21T08:57:00.000-08:00

Marcelo Ochoa posted a nice hack to get Sleep functionality in PL/SQL
http://marceloochoa.blogspot.com/2007/11/plsql-sleep-functionality.html

While therei also recommend reading his post about making Log4J work in OracleJVM.
http://marceloochoa.blogspot.com/2007/11/getting-logging-entering-exiting-and.html

What is going on at Oracle Open World for Java in the database, JDBC, PHP, and Ruby on Rails?

2007-10-11T09:48:00.001-07:00

1/ You can meet our developers at our demogrounds (JDBC, PHP, Ruby on Rails) in the database demoground.

2/ Here are the sessions, and hands-on labs that i am driving or involved with

Session ID: S290759 Session Title: Oracle JDBC Best Practices for Performance, Security, High Availability, and Scalability Room: Grand Ballroom A Date: 2007-11-12 Start Time: 11:00

Session ID: S292070 Session Title: The Way to the Web with PHP and Oracle Database Room: Yosemite Room A Date: 2007-11-12 Start Time: 12:30

Session ID: S292132 Session Title: Scale Your PHP Application to Tens of Thousands of Connections Room: HiltonContinental Parlor 7 & 8 Start Time: 1:45 PM

Session ID: S292073 Session Title: Web 2.0 Applications Hands-on Lab with PHP and Oracle Database Room: Continental Ballroom 4 Date: 2007-11-12 Start Time: 13:45

Session ID: S292211 Session Title: Hands-on Lab: Ruby on Rails with Oracle Database Track: Emerging Technologies Room: Continental Ballroom 4 Date: 2007-11-12 Start Time: 16:45

Session ID: S292213 Session Title: Digging Deeper into Dynamic Scripting Languages: Using Ruby on Rails with Oracle Database Room: Continental Parlor 7 & 8 Date: 2007-11-13 Start Time: 10:45

Session ID: S290758 Session Title: Java Stored Procedures or Java Middleware: Trade-offs, Techniques, and Tips from Oracle's Database and Middleware Teams Room: Continental Ballroom 6 Date: 2007-11-13 Start Time: 16:45

Session ID: S290761 Session Title: Behind the Scenes: Getting the Most from the New Java Virtual Machine in Oracle Database 11g Room: 308 Date: 2007-11-14 Start Time: 15:00

Session ID: S290760 Session Title: Oracle Database 11g Release 1 JDBC Pearls: JDBC 4.0, Advanced Security, Query Change Notification, Startup/Shutdown, and Much More Room: 307 Date: 2007-11-15 Start Time: 08:30

See you there

Database Resident Connection Pool Coming to PHP Web 2.0 Applications

2007-10-09T22:16:00.000-07:00

Database Resident Connection Pool (DRCP) in a new 11g feature that PHP applications can already benefit from.
Here is the press release http://biz.yahoo.com/prnews/071009/aqtu120.html?.v=20
and the white paper http://www.oracle.com/technology/tech/oci/pdf/oracledrcp11g.pdf

SOAP in SQL: Invoking external Web Services

2007-07-13T14:55:00.000-07:00

What the Heck?

Have you ever felt the need to federate data from multiple sources, including Web services (e.g., stock price, scientific data, policy tables, tax tables, weather information)?

How would you invoke business operations implemented as external Web services, such as placing new orders, orders shipment, credit card payment transactions (and getting valid authorization numbers) from within the database?

Well, you need to turn your database into a Web Services Consumer.

Do you need to dive into the Web Services protocols, APIs and jargon beforehand?
No, magic JPublisher remove this pain from your neck! DBAs, PL/SQL bigots, and database developers in general don't need to know anything about WSDL, SOAP, UDDI, SOA to call a Web services from SQL or PL/SQL.

How does it work?

4 simple steps
1) Web Service enable your database: see the Web Services Callout utility guide
2) identify the target Web Services and save the location of the WSDL and the endpoint where
it is listening to requests. Examples: the Google Web Service is identified by its wsdl
WSDL: http://api.google.com/GoogleSearch.wsdl and is listening to requests @
Endpoint: http://api.google.com/search/beta2

A simple way to check that a Web service is up and listening to requests at the endpoint in
question is to direct your browser at the endpoint URL; you shoud, receive the following
message:

SOAP RPC Router
Sorry, I don't speak via HTTP GET- you have to use HTTP POST
to talk to me.

3) Ask JPublisher to generate everything needed to invoke the Web services from within the
database

C:\>jpub -u scott/tiger -sysuser=scott/tiger
-proxywsdl=http://api.google.com/GoogleSearch.wsdl
-proxyopts=tabfun,soap
-httpproxy=www-proxy.us.oracle.com:80
-endpoint=http://api.google.com/search/beta2 -dir=tmp

4) Invoke an operation (e.g., Spell Check) of the Web services directly from SQL

SQL> select jpub_plsql_wrapper.dospellingsuggestion( '', 'Nelson Mandelo')
as GOOGLE_Spell_Check_Web_Service
from dual;

That's it!

You can learn more in the following Oracle Database docs: JPublisher, the Java developers guide and also in chapter 16 of my book.

Have fun!

Groovy, Ruby, PHP, Python: the revival of scripting languages

2007-05-24T16:58:00.000-07:00

Scripting languages have been around for a long time but were regarded as only good for tactical quick-and-dirty applications. Then came the web and the need to add dynamic content to page templates through scriptlets (i.e. javascript). Then came the need to produce web pages directly from server-side (middle-tier and/or database).

To make this story short, 20+ millions web sites today are built using PHP, the most popular scripting language; add few millions for PERL, Python, and so on. Scripting languages got their "letter de nobless" and the rest is history!

Groovy, Ruby on Rails are the new generation of scripting languages. In chapter 5 of my book, i gave few examples of scripting languages running directly in the Oracle database including TCL (JACL), PYTHON (JYTHON), SCHEME and GROOVY. Note: these are just proof of concepts.

Even the Java community is embracing scriting languages through the proposed Java specification request 223 (particularly PHP) but The $1 Million question today is whether or not Ruby on Rails will overtake PHP and/or Java.

Just for fun, see this picture and this video.

Have Fun!

Oracle Database at JavaOne

2007-05-03T09:31:00.000-07:00

Hi,

For those attending JavaOne this year, i'll be available to talk Tuesday 05/8th (2:30 pm to 5:30 pm) and Wednesday 05/9th (2:00 to 4:30 pm ) at the Oracle Database demo pod within the Oracle quarter in the exhibition hall.
Stop by if you have interest or questions regarding Oracle JDBC, Java in the database, Oracle Database XE, and SQL Developer.
Fwiw, I'll be doing book signing Wed 05/9th at 4:30pm at the JavaOne book store.

See you

Collaborate07, Application Express and .Net

2007-04-22T18:37:00.000-07:00

I spent 4 days last week in the Mandalay Bay Casino-hotel in Las Vega (not gambling, as you would think!) but attending Collaborate07 a joint IOUG, OAUG and Quest International event.
It was a crowded event according to IT standards but there was in the same time an Entertainment industry convention with 100,000+ atendees; no other convention place can beat Las Vegas!
I gave a session on new JDBC and Java in the database featureas as part of Oracle Database 11g: What's new for Java, JDBC, PHP, OCI and .Net session.
I spent the rest of my stay at the exhibition floor demoing new JDBc and Java in the database.
I was sharing the same demo pod with my colleague Carl Backstrom (the APEX guy) and Christian Shay (the .Net guy). Even though we are all from Oracle it was the opportunity to better know the cool things Oracle is doing in the areas of Application Expres (APEX) and .Net.

I encourage you to visit their blogs http://carlback.blogspot.com/index.html, http://cshay.blogspot.com/ and play with their products.

Have fun, Kuassi

OC4J or Java Stored Procedures

2007-04-11T09:44:00.000-07:00

I came across the following nice story and think i shoud highlight this on my blog:
http://kristianjones.blogspot.com/2007/03/oc4j-or-java-stored-procedures.html

Enjoy, Kuassi

Oracle Develop Coming to Your City

2007-04-07T17:56:00.000-07:00

You probably recall the famous Alexandre Dumas's quote in my book "If you don't go to Lagardere, Lagardere will come to you"?

Well, Oracle Develop is coming to you in Seoul, Bangalore, Beijing, Munich, London, and Prague; check it out @
http://www.oracle.com/technology/events/develop2007/index.html

I'll be presenting:

Oracle JDBC: High-Availability, Load Balancing, Best Practices, and Roadmap
The PHP/Oracle Experience: Building for Scalability
Developing and Deplying Oracle and PHP (Hands-on Labs).

I'll be glad to meet you in person.

See you there, Kuassi

Is Your Java Application FailoverProof (i.e., RAC Aware)?

2007-01-15T21:43:00.000-08:00

Developing and Deploying FailoverProof Java/JDBC Applications in RAC environment using Fast Connection Failover, TAF and Runtime Connection Load Balancing.

Why in the world should a Java developer care about failover and what does it mean to be failoverproof?
Well, failure is inevitable however, in mission critical (i.e., web) deployments, all applications including the Java ones must sustain resource manager (i.e., RDBMS) failure, or connection failure, or transaction failure without disrupting the service.

How exactly?
For the sake of simplicity, let's take a JDBC program. Best practices mandate that Java/JDBC programs capture exceptions and deal with these; here is a skeleton of a failoverproof program using Oracle JDBC in RAC environment:
...
try
{
conn = getConnection();
// do some work
} catch (SQLException e) {
handleSQLException(e); }
...
handleSQLException (SQLException e)
{
if (OracleConnectionCacheManager.isFatalConnectionError(e))
ConnRetry = true; // Fatal Connection error detected
}

Capturing SQL exceptons and re-trying to get a connection are all good JDBC programming so the burden is not really at the Java application level (it has to be somewhat portable), rather at the driver or framework level. Up to these (the driver, the OR Mapping framework, servlet engine, Java EE container) to furnish, under the covers, a failoverproof environment.

Do all drivers and Java frameworks are failoverproof?
You wish! The reality is that very few JDBC drivers or Java frameworks furnish true/reliable connection or transaction failover mechanisms.

From database access point of view, what does it take for a JDBC Driver or a Java framework to be failoverproof?
First of all, a JDBC driver or a Java EE ccontainer by itself cannot furnish a complete failoverproof environment, it more importantly requires the resource manager, in this case the RDBMS to be failoverproof as well. In the Oracle RDBMS case, instance/node failover as well as scalability is furnished by the RAC framework.

What is RAC?
An Oracle database is managed by a database instance which is made of a shared memory (a.k.a. SGA)and a set of database server processes. A database is usually accessed and managed by a single instance. However, an Oracle database can also be concurrently accessed and managed by multiple instances up to 64 nodes and beyond; this technology is known as Real Application Clusters (RAC).

How Does RAC Furnish Failover?
Starting with release 10g, RAC generates events that indicate the health or status of each RAC components including SERVICE, SERVICE_MEMBER,DATABASE, INSTANCE, NODE, ASM, and SRV_PRECONNECT.
The possible status are: UP, DOWN, NOT_RESTARTING, PRECONN_UP, PRECON_DOWN, and UNKNOWN.

Example of events can be: "Instance1 UP", "Node2 Down".

RAC furnishes failover by design in the sense that when a service/instance/node fails, a well written application can be redirected to the surviving node/instance provided these furnish the same service and proceed against the same database.

How Does JDBC Leverages RAC Failover?

The Oracle JDBC 10g drivers, more specifically it's connection cache (a.k.a. Implicit Connection Cache) leverages RAC by subscribing to the following events and status (as described in RAC documentation and in chapter 7 of my book):

Service Up: The connection pool starts establishing connections in small batches to the newly added service.
Instance (of Service) Up: The connection pool gradually releases idle connections associated with existing instances and reallocates these onto the new instance.
Instance (of Service) Down: The connections associated with theinstance are aborted and cleaned up, leaving the connection pool with sound and valid connections.
Node Down: The connections associated with the instance are aborted and cleaned up, leaving the connection pool with good connections.

But to be reliable, these events must be propagated to interested parties as fast as possible because the timeout mechanisms(tcp_keepalive, tcp_ip_interval, and so on) are unreliable and may take a long (tens of minutes) to indefinite time to be kick-in.
Orale furnishes ONS (Orale Notification Services) and Advanced Queue as publish/subscribe and predictable notification mechanisms which detects and propagates quasi-instantaneously (sub-seconds) those events to components that have subscribed to these mechanisms.

Setting up JDBC for Failover

Set up a multinstance Oracle Database 10g RAC database (see RAC documentation).
Virtualize the database host through a service name (see JDBC URL in chapter 7 of my book).
Configure ONS on each RAC server node (see the RAC Administrator Guide or chapter 7 in my book).
Configure ONS on each client node (10g Release 1) or use simpler remote subscription (10g Release 2). Ensure ons.jar file is in the CLASSPATH then programmatically set the ONS configuration string for remote ONS subscription at the data source level (unfortunately this cannot yet be set through system property): ods.setONSConfiguration("nodes=node1:4200,node2:4200"); The Java virtual machine (JVM) in which the JDBC driver is running must have oracle.ons.oraclehome set to point to ORACLE_HOME -Doracle.ons.oraclehome=
Enable the Connection Cache and Fast Connection Failover through system property: -Doracle.jdbc.FastConnectionFailover = true Alternatively, the Connection Cache and Fast Connection Failover can be enabled programmatically using OracleDataSource properties: ods.setConnectionCachingEnabled(true); ods.setFastConnectionFailoverEnabled(true);

Oracle JDBC: Handling of DOWN events (Under the covers) Upon the notification of Service Down event, a worker thread (one per pool instance) processes the event in two passes:First pass: Connections are marked as down first, to efficiently disable bad connectionsSecond pass: Aborts and removes connections that are marked as downNote: active connections that may be in the middle of a transaction receive a SQLException instantly

Oracle JDBC: Hanlding of UP Events (under the covers)
A Service UP event initiates connections to be load balanced to all active RAC instances Connection creation depends on Listener’s placement of connections. Starting with 10g release 2,load balancing advisory events enabled Runtime Connection Load Balancing (covered in chapter 7 of my book).

Object-relational Mapping frameworks as well as any Java EE containers may either leverage Oracle JDBC (bypassing their own connection pool) or subscribe diretly to RAC events using the ONS APIs and processing these (i.e., handle connection retry). To my knowledge, only Oracle's Java EE containers (OC4J) has integrated Fast Connection Failover and ONS at datasource level.

How does Oracle JDBC Fast Connection Failover (FCF) compares with TAF?
Fast Connection Fail-over and TAF differ from each other in the followingways:

Driver-type dependency: TAF is in fact a OCI failover mechanism exposed to Java through JDBC-OCI. FCF is driver-type independent (i.e., works for both JDBC-Thin and JDBC-OCI).
Application-Level Connection Retries: FCF supports application-level connection retries (i.e., the application may retry the connection or rethrow the exception). TAF on the other hand retries connection transparently at the OCI/Net out of the control ofthe application or Java framework.
Integration with the Connection Cache: FCF is integrated with the Implicit Connection Cache and invalidates failed connections automatically in the cache. TAF on the other hand works on a per-connection basis at the network level; it does not notify the connection cache of failures.
Load Balancing: unlike TAF, FCF and runtime connection load balancing (RCLB) support UP event load-balancing of connections and runtime distribution of work across active RAC instances.
Transaction Management: FCF automatically rolls back in-flight transations; TAF, on the other hand, requires the application to roll back the transaction and send an acknowledgment to TAF to proceed with the failover.
TAF does not protect or fail-over codes that have server-side states such as Java or PL/SQL stored procedures; however, the application can register a callback function that will be called upon failure to reestablish the session states.

// register TAF callback function “cbk”

((OracleConnection) conn).registerTAFCallback(cbk,msg);

Voila, you now have a Java plateform with connection pool failover, on top of which you can code and deploy JDBC applications or Java EE components.

For more details, see chapter 7 of my book: http://db360.blogspot.com/2006/08/oracle-database-programming-using-java_01.html

Get Bolder with LOBs Manipulation in Java/JDBC

2006-11-29T11:15:00.000-08:00

The Oracle database supports very large CLOB/BLOB columns/data however, the average Java/JDBC applications manipulates mostly medium size LOBs of tens/hundreds Kbytes in size.

WTF with LOB Programming in Java/JDBC

If you have ever dealt with LOBs in Java/JDBC applications, you know that it is not always straightforward, and sometime difficult to program cleanly. Let's take a closer look at the currently available approaches with Orace JDBC.

3 Ways to Slice and Dice LOBs

As of 10g Release 2, Oracle JDBC furnishes 3 ways of accessing and manipulating LOBs including:Oracle's LOB methods, optimized standard LOB methods, and LONG Streaming. Which one to use and when?In this article, i want to shed some light so as to make the reader more comfortable, bolder, when using CLOBs, BLOBs, and BFILE in Java/JDBC applications.

Oracle LOB Approach
LOB manipulation is done in two steps

Step#1: Manipulate the LOB Locator

Create or Retrieve, Insert, and Update the LOB locator using createTemporary, getEmptyCLOB, getEmptyBLOB methods. setBlob, setBLOB, setClob, setCLOB, getBlob, getBLOB, getClob, updateBlob, updateClob, setBLOB, setBLOBATName, setBFILE, updateBLOB, …, and updateCLOB methods.

ResultSet rs = stmt.executeQuery(“SELECT blobcol from XobTab”;
...
BLOB BlobLoc = ((OracleResultSet)rs).getBLOB(1);

Step#2: Manipulate the LOB Content

Write LOB content using getString, getSubString, and getBytes, setStrings, and putBytes methods.

tempLob.putBytes (1, bytarr);
PreparedStatement pstmt = conn.prepareStatement ( "insert into XobTab values (?)”;
((OraclePreparedStatement)ps).setBLOB (1, tempLob);

Read the LOB contents and Stream the content out, using getBinaryStream, get-String, getCharacterStream, and getAsciiStream.

byte[] bytarr = new byte[20]; // 20 bytes chunk
BLOB LOBLoc = ps.getBLOB (1);
InputStream is = LOBLoc.getBinaryStream();
while ((length = stream.read(bytarr)) != -1)
{
// process the chunk
bytarr = new byte[10];
}
is.close();

In pre-10g releases, JDBC-Thin used the PL/SQL DBMS_LOB package for LOB operations; in 10g, it uses a more nativead faster protocol however, certain operations such as hasPattern and isSubLob are still done via the dbms_lob package.

Standard and Optimized LOB Approach

For LOBs of size less than 2 gigabytes, LOB manipulation has been simplified especially in 10g Release 2) by removing the need to explicitly manage the locator. The standard methods getBytes, getBinaryStream, getString, getCharacterStream, getAsciiStream setBytes, setBinaryStream, setString, setCharacterStream, and setAsciiStream have been extended to take BLOB, CLOB and BFILE columns as parameters. This approach simplifies LOB programming; under the covers, the driver does the right thing (i.e., taking care of the locator, etc).

Example: Strings or byte arrays can be stored/read into/from the database, by simply using setString or setBytes, getString or getBytes.

LONG Streaming Approach

LONG streaming is furnished to help faster retrieval of data from a LOB without all the LOB overhead (i.e., retrieving the lob locators and going through the lob protocol), which can be very costly for small data. All you need to do is define the column as a LONG, then the driver takes care of LOB to LONG conversion and vice versa. This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions: LONG streaming always starts at the beginning of the LOB (i.e., you cannot position or start anywhere); you may read as much data as you wish but only a single pass is allowed (i.e., you cannot read the stream twice).
Using this approach, CLOBs can be declared as LONGVARCHAR using defineColumnType. and streamed using getAsciiStream by redefining it a as
(OracleStatement)stmt.defineColumnType(,Types.LONGVARCHAR);

Similarly BLOBs columns can be declared as LONGVARBINARY using defineColumnType then streamed using getBinaryStream
(OracleStatement)stmt.defineColumnType(, Types.LONGVARBINARY);

Example:
// Pretend that these columns are of LONG and LONG RAW types
(OracleStatement) stmt.defineColumnType(1,Types.LONGVARBINARY);
(OracleStatement)stmt.defineColumnType(2,Types.LONGVARCHAR);
...
ResultSet rs = stmt.executeQuery(“select LOBCOL from XOBTab”);
// Retrieving a Clob with getString");
ocrs.setCommand ("SELECT lobcol FROM XOBTab");
ocrs.execute ();
while (ocrs.next ())
{
bytarr = stmt.getString (1));
}
ocrs.close ();

// Retrieving a Blob with getBytes");
ocrs.execute ();
while (ocrs.next ())
{
ocrs.getBytes (2).length);
}

Get More

This article, excerpted from my book, summarizes the various approaches to LOB manipulation using Oracle JDBC drivers. You can find more details and code snippets in either the Oracle JDBC 10g R2 doc or in chapter 8 of my book.

WTF with SQLJ

2006-11-05T22:45:00.000-08:00

What is SQLJ

SQLJ allows embedding SQL statements in Java, similar to Pro*C (and all precompilable languages). A SQLJ program is pre-compiled into JDBC (by a translator) and therefore represents a simpler and more productive alternatve to straight JDBC programming in client-tier, middle-tier and database-tier. A runtime mechanism is usually needed at execution time.

#sql iter = {SELECT ENAME FROM EMP where EMPNO ='7934'};
if (iter.next()) { System.out.println("Employee# 7934 is " + iter.ENAME()); }

Oracle SQLJ Status

SQLJ has been briefly de-supported in early releases of the Oracle Database 10g (namely 10.1.0.1, 10.1.0.2, and 10.1.0.3) but is fully re-supported in the Oracle Database 10.1.0.4 and up.

SQLJ Benefits
Many benefits including simplicity features, translation-time type checking, interoperability with JDBC, Oracle extensions to SQLJ, SQLJ Stored Procedures, and Runtime-free code generation.
Simplicity Features (Compared with JDBC)

Embedded host variables (JDBC uses parameter markers).
No need to cast data types.
No need to programmatically register output parameters.
No need to explicitly handle the NULL value (See JDBC support for Null in Part II of my book).
Support for SELECT INTO statements.
Support for PL/SQL anonymous blocks makes it easy to embed PL/SQL in Java.
No need for String concatenation for long SQL statements.

Translation-Time Type Checking

Syntax checking of SQLJ constructs: a SQLJ parser checks the grammar, according to the SQLJ language specification.
Syntax checking of Java instructions: a Java parser (invoked under the covers) checks the syntax of Java host variables and expressions within SQLJ executable statements.
Semantics checking: Depending on the option settings, this includes: Syntax of SQL statements by a SQL semantics checker; SQL offline parser; Validation of schema objects (i.e., tables name, columns name); Validation of the mapping between Java types and SQL types.

Interoperability with JDBC

JDBC connections can be used by SQLJ as a connection context and vice versa.
JDBC ResultSet can be passed to a SQLJ application as a SQLJ Iterator, and vice versa.

Oracle Extensions

Support for Dynamic SQL: Oracle extends it's implementation beyond the specification which only allows static SQL statements, to allow constructing SQL statements at runtime, similarly to JDBC.

String table = "new_Dept"; #sql { INSERT INTO :{table :: dept} VALUES (:x, :y, :z) };

Compile-Time Performane Optimizations

Row prefetching
SQLJ statement caching
Update batching
Column type(s) definition
Parameter size definition

SQLJ Stored Procedures

The database-resident Java VM (covered in depth in Part I of my book) embeds a SQLJ translator and runtime which allow you to load, pre-compile, compile and execute SQLJ source programs direcly in the database.
...

dbprint("Hello from SQLJ in the Database!");

Date today;

#sql {select sysdate into :today from dual};

dbprint("Today is " + today);

...

static void dbprint(String s) {

try {

#sql { call dbms_output.put_line(:s)

};

} catch (SQLException e) {} }

Runtime-free code generation

SQLJ traditionally requires a runtime (implemenrtor specific). Oracle'simplementation allows you to generate runtime-free code (i.e., oure JDBC).

Advanced/Powerful SQLJ Concepts

SQLJ is more than a mere high-level JDBC code; the language/specification defines concepts such as connection contexts, execution context, iteratorsand expressions that are more advanced than their JDBC counterpart (when they exis).
A SQLJ program is a Java/JDBC code with SQLJ declaration statements,and SQLJ executable statements.

SQLJ declaration statements
This category contains import statements, connection contexts declaration,execution contexts declarations, and iterators declarations.

Excutable Statements

Executable statements can be grouped into Statementclauses and Assignment clauses.
Statement Clause:

#sql [optional SQLJ space] { Statement clause };

Assignment clauses

Query clause

#sql [conctx, exectx] iter = { SQLJ clause };

Function clause

#sql result = { VALUES ( ())};

Iterator conversion clause

#sql iter = { CAST :rset };

Expressions

Expresions are another powerful SQLJ mechanism that allows you to exploit Java expressions in SQLJ statements including: host expressions,context expressions and result expressions.

#sql [connctx_exp, execctx_exp] result_exp = { SQL with host expression };

Accessing Oracle SQL and PL/SQL Data Types
SQL and PL/SQL data are manipulated in SQLJ through SQLJ host variables/expressions, result expressions, and iterator accessors.

Assume a table with an XMLType column, created as follows:

create xmltype_tbl (xmltype_col SYS.XMLType);

insert into xmltype_tbl values(SYS.XMLType('tom'));

insert into xmltype_tbl values(SYS.XMLType('jon'));

It can be manipulated using the following code fragments:

import oracle.xdb.XMLType;

...

#sql iter={select xmltype_col from xmltype_tbl;}

while(iter.next()) {

System.out.println(iter.xmltype_col().getStringVal());

}

while (iter.next())

{

System.out.println(iter.xmltype_col.getClobVal());

}

Learn more about SQLJ and Oracle implementation and code samples

Manipulating Tabular Data using JDBC RowSet API

2006-09-03T13:08:00.000-07:00

If you Google "Tabular Data", you will get more than 2 millions hits, which is an illustration of the pervasive and broad scope of the concept. So what are tabular data? The following proposal might not be the most well thought definition but tabular data can be defined as data organized or presented as items/columns and records/rows that can be stored in (or retrieved from) various persistence repositores including file system, spreadsheet, relational tables, XML documents and so on.

This blog will introduce the goals the JSR-114 RowSet, briefly describe the various models and furnish pointers to get further details and code samples.

Why JDBC RowSet?

When dealing with tabular data, a number of Java SE/EE and Web Services requirements (such as JavaBean properties, XML documents properties) that are not addressed by the existing JDBC interfaces.

The RowSet specification (http://www.jcp.org/en/jsr/detail?id=114) addresses the following requirements and shortcomings of JDBC specifications:

Make query results scrollable and updatable without relying on the underlying JDBC driver
Manipulate data (e.g., filtering and joining) and make changes while disconnected from data sources
Associate properties and event listeners to query results to facilitate manipulation by tools
Consume data results in chunks and ship result set to thin clients, handheld devices, and PDAs which are most of the time not connected and do not have a full-fledged JDBC driver
Convert data results to or from XML documents.

The RowSets Models

The RowSet Specification (JSR-114) formalizes and completes the concept of RowSet introduced in JDBC 2.0. It defines five standard JDBC RowSet interfaces including: JdbcRowSet, CachedRowSet, WebRowSet, JoinRowSet, and FilteredRowSet (see Figure 1 below).

The RowSet Hierarchy

The base JDBC RowSet interface (javax.sql.Rowset) encapsulate/extend ResultSets (java.sql.ResultSet) through a set of properties including: datasource, datasourcename, url, username, password, typeMap, maxFiedSize, maxRows, queryTimeout, fetchsize, transactionisolation, escapeProcessing, command, concurrency, readOnly, fetchDirection, as well as events and an event listener mechanism.
In general, each rowset model is populated from a repository using the repositorey-specific APi (i.e., JDBC for RDBMS), then manipulated using the towset-model-specific methods.
In the rest of this blog, I'll give you a brief description of each model excerpted from my book (a full coverage of the RowSet API is beyond the scope of a blog).

JDBCRowSet

The JDBCRowSet object is a JavaBean equivalent of ResultSet; as such, it maintains a connection to its datasource, has a set of properties,and a listener notification mechanism. The contents are identical to those of a ResultSet object, and it inherits all of the result set methods for rows manipulation, cursor movement, and data manipulation.
The OracleJDBCRowSet class implements the javax.sql.rowset.JdbcRowSet interface.

CahedRowSet

The CachedRowSet object represents disconnected RowSet such that the row data and metadata can persist beyond the connection. When the connection to the database is restored, any modifications to the RowSet are propagated back to the database. It caches its rows in memory and allows operating on these without being connected to the datasource, resulting in increased scalability. In addition, it turns any result set object (even the read-only ones) into scrollable and updatable rowsets.
The OracleCachedRowSet class implements the javax.sql.rowset.CachedRowSet interface.

WebRowSet

A WebRowSet object is a CachedRowSet object, augmented with a default WebRowSetXmlReader, which reads an XML document into the rowset, and a default WebRowSetXmlWriter, which writes the rowset as an XML document.

You can:

Create a WebRowSet object and dump its contents to an XML document.
Read an XML document into a WebRowSet object.
Make changes to a WebRowSet object (Insert, Update, Delete rows) and synchronize it back to the datasource.

The W3C XML schema for WebRowSet is defined by Sun, and available at http://java.sun.com/xml/ns/jdbc/webrowset.xsd.

The oracle.jdbc.rowset.OracleWebRowSet class implements the javax.sql.rowset.WebRowSet interface. The oracle.jdbc.rowset.OracleWebRowSetXmlWriter class extends the javax.sql.rowset.spi.XmlWriter.

FilteredRowSet

A FilteredRowSet object simulates a SQL WHERE clause by applying user-defined filtering criteria on rows in a disconnected rowset; in other words, it returns/retrieves only rows that meet the defined criteria, and inserts/updates only rows that meet the constraints defined in the filter(s). The constraints are defined within a predicate class then assign this predicate to the FilteredRowSet object; as a result, the constraints defined in the predicate class will be evaluated against each row in the rowset object.In order to use filtered rowsets the JDBC applications must:

Define and implement the Predicate interface.
Create an instance of FilteredRowSet object and set properties.
Populate the FilteredRowSet.
Set/enable filters.
Retrieve the filtered rows.

The oracle.jdbc.rowset.OracleFileteredRowSet class implements the javax.sql.rowset.FilteredRowSet; in addition the oracle.jdbc.rowset.OraclePredicate interface inherits from the javax.sql.rowset.Predicate.

JoinRowSet

The JoinRowSet object represents a SQL JOIN operation between disconnected RowSet objects.A JoinRowSet object combines data from multiple rowsets. In order to use Join rowsets, the JDBC applications must:

Create an empty instance of JoinRowSet object and set properties.
Create and add RowSets objects to the JoinRowSet.
Define the match column (similar to SQL matching columns).
Navigate and consume the JoinRowSet Object.

The oracle.jdbc.rowset.OracleJonRowSet class implements the javax.sql.rowset.JoinRowSet interface.

Further Details

The JSR-114 furnishes a reference implementation of each rowset type as part of Java/JDK 5.0, or as stand-alone Jars (i.e., rowsetjsr114.jar) that can be used on top of any standard JDBC driver. Oracle implements JSR-114 specification as part of the JDBC drivers (i.e., ojdbc14.jar).

For more details, see:

the specification
Sun's Totorial
and my book which contains the detailled description of Oracle's implementation of each RowSet model and working code samples.

Oracle Database Programming using Java and Web Services

2006-08-01T17:34:00.000-07:00

This book is about Oracle database programming using Java and Web services. Foreword by Tom Kyte (html) (pdf)

This book is for database developers (DBA, PL/SQL developers, data architects), Java/JDBC/SQLJ developers, and Web services /SOA assemblers.

Through its 1085 pages, it furnishes a complete coverage of the following topics:

Java in the database: stored procedures as database programming model (rationale, obstacles to their adoption, languages for stores procedures, PL/SQL or Java?). Under the hood of the Java runtime in the database (design goals, architecture, memory management, security, threading, Java execution in the database, native compilation, etc). Examples of typical and atypical applications (SAP callout, JDBC callout, EJB callout, HTTP Callout), Excel-like expressions parser) using Java in the database. Mapping SQL and PL/SQL types to/from Java. Declaring Java stored procedures (Call Specs). Experimental examples of running JACL, JYTHON, SCHEME and GROOVY in the database using the Java runtime.
Oracle JDBC 10g: In depth coverage of the JDBC API specifications and Oracle implementation. Connection and connection services (Implicit Connection Caching). High-Availability in RAC environments (ONS, Fast Connection Failover, TAF) and scalability (Runtime Connection Load balancing in RAC environments). Statements and Oracle extensions (statement caching, DML batching). Manipulating Oracle data types with JDBC. Practical coverage of Result Sets and the various RowSet models (JSR-114) . Proxy Authentication, SSL encryption, end-to-end tracing, Logging, local/global Transactions, Security and best practices.
Oracle SQLJ 10g: Concise coverage of the SQLJ Language/API and Oracle implementation. Manipulating SQL and PL/SQL data types with SQLJ and contrasting with JDBC (interoperability, differences). Comprehensive coverage of Oracle datatypes mapping, and best practices.
JPublisher in Action: a practical coverage of a versatile tool that complements JDBC (and SQLJ) for accessing the Oracle database from Java. Automatic wrapping of PL/SQL packages as well as mapping complex/user-defined types to Java.
Database Web services: Introduction to Web services and SOA for DBAs. How to turn your Oracle database into a Web service consumer (call-out external Web services from within the database using SQL, PL/SQL, Java). How to turn your database into a Web services provider (exposing PL/SQL package into Web service as wellas SQL Query, AQ quques and operations, SQL Queries and DML, and Java in the database --using few mouse clik or a command line utility. How to add SOA services to database web services.

Here is the first chapter, the Table Of Content, the code samples at OTN and at the publisher's web site, and a reader's review.

Enjoy and pass on the buzz.

Get the Best Performance out of Java in the Database

2006-07-11T21:17:00.000-07:00

When you run Java in the database, what are the various execution modes? Are you taking the most efficient approach? In this post I'll touch on the various execution modes for running Java in the Oracle database.

As we all know, the best execution performance for Java is achieved via binary executables obtained through compilation (JIT compilers and/or static compilers). The Oracle Database currently furnishes a static native Java compiler known as NCOMP, which must be explictly invoked ahead of the deployment.

There are currently three execution modes: (i) fully interpret, (ii) NCOMPed System classes, and (iii) fully NCOMPed (or NCOMPed User Classes).

Fully Interpreted (System and User Classes)
In this mode, both system classes and the user classes, run interpreted. During the database installation, the non-compiled Java VM system classes are installed. By default, user classes also run interpreted and most customers stick to the default mode and are happy with the level of performance obtained, mostly because, as explained in previous posts and in my book, the combination of Java and SQL run faster in the database (even fully interpreted). However, this is not the most efficient execution mode.

NCOMPed System Classes and Interpreted User Classes
In this mode, the system classes run natively compiled while user classes run interpreted. The natively compiled system classes (NCOMP libraries) are installed explictly following the default database install, from the companion CD. Without further explcit action, the user classes run interpreted on top of the natively compiled system classes. The overall peformance is greater than the previous mode (i.e., fully interpreted).

Fully NCOMPed (System and User Classes)
In this mode, the system and user classes run natively compiled. User NCOMP requires an explicit action from the developers. It comprises three commands/steps: 'NCOMP', 'STATUSNC', and 'DEPLOYNC'.

NCOMP

This command takes Java bytecodes that have been resolved and verified during the loading phase (loadjava) and can therefore be trusted and expected to execute correctly, then does three things:

gathers class info, generates a script that will drive the NCOMPing process, and produces the list of NEED_NCOMPNG orALREADY_NCOMPED methods
Pauses for the Java-to-C translation: C compilation and linkage of each package involved into platform-specific DLLs
Deployment/installation of the resulting DLL (unless -noDeploy) is specified

ncomp [ options ] class_designation_file
-user -u username/password [@database_url]
[-load]
[-projectDir -d project_directory]
[-force]
[-lightweightDeployment]
[-noDeploy]
[-outputJarFile -o jar_filename]
[-thin]
[-oci -oci8]
[-update]
[-verbose]

STATUSNC

This command checks whether the JAR files, ZIP files, or CLASSES files are NCOMPed or not.
statusnc [ options ] .jar .zip
.classes.
-user /[@database]
[-output -o ]
[-projectDir -d ]
[-thin]
[-oci -oci8]

DEPLOYNC

This command deployd the natively compiled deployment JAR file, to the database.
deploync [options] deployment.jar
-user -u username/password [@database_url]
[-projectDir -d project_directory]
[-thin]
[-oci -oci8]

Conclusions

NCOMP may give an order of magnitude speed up (i.e., 10 times) compared to interpreted execution. But as usual, there is no absolute figure; it all depends on what exactly you are NCOMPing as NCOMP speeds up only Java code, not the embedded SQL.

See more details in the Java Developer's Guide of the Oracle Database documentation nd in my book.

Invoking External Functionality/System from within the Database

2006-06-21T22:30:00.000-07:00

Looking at the picture at the following URL,
http://photos1.blogger.com/blogger/4310/2356/1600/360_DB_Prog.2.jpg, i have touched through my previous posts on the first three points, including:
(1) the user-defined database functinality itself,
(2)calling database functionality from within the database,
and (3) calling user-defined database functionality from outside.

In this post, I'll touch on the fourth point (4): invoking external functionality from within the database.
The requirements range from alerting or notifying external systems based on data value/state, validating or obtaining credit card authorization, querying/updating remote database, invoking a rate/tax engine, obtaining dynamic data, invalidating middle-tier data cache, messaging across tiers, executing OS commands, and so on.

The Java runtime in the database allows the use of various Java-based mechanisms and protocols, depending on the design/implementation of the target external systems as highlighted in the following examples.

- Calling out external RMI Servers allow invoking SAP System from within the database
through SAP JCO.

- Implementing an HTTP-callout trigger using the Java-based HTTPClient for requesting a
Web component (i.e., JavaServer Page) to invalidate a middle-tier data cache. Similarly a
credit card authorization and validation can be implemented using HTTPS Call-out.

- Alerting/Notifying external systems via email, using Java-based mailer in the database.

- Implementing JDBC-Callout to issue queries and DML against remote non-Oracle database(s)
using a 3rd party pure Java JDBC driver.

- Messaging across tiers using JMS over Streams AQ by invoking Message-Driven Beans in the
middle-tier.

- Web Services Callout allows retrieving dynamic data from external Web services or invoking
remote operations through Web services mechanisms.

- Invoking OS commands using Runtime.exec('').

- And so on ...

The beauty of embedding a full fledged Java VM in the database is that even when a protocol or invocation mechanism is not available by default, you can reuse existing Java library that implements the mechanism or protocol in question. The complete details of the examples and case studies with the code samples are available in my soon-to-be-released book http://www.amazon.com/gp/product/1555583296.

Invoking User-Defined Database Functionality from within and outside the database

2006-05-29T23:03:00.000-07:00

In my previous post i touched on the fact when database access is significant, it is often more efficient to implement and run user-defined functionality directly in the database.
In his dated but still good report on distributed computing economics (i.e., Grid Computing) Jim Gray's recommends to "put computing as close to the data as possible in order to avoid expensive network traffic". See ftp://ftp.research.microsoft.com/pub/tr/TR-2003-24.pdf.
This is true for most RDBMS; as far as the Oracle database is concerned, this is true for PL/SQL, Java, and Web services (i.e., Database Web Services).

Now, assume you have built user-defined database functionality, how do you invoke it from within and from outside the database? With the Oracle database, there are three cases:

1/Invoking PL/SQL based user-defined database funtionality

Assume Func_nn(),Proc_nn(),Pkg_xx.Func_nn(), and Pkg_xx.Proc_nn()

Invocation from SQL, PL/SQL, and SQL*Plus
SQL>CALL Func_nn();
SQL>CALL Pkg_xx.Func_nn();
SQL>CALL Pkg_xx_Proc_nn();
SQL>Begin Proc_nn() End/
Invocation from Java (J2EE, JDBC, Java-in-the-database): same as Java-based functionality see below.

2/ Invoking Java-based user-defined database functionality

Invocation from Java-in-the-database: A user-defined Java functionality is transparently invoked by another Java class running in the database, provided the invoker has the proper privileges and authorization.
Call Specifications: Java-based functionality is made available/known to SQL (therefore to PL/SQL, and JDBC) through a PL/SQL Wrapper called Call Spec. There exist top-level Call Spec,PL/SQL-packaged Call Spec, and object type Call Spec. Call Spec must map parameters and retur values to/from SQL or PL/SQL types to Java (and JDBC supported) types; such mapping can be challenging unless you use a tool (i.e., JPublisher).
Invocation from SQL, PL/SQL and SQL*Plus: similar to PL/SQL-based functionality (see above).
Invocation from Java/JDBC/J2EE using the SQL 92 Syntax CallableStatement cstmt = conn.prepareCall(“{? = call func(?, ?)}”);
Invocation from Java/JDBC/J2EE using the PL/SQL Anonymous Block syntax CallableStatement cstmt = conn.prepareCall(“begin ? := func(?, ?); end;”); Note: with JDBC programming, Output parameter(s) must register the data types and the value(s) of Input parameter(s) must be set. However,not all SQL database types are supported.
Invocation from Java/JDBC/J2EE using Client-Stub: a JPublisher-generated client-side stub allows callingmethods on Java classes in the database, without the provision of a user-definedCall Spec (see above), and without their limitations (of Call Specs).
Invocation from OJVMJAVA: the Oracle database furnishes an interactive command-lineutility called OJVMJAVA, which lets you run Java classes in the database (i.e., classes with a public static main() method) from the client machine.

3/ Invoking User-defined Database Functionality as Web Services Simply put, Web services consist of a service client and service provider exchanging SOAP-based XML messages using Web protocols. User-defined database functionality can be published as Web Services and invoked by by standard-compliant Web services clients. However, beyond SOAP, WSDL, and UDDI, there are more to Web services. The Oracle implementation of Database Web services benefit from the Quality of Services furnished by the Oracle AS SOA stack. The steps for turning database functionality as Web Services is described in Database Web Services portal (linked from this blog).

All these approaches are fully decribed in my book http://www.amazon.com/gp/product/1555583296/

In my next post I'll touch on invoking external functionality from within the database.

User-Defined Database Functionality

2006-05-03T23:00:00.000-07:00

Beyond the out-of-the-box built-in RDBMS functionality, you often need to extend the capabilities of your database by developing your own database-resident functionality, or data logic.

The requirements for user-defined database functionality range from performance (see free-sample chapter in previous posts), integration with existing functionality, security, ability to share data logic (across all clients applications), cost reduction, skill reuse, deployment/topology choices (see dilemma below), and many other reasons.

The following applications are candidates for being implemented as user-defined database functionality:
- Traditional data logic: SQL statements grouping
- Content/Repository management
- In-database Message Queuing and propagation
- Reporting functionality: generate pdf, excel, and XML documents from relational data
- Data-based Alert, and Notification
- Expressions Parsers (see code fragment below)
- Key generation, MD5 CRC checksum
- Multi-media data processing: store, retrieve, transform/convert images (GIF, PNG, JPEG), audio, video, or heterogeneous media data. Alternatively you can just use Oracle interMedia, a packaged multi-media processing framework.
- Text data management: index, search, and analyze texts stored in RDBMS. Alternatively you can just use Oracle Text, a packaged text data processing framework.

However, an RDBMS is not an application server, you must make the determination that the user-defined functionality pertains indeed to the RDBMS.
Examples:
- tax and rate engines, which take input values and return tax amounts or rates, are better implemented as EJBs and do not belong to the RDBMS.
- a JavaServer Pages that generate user-interface rendered in a browser does not belong to the RDBMS.
As discussed in many forums, making the determination is not always a clear cut.
Examples:
- a frequent dilemma is the choice between caching data in the middle-tier (i.e., CMP Beans, POJO) or pushing the processing closer to the data (i.e., stored procedures)? See http://www.theserverside.com/discussions/thread.tss?thread_id=2613
- a server-side UI can be produced using either a JavaServer Pages (that is deployed in the middle-tier) or the database-resident Oracle Application Express (formerly known as HTML DB), or use the HTP package, directly in the database.

The mechanisms and programming models for building user-defined database functionality
range from proprietary procedural languages (e.g., PL/SQL, T-SQL), standard languages (e.g., Java), de facto standard languages (C#, VB), messaging systems for database messaging, job/task scheduler, and so on.

The Oracle database furnishes PL/SQL, Java, Streams AQ, DBMS_SCHEDULER and so on for building user-defined functionality.
There are tons of PL/SQL examples, see http://asktom.oracle.com.
Java in the database is an open alternative to PL/SQL for implementing advanced functionality such as a domain-specific language parser. Imagine an Excel expression evaluator/parser that can be used to generate formulas representing complex interrow calculations in the database and dynamically generating the corresponding SQL.

Mini Parser code fragment
public class Mini parser {
package example;
import example.parser.parser;
import example.formula.Expr;
import java.io.StringReader;

public static String eval(String spreadsheetExpr) throws Exception {
parser p =
new parser(new StringReader(spreadsheetExpr));
Expr expr = (Expr)p.parse().value;
return expr.eval();
}

public static void main(String[] args) throws Exception {
System.out.println(eval(args[0]));
}
}

Then the following query returns 15 which is not the generated SQL, but gives you an idea of how the expression evaluator works:

select eval('=sum(1,2,3,4,5)') from dual;

The complete mini-parser source code as well as the corresponding CUP and LEX descriptions are available in chapter four of my book (see link on this blog).

In the next posting, I'll elaborate on how to make the user-defined database functionality available for invocation by other database components as well as external components.

Database Functionality: Beyond Data Storage

2006-04-24T22:08:00.000-07:00

Ain't no enterprise applications without a database (RDBMS), still some of us have the tendency to think of databases as dumb data repositories or mere storage engines for Relational/Structured, Binary/MultiMedia, Documents/Unstructured and Semi-Structured/Messages data. If so, as Henri Bergson wrote "The eye can only see what the mind is prepared to accept" -- iow, you cannot see and utilize all their possibilities.

An exhaustive coverage of database functionality, that you get out-of-the-box, and those that you can build yourself is beyond the scope of a blog -- but range from OLTP to OLAP/Business Intelligence, from Messaging to Content Management, and much more.
In the next posting I'll touch on programming models that you can use to build database functionality, also known as data logic.

What 360 degrees Database Programming Means

2006-04-16T16:55:00.000-07:00

As some of you have asked me privately -- don't be shy, post your comments :), what the h. does 360 degree database programming means? Well, 4 things that i will elaborate on in following posts: (1) the database functionality itself, (2) calling database functionality from within the database, (3) calling database functionality from outside (client, middle-tier, etc), and (4) calling external system/functionality from within the database.

Stored Procedure as Database Programming Model

2006-03-31T16:20:00.000-08:00

I thought the best way to trigger this blog is to discuss the rationales for stored procedures, the obstacles to their adoption, languages used for writing stored procedures, and the $100000 question of proprietary procedural languages such as PL/SQL versus open standards languages such as Java.
http://www.oracle.com/technology/books/pdfs/mensah_ch1.pdf
Enjoy and share your thoughts, Kuassi