modern database development oow2008 lucas jellema

Optimal Use of Oracle Database 10g and Oracle Database 11g for Modern Application DevelopmentLucas Jellema, Oracle Open World 2008, San Francisco

Optimal Use of Oracle Database 10g and Oracle Database 11g for Modern Application Development

Lucas Jellema AMIS, The Netherlands

Oracle Open World, San Francisco September 2008


What is Modern?

SOA

(operational) Analytics

event

XML

SAAS

S-Ox/Audit

Service

MultiMedia

Tera Byte

24/7

GlobalAsynchronous

Fuzzy

I18n (internationalization)

Business Rules

Data Integrity

Share/Collaboration

Enterprise 2.0

Message

OO DecouplingSecurity

PersonalizationScalability

B2B


What is Modern?

What becomes possible New functionality in software, new facilities in

infrastructure Support for Cloud Computing, Database Accelerator

What becomes affordable Promotion of features from Extra Cost Option to EE to SE

What becomes desirable Encapsulation of SQL, Service Orientation, Active Database

What becomes inevitable Sarbanes Oxley, 24/7 & global

What becomes visible Subquery Factoring, Instead of Trigger, Ref Cursors,

Flashback


Some valuable 11g examples‘Total recall’ – Flashback Data Archive

Makes Historical Data reliably available, whichin turn puts Flashback in the hands of application developers

Virtual Columns – organized redundancy and PL/SQL expression based constraints and indexes

Compound Trigger – clean work-around for mutating table violation

Result Cache – declarative caching of calculated results SQL Query Result Cache & PL/SQL Function Result Cache

DBMS_COMPARISON – compare and merge recordsContinuous Query Notification – alert listeners (PL/SQL

and external) of relevant data changes


Several often neglected featuresLarge Volume, Batch Processing

Single SQL statement bulk DML/ETL: Multi Table Insert, Merge, (pipelined) Table Functions

DML error loggingAnalytical functions

Compare between rows (look forward: lead, look back: lag)

Aggregation along several axes and levels in a single query

Sub-query factoring Implementing complex query requirements using clear,

managable and reusable piecesCase Expression

Real If-Then-Else in SQL and PL/SQL (forget DECODE)


Several often neglected features

‘new’ join syntax and outer join functionality Readability, functionality, standardization

‘Hierarchical Queries’ (especially with sys_connect_by_path and connect_by_root) traverse any network data structure

Object Types and Collections Improved integration between SQL and PL/SQL through

Bulk Operations, Table & Multiset operatorsRef Cursors

Make query results available without revealing the SQL for virtually any client (PL/SQL, C, .Net, Java, Ruby, PHP,

…)Instead-Of triggers on Views

Decouple DML from database design


Other gems worth mentioning

Virtual Private Database – foundation for SaaSDatabase Event Triggers (logon, create/drop/alter,

…)COLLECT operator – aggregate into collectionsdbms_job – key to asynchronous processingApplication Context – context sensitive bind-

parameter injection in frequently used queriesDBMS_APPLICATION_INFO – for instrumentationdbms_epg – publish PL/SQL package through URLUnpivot – turn multiple columns into multiple rows to

untangle unfortunate database designRollup – aggregate at multiple levels in a single

query


Statements with regard to Database

Database is nothing but a filing cabinet: Data goes in, data comes out; that’s it

We should strive for database independence We may want to replace the (relational) database

with ‘another data store’

Database portability must be maintained at any cost (vendor independence) We must be able to switch to another database

vendor Therefore we should only use functionality that is

available in all (mainstream) database products


Trends in the world…

Global – multi-timezone, multi-lingualMulti-tier – web user <> database userResuable functionality available through servicesSaaS – Software as a Service

Single instance of application and database Multiple remote organizations, many remote users

Operational BI – analytics on the flyFading Application boundaries vs. Enterprise DataStandardization – XML, SQLDesign Patterns – OO, Encapsulation, Decoupling,

Asynchronous, Dependency Injection, Reuse, …


Typical Architecture

J(2)EE Application Server

BatchBatch

Batch

Client

Enterprise Service Bus

DWH

?


What goes where?

What is the best place to do things? And what the very worst!

Criteria: Functionality Robustness Performance Ease of implementation

and maintenance Required skills Agility (License) Costs


Some proposed guidelines…

No SQL in application Or at the very least centralized

No HTML rendering in the databaseNo Data stored on the file system

Or anywhere outside the database• Except certain application meta-data

Database handles data access authorization

Client sets user identity, locale, time and dateDatabase is not accessed from outside the

firewall Bulk data manipulation in the database

SQL

?


Developing in the Oracle Database

Prepare to be reused, e.g. from web-applications, the SOA environment, batch programs and other clients Hide implementation details (shield from change) Publish reusable APIs

• Consider REF Cursor and XML

Leverage services offered outside the databaseGuarantee data integrity in the database

Do not rely on the clients to implement data rules

Instrument – indicate what is going on (and where)Avoid hard coded references and dependencies

Including SYSDATE and USER and references to supplied packages – use configurable parameters and wrappers


The client is always right

What time is it or at least what is the client’s timezone

What locale is it which language/regional settings apply

Where is it located what is the geographical location

Who is it web user’s identity <> any database user

Note: The database can only serve! Following the client’s lead


Single schema, light weight users

End user authenticates through Client with the Mid-tier

Mid-tier connects to RDBMS through pooled connections to fixed application schema

Mid-tier informs the database of the ‘client identifier’: the light-weight client-user-

identity, to be read with SYS_CONTEXT('USERENV‘,'CLIENT_IDENTIFIER')


Client

LDAP

APPLICATION_SCHEMAAPPLICATION_SCHEMA

USERENV


Set Client Identifier with Light Weight User

In Java using Oracle JDBC Driver

In PL/SQL

Or use a logon trigger to derive the client identifier

conn.setClientIdentifier("john.doe")

DBMS_SESSION.SET_IDENTIFIER( 'john.doe');

CREATE OR REPLACE TRIGGER logon_triggerAFTER LOGON ON DATABASEDECLARE uid VARCHAR2(64);BEGIN SELECT ora_login_user ||':' ||SYS_CONTEXT('USERENV', 'OS_USER') INTO uid FROM dual; dbms_session.set_identifier(uid);END logon_trigger;


Set Client Identifier with Light Weight User

In the database – SQL or PL/SQL – the current client identifier can be read like this:

The Client Identifier is also available in V$SESSION

as well as V$SQLAREADBMS_MONITOR.CLIENT_ID_TRACE_ENABLE

allows for tracing all activity of a CLIENT_IDENTIIER aka light-weight user Note: Oracle standard auditing does not use

CLIENT_IDENTIFIER

SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')

SELECT sid, client_identifier, service_nameFROM v_$session;


Current User Context

Client can set applicable Locale, Organization Id, Timezone in a similar way in an Application Context Or have them derived from the user’s profile that is

linked to the user identity

The overall user context is potentially used in many ways Set audit columns (no more USER) Set database (session) timezone Return messages in appropriate

language Enforce data authorization Expose Virtual Private Database

Attribute Value

OrgId 14

Locale fr_ca

Timezone +2.00

UserId ljellema


Virtual Private Database and SaaS

SaaS applications (pka ASP) have a single application & database instance, used by many remote users

Users share the application and the database, yet: Do not share (all) data May have specific application customizations Can define personal translations/renditions of boilerplate Have their own set of user preferences or settings

When asked for data from such tables as USER_PREFERENCES, FLEX_ITEMS and MESSAGES the database should apply the current user context to

return the appropriate slice of the data


SAAS – MultiOrg - Authorization


A

B

C

Customer CCustomer B

Customer C

setClientIdentifier

ORG USER

Web Application blindly queries a table for ‘all’ records

Based on User Identifier, (in current context) determine OrganizationOnly return records associated with that organization


Implementing ‘Context Awareness’

Oracle Enterprise Edition: Virtual Private Database aka Fine Grained Access Control and Row Level Security define policy and policy function to dynamically add to

where

Poor man’s VPD (Standard Edition/Express) Create a view for every context aware table

• Add where clause that filters based on context• Have the applications access data through views

Create DML triggers on each context aware table that• raise access violation exception when DML occurs outside of the

current user context (e.g. delete data owned by other organization)

• set context specific columns on newly inserted record – such as USER_ID and/or ORGANIZATION_ID


Fuzzy Search - From = to ≈

Many searches do not require only exact matchesFor example: we are looking for employees who

earn approximately 1500 and started in 1982 or thereabouts Exact match:select * from empwhere salary = 1500 AND extract (year from hiredate) = 1982

Everything in a bandwidth matches:where salary between 1000 and 2000AND extract (year from hiredate) between 1981 and 1983

However: the closer the better

Example: Oracle Text (score) and Google


Fuzzy Search - From = to ≈

Our criteria are: Say the salary should be as close as possible to

1500 and be at least between 1000 and 2000

The hiredate should ideally be in 1982; however, 1983 is somewhat acceptable (half the score for 1982)

We value a match on salary higher than one on hiredate (twice as important)

We want the top 5 scoring Employees

score

salary15000

1

1000 2000


Scoring on Salary


Scoring on Hiredate


Left Outer Join

Left Outer Join is used to state that: After taking every record from EMP Optionally add the sal_around_1500 score Optionally add the emp_hiredate_1982 score

Note how the ISO/ANSI SQL (Oracle 9i) join syntax allows continuous joining to multiple sources We can throw in right outer join and full outer join as

well Separate the Join condition and the Where clause


The top 5 of best matching records

select *

from ( select ename, score, ...

order

by score desc

)

where rownum < 6


Using the number_range_score function


Add the analysis of the score…

We appreciate the top5 list of scores

However, we would like to see why each employee scores as it does How are the scores of 2.2 and 2 for Miller and

Turner composed? What if we change the relative weights of the

search criteria?


Collecting Employee’s score per criteria

For every employee record, we will have multiple scores – one for each search criterium

To collect multiple records per single record in one query, we can use the COLLECT aggregator Collecting OBJECT TYPES into a TABLE OF OBJECT

TYPE


Collecting Employee’s score per criteria


Slightly nicer layout


Search results – top scoring employees


Add a new search criteria

We would prefer CLERKS but can live with ANALYSTS (60%) and SALESMEN (40%) This criteria weighs 1.5 compared

Query to add:


Fuzzy Search outcome with 3 criteria

- Salary close to 1500 and within 1000-2000- Hiredate 1982 (or less desirable) 1983- Job is CLERK, or ANALYST or SALESMAN


Some features used

Scalar Subquery select x, (select from) from

Collection of Object Type and Cast operator

Collect AggregatorLeft outer joinIn-line subquery:

select x, from (select x from order by) where …


RSS feeds are used to publish “events”


RSS Aggregator Archive

Access RSS Feed on the internetInterpret and transform the XML dataPublish the RSS Feed as queryable view in

the databaseCombine multiple RSS FeedsArchive the RSS Feeds in a central table –

updating changed items, inserting new onesPeriodically scan RSS Feeds and update

archiveComplete transaction even in the face of

constraint violations


The browser in your databaseIntroducing UTL_HTTP

≈select utl_http.request ( 'http://technology.amis.nl/blog/?feed=rss2' , null )from dual


Whatever the browser recives from a URL, the UTL_HTTP

receives too


Example UTL_HTTP code: processing response

SET serveroutput ON SIZE 40000

DECLARE pieces utl_http.html_pieces; n number default 0; resp utl_http.resp;BEGIN pieces := utl_http.request_pieces ( url => 'www.someurl.com' , max_pieces => 9999 , proxy => null ); for i in 1..pieces.count loop loop exit when pieces(i) is null; dbms_output.put_line(substr(pieces(i),1,255)); pieces(i):= substr(pieces(i),256); END LOOP; end loop;END;


Database can call out through UTL_HTTP

Application Server Servlet (intranet) WebService BPEL Process ESB Service These services in turn can do many things, such as

• Send email, send chat-message, access database, call external WebService, read/write file system, access EJB or .NET based services, contact FTP server, deal with SAP

Internet (though that is not a decoupled thing to do) RSS feed WebService Web Site

Note: HTTP Get requests can also be performed by:• dbms_xmlparser.parse(url)• HTTPURITYPE(url)• Oracle Text (index external docs)• utl_tcp, stored Java


Publish RSS as “view” inside database

create view AMIS_WEBLOG_FEED asselect title, link, author, to_date(substr(publication_date,6,20) ,'dd mon yyyy hh24:mi:ss') timestfrom xmltable ( XMLNAMESPACES('http://purl.org/dc/elements/1.1/' AS "dc") , 'for $i in //item return <Article>{$i/title}{$i/dc:creator} {$i/pubDate}{$i/link} </Article> ' passing httpuritype('http://technology.amis.nl/blog/?feed=rss2').getXML() COLUMNS title varchar2(100) path 'title' , link varchar2(100) path 'link' , author varchar2(100) path 'dc:creator' , publication_date varchar2(100) path 'pubDate' )


Aggregating Feeds into Central Archive

Central table to archiveall RSS News Items Keep items available after

RSS feed ‘has moved on’

RSS_ARCHIVE



New(s) items must be inserted, existing can be updated Source is join between RSS_FEEDS and XMLTable

based on external RSS feed



Run a job – scheduled, in the backgroundCreate Schedule – when to trigger execution

Create stored procedure SCAN_RSS_FEEDS that executes the MERGE statement and commits

Create Program – what action to take



Run a job – scheduled, in the background

Create Job – link Schedule (when) and Program (what)

Sit back and relax (and perhaps check on job)


Please, no more stories on …

Create a check constraint on the RSS_ARCHIVE: No stories with Palin, Biden, Obama or McCain in the

title!

However, the background job issues a single MERGE statement to refresh the archive table That will fail as long as the collective feeds contain

only one article that violates the CHECK CONSTRAINT Surely that is not what you would want to happen?


Complete DML statement – log errors

Oracle 10gR2 introduced ‘DML Error log’ Create an error log

table

Indicate for a DML statement that exceptions should be logged to the table while the statement may continue and complete successfully


RSS Archive – Features Used

UTL_HTTP, httpuritypeXMLDB: XMLTable, XQueryDBMS_SCHEDULERRegular Expression in Check Constraint

(REGEXP_LIKE)MERGEDML ERROR LOG


Expose recent HRM Data Changes

Several parties are interested in all changes in the area of Human Resources (table EMP & DEPT) We have received a request to make an overview

of all recent changes available, preferably in XML format


Our first thought – using triggers

After Insert/Update/Delete trigger for each row Record change in table EMP_CHANGE_LOG Part of the same transaction as the change(s)

However: Adds overhead to the DML operation Requires a trigger on every table for which

changes must be exposed• And if a trigger is disabled, changes are not logged

Requires an additional (log) table for each table

And it can be done much more elegantly….


Flashback Versions

Retrieve all states each record has been in Every transaction that touched a row left a version of

it Pseudocolumns: xid, operation, starttime, endtime

Note: depends on the availability of UNDO or Flashback data; using 11g Flashback Archive is a way to ensure that on a per table basis


Example of Employee Versions


Finding the biggest salary raise ever

Analytical Function LEAD (looks to the next row) Get SALARY in the version that comes after the

current row (when sorted by starttime descending)


Expose HRM Changes in XML document

Create package HRM_CHANGE_HISTORY Containing procedure EMP_HISTORY that writes

an XML document to htp.p with Employee change records

Using DBMS_EPG Create DAD hrmhistory on path /hrm/* Authorize DAD hrmhistory

for schema SCOTT

Access URL http://localhost:2100/hrm/scott. hrm_change_history.emp_history


http://host/../emp_history


Pull EMP change overview from browser


Pull model: DBMS_EPG/Web Toolkit

Pull model means create on request Always the most fresh data Potentially large load on the database

as for every request the XML document is recreated

Document is retrieved through a download by an HTTP client

Alternative: push (publish) model Periodically create the XML document Make it available to various clients

• On the file system, on the web


XDBFolder

•WEBDAV•FTP•HTTP•PL/SQL•SQL


Periodically publish XML Report

Create XML DB Folder Is also a ‘directory page’ via HTTP And a WEBDAV folder And an FTP directory

Schedule Job to Create Resource in the XDB Folder Based on XMLType: the result of

a query that collects the desired data

To access: Go to URL & open in browser Map Network Drive via WEBDAV Open FTP session


XDBFolder

•WEBDAV•FTP•HTTP•PL/SQL•SQL


Create XML ResourceDECLARE CURSOR xmlcur IS SELECT xmlelement("EMPHISTORY", ... l_xml_dir VARCHAR2(30) := '/public/hrmData'; l_doc_name VARCHAR2(30) := 'EmpChangeHistory.xml'; l_emp xmltype; l_res booleanBEGIN IF dbms_xdb.CreateFolder(l_xml_dir) THEN ... some ACL setting up END IF; open xmlcur; fetch xmlcur into l_emp; close xmlcur; l_res := dbms_xdb.createResource ( abspath=> l_xml_dir||l_doc_name , data=> l_emp ); COMMIT;end;


HRM Report via WEBDAV, SQL & HTTP


Viewing&Editing XDB Resource in Excel


Uploading data from file to database


Some features used

Flashback VersionsAnalytical Functions SQL/XML and XMLTypePublish database service on the web:

DBMS_EPGXML DB

Publish (FTP/WebDav/HTTP) Resource Access XMLDB resource through SQL, PL/SQL,

WebDAV (Excel, Windows Explorer) and HTML (Browser)


Summary

Modern is determined by what you can do, can afford, have a need or demand for and consider best practice

Main trend: Service or Interface orientation Clear, reusable interface Hide implementation details Loose coupling, reduced dependency Standardized contract, communication Cater for various client technologies & protocols

• SQL/PLSQL, .NET, Java/JDBC, XML, HTTP, WebDAV,

Inside database, benefit from external services through HTTP

Make database context aware (who, when, where)

Continually appreciate the power and richness of SQL


Want to know more?

Find this presentation and all scripts Or read our articles at

Oracle Database, SQL & PL/SQL

ADF APEX SOA Suite: BPEL,

EBS, BAM, … and many other

Oracle related topics…

modern database development oow2008 lucas jellema

Technology

database database

database sql

database vendor

database independence

database accelerator

active database

date database

relational database