lucas jellema oracle openworld 2014, san francisco, ca, usa everything that is really useful in...

89
Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Upload: dulce-mccarthy

Post on 15-Dec-2015

227 views

Category:

Documents


5 download

TRANSCRIPT

Page 1: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Lucas Jellema

Oracle OpenWorld 2014, San Francisco, CA, USA

Everything That Is Really Useful in Oracle Database 12c for Application Developers

Page 2: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

2

Oracle Database 12c =

• Multitenant Architecture aka Pluggable Database

Page 3: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

3

Oracle Database 12c – 12.1.0.2 =

• In-Memory Database Option

Page 4: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

4

Oracle Database 12c for Database Developers

Flash

backJSO

N

Top N

Pattern

Match

Inline

PL/S

QL SQ

L Translation

Dat

a

M

aski

ng

(+)

Page 5: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

5

License

• Flashback Data Archive in all database editions

Page 6: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Flashback

• Introduced in 9i• Based on UNDO• Initially only for recovery• As of 11g – Total Recall option with

Flashback Data Archive– Controlled history keeping per table

select ename, job, salfrom emp as of timestamp (systimestamp - INTERVAL '1' DAY) yester_emp

Page 7: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Flashback

• Look back into history– Query trends (version history)– Difference reporting– Restore Test Data Sets [to pre-test situation]– Audit trails (Replace journaling tables)

• Require trick for transaction history: WHO?

• Also: when is the start of history? What went on before? What to do with existing archives?

• By the way: Flashback Data Archive requires EE & Advanced Compression database option

Page 8: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Total Recall

• Flashback Data Archive Improvements:– Complete schema evolution support All table definition, partitioning,

and space management DDLs are supported on FDA-enabled tables.

– The metadata information for tracking transactions including the user context is now tracked. The addition of user-context tracking makes it easier to determine which user made which changes to a table. • This could mean that journaling tables are now officially deprecated• Also given that the current contents of journaling tables can even be migrated to

Flashback Data Archive

Page 9: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Total Recall (2)

• Import and export of history – Support for import and export using Data Pump for FDA-enabled

tables. Data Pump can now be used to export and import an FDA-enabled base table along with its schema-evolution metadata and historical row versions.

• User generated history– Support for importing user-generated history has been added.

Customers who have been maintaining history using other mechanisms, such as triggers, can now import that history into Total Recall.

• Database Hardening– Register "Application" (a group of tables) and enable/disable

flashback data archive for the application (also available: lock an application – make all tables read only)

Page 10: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

10

Generate History – Actions by SYS

create table oow.emp as select * from scott.emp

grant execute on dbms_flashback_archive to oow;grant execute on dbms_flashback to oow;

CREATE FLASHBACK ARCHIVE DEFAULT one_year TABLESPACE users QUOTA 100M RETENTION 1 YEAR;grant flashback archive on one_year to oow;

exec dbms_flashback_archive.create_temp_history_table('OOW', 'EMP');

-- This statement once in a database instance--This will extend mappings to the past so that import of old history can be done. Goes back to 01-JAN-88.EXEC DBMS_FLASHBACK_ARCHIVE.extend_mappings();

---

-- after some history has been created:EXEC DBMS_FLASHBACK_ARCHIVE.IMPORT_HISTORY('oow','EMP');

Page 11: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

11

Generate History – Actions by Application

• Insert records describing each stage of history that has existed– Including start and end time of historic state

insert into temp_history (RID , STARTSCN , ENDSCN , XID, OPERATION ,EMPNO, ename, job, hiredate, sal, deptno )values (NULL, timestamp_to_scn(to_date('01-04-2001', 'DD-MM-YYYY')), timestamp_to_scn(to_date('01-07-2003', 'DD-MM-YYYY')), NULL, 'I',1567,'SELLERS','CLERK',to_date('01-04-2001','DD-MM-YYYY'),2200, 10);

insert into temp_history (RID , STARTSCN , ENDSCN , XID, OPERATION,EMPNO, ename, job, hiredate, sal, deptno)values (NULL, timestamp_to_scn(to_date('01-07-2003', 'DD-MM-YYYY')), timestamp_to_scn(to_date('01-10-2006', 'DD-MM-YYYY')), NULL, 'U',1567,'SELLERS','CLERK',to_date('01-04-2001','DD-MM-YYYY'),2200, 20);…

Page 12: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

12

Query the generated history

select ename, jobfrom emp as of timestamp (sysdate - INTERVAL '10' YEAR)minusselect ename, jobfrom emp

select ename, jobfrom emp as of timestamp (systimestamp - INTERVAL '3' YEAR) minusselect ename, jobfrom emp

Page 13: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

13

Ensure transaction context is recorded (and set)

exec dbms_flashback_archive.set_context_level(level=> 'ALL');

exec dbms_session.set_identifier('The Creepy User from Finance ');

update oow.empset sal = sal * 1.4 where ename = 'ALLEN'/commit;

exec dbms_session.set_identifier('Scary Janitor from the Annex');

update oow.empset sal = sal * 0.7 where ename = 'MILLER'/

commit;

Page 14: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

14

Audit the generated history

SELECT versions_xid, versions_starttime, empno, ename, sal new_sal, s.client_identifierFROM oow.emp VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue, sys.sys_fba_context_aud swhere versions_xid = s.xid

Page 15: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

15

Alternative: retrieve context with dbms_flashback_archive.get_sys_context

SELECT versions_xid, versions_starttime, empno, ename, sal new_sal, dbms_flashback_archive.get_sys_context (versions_xid,'USERENV','CLIENT_IDENTIFIER') whoFROM emp VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue

Page 16: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

JavaScript Object Notation

Lightweight data-interchange format

Flashb

ackJSO

N

Top N

Pattern

Match

Inline PL/S

QL

SQL Translation

Dat

a

Mas

king

(+)

Support in Oracle Database 12c – 12.1.0.2

Page 17: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Name : Value

Page 18: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Name : Value,Name : Value,Name : Value

Page 19: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Name : Value,Name : Value,Name : Value

Name : Value,Name : Value,Name : Value

Name : Value,Name : Value,Name : Value, ,

Page 20: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

{ "NAME" :"ACCOUNTING", "EMPLOYEES" : [ {"ENAME" : "KING", "JOB" : "PRESIDENT", "SAL" : 5000 }, {"ENAME" : "MILLER", "JOB" : "CLERK", "SAL" : 1300 }]}

Page 21: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

<department name="ACCOUNTING"> <employees> <employee> <ename>KING</ename> <job>PRESIDENT</job> <sal>5000</sal> </employee> <employee> <ename>MILLER</ename> <job>CLERK</job> <sal>1300</sal> </employee> </employees></department>

Page 22: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

22

JSON

• Light Weight, Structured, fairly tied coupled (low bandwidth) interactions between application[component]s

• Very popular in rich client web applications and mobile apps – usually as the format used in REST-services

• Oracle Database 12c is JSON aware– Store documents (in a column VARCHAR2, CLOB, NCLOB, BLOB, RAW, BFILE –

and have them checked for JSON validity

– Create Indexes on JSON contents– Access JSON content directly from SQL queries– Note: similar to but only a subset of XML support in Oracle Database

(as JSON in terms of functionality is a somewhat pale subset of XML)

alter table Xadd CONSTRAINT ensure_json_chk CHECK (column_y IS JSON))

Page 23: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

23

JSON in the Oracle Database

• Why JSON in relational database?– Consistency (integrity, transaction ACIDity) for storing JSON documents– Quickly interpret data received (and stored?) in JSON format– Leverage JSON as a convenient way to handle structured data as a string– Easy data transfer over some protocols, including HTTP – especially when database

engages directly in HTTP interactions

Page 24: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

24

A sample JSON document

{"matches": [ {"matchLineUp": "NED-ESP" , "score": "5-2" ,"matchDate":"13-06-2014" } , {"matchLineUp": "SWI-FRA" , "score": "2-5" ,"matchDate":"20-06-2014" } , {"matchLineUp": "GER-BRA" , "score": "1-7" ,"matchDate":"08-07-2014" } ]}

Page 25: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

25

Check for a valid JSON document and/or path result

• IS JSON can be used in the WHERE clause to filter on rows that contain a valid JSON document– It can be used in CHECK Constraints for that same reason

• JSON_EXISTS is typically used in the WHERE clause to filter on rows whose JSON content satisfies some JSON path condition

select * from customerswhere json_exists(doc, '$.addresses.privateAddress')

select * from customerswhere doc IS JSON

Page 26: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

26

JSON_VALUE to retrieve scalar value from JSON document

• JSON_VALUE is a SQL Function used to retrieve a scalar value from a JSON document – JSON_VALUE can be used like any other SQL Function in queries and DML – in

various clauses like SELECT, WHERE, GROUP BY, etc.

select json_value ( '{"matches": [ {"matchLineUp": "NED-ESP" , "score": "5-2","matchDate":"13-06-2014"} , {"matchLineUp": "SWI-FRA" , "score": "2-5","matchDate":"20-06-2014"} , {"matchLineUp": "GER-BRA" , "score": "1-7","matchDate":"08-07-2014"} ] }' , '$.matches[1].score') as "2nd_match_score"from dual

Page 27: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

27

JSON_QUERY to retrieve JSON-snippets from a JSON document

select json_query ( '{"matches": [ {"matchLineUp": "NED-ESP" , "score": "5-2","matchDate":"13-06-2014"} , {"matchLineUp": "SWI-FRA" , "score": "2-5","matchDate":"20-06-2014"} , {"matchLineUp": "GER-BRA" , "score": "1-7","matchDate":"08-07-2014"} ] }' , $.matches[*].score' WITH WRAPPER) as scoresfrom dual

• JSON_QUERY is a SQL Function used to retrieve a JSON snippet from a JSON document - the result is a string that contains valid JSON– Use WRAPPER to wrap an Array result in an object to return a valid document

Page 28: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

28

JSON_TABLE to expose records from a JSON document relationally

with match_results as( select '...' json_doc from dual)select lineUp, score, to_date(matchDate, 'DD-MM-YYYY') matchDatefrom match_results, json_table( json_doc, '$.matches[*]' COLUMNS ( lineUp varchar2(20) PATH '$.matchLineUp' , score varchar2(20) PATH '$.score' , matchDate varchar2(20) PATH '$.matchDate' ) )

• JSON_TABLE is used in the FROM clause of SQL statements to project data from a JSON document as a virtual relation view– JSON_TABLE is very similar to XML_TABLE

Page 29: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

29

12.1.0.2 – JSONWhat it does not do?

• Support for variable strings as JSON_PATH in JSON-functions• Deal with JSON in PL/SQL

– JSON_VALUE and JSON_QUERY cannot be used directly from PL/SQL

• Construct JSON documents– Not supported: conversion from ADT (to XMLType) to JSON type and vice versa– Not supported: a SQL/JSON syntax similar to SQL/XML for querying together a

JSON document– Not supported: facilities that inject JSON into RESTful Services implemented through

the PL/SQL Embedded Gateway

TypesOBJECTS,

NESTED TABLE XMLType

Page 30: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

The PL/JSON library

• This library has been around for several years – and is still pretty much relevant

• Especially useful– For composing JSON documents– And for converting back and forth from and to JSON to XMLType and ADT/UDT

Page 31: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

31

Bringing closer together PL/SQL and SQL

Flash

backJSO

N

Top N

Pattern

Match

Inline

PL/S

QL SQ

L Translation

Dat

a

M

aski

ng

(+)

Page 32: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

In-line PL/SQL Functions and procedures

• Procedures are also allowed in-line• In-Line Functions and Procedures can invoke each other

WITH procedure increment( operand in out number , incsize in number)isbegin operand:= operand + incsize;end;FUNCTION inc(value number) RETURN number IS l_value number(10):= value;BEGIN increment(l_value, 100); RETURN l_value;end;SELECT inc(sal)from emp

Page 33: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

In-line PL/SQL Functions and procedures

• In-Line Functions and Procedures can invoke each other– And themselves (recursively)

WITHFUNCTION inc(value number) RETURN number ISBEGIN if value < 6000 then return inc(value+100); else return value + 100; END if;end;SELECT inc(sal)from emp

Page 34: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Dynamic (PL/)SQL is allowed inside inline functions

• EXECUTE IMMEDIATE can be used inside an inline PL/SQL function to dynamically construct and execute SQL and PL/SQL

WITHFUNCTION EMP_ENRICHER(operand varchar2) RETURN varchar2 IS sql_stmt varchar2(500); job varchar2(500);BEGIN sql_stmt := 'SELECT job FROM emp WHERE ename = :param'; EXECUTE IMMEDIATE sql_stmt INTO job USING operand; RETURN ' has job '||job;END;SELECT ename || EMP_ENRICHER(ename)from emp

Note: do not try this at home! It is a horrible query!(looks too much like POST_QUERY for comfort)In-Line PL/SQL is not an excuse for lousy SQL

Page 35: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Combining in-line Views and PL/SQL Functions & Procedures

WITHprocedure camel(p_string in out varchar2)isbegin p_string:= initcap(p_string);end;function obfuscate(p_string in varchar2)return varchar2is l_string varchar2(100);begin l_string:= translate(upper(p_string), 'AEUIO','OIEUA'); camel(l_string); return l_string;end;my_view as (select obfuscate('Goedemorgen')from dual)select *from my_view

Page 36: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

PL/SQL Functions That Run Faster in SQL

• As of Oracle Database Release 12c, two kinds of PL/SQL functions might run faster in SQL:– PL/SQL functions that are defined in the WITH clauses of SQL SELECT

statements, described in Oracle Database SQL Language Reference– PL/SQL functions that are defined with the "UDF Pragma"

• Pragma UDF means: compile in the ‘SQL way’ as to eliminate SQL PL/SQL context switch

FUNCTION inc(string VARCHAR2) RETURN VARCHAR2 IS PRAGMA UDF; value number(10):= to_number(string);BEGIN if value < 6000 then return inc(value+100); else return to_char(value + 100); end if;end;

Page 37: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

38

• Maximum length for VARCHAR2 is now 32KB (up from 4KB)

• Invisible Columns• One unified audit trail

• PL/SQL DBMS_UTILITY.EXPAND_SQL_TEXT can be used to uncover the real SQL executed for a given query– Expressed only in the underlying base tables, including VPD policies

• New PL/SQL Package UTL_CALL_STACK provides API for inspecting the PL/SQL Callstack– Programmatic interpretation, not pretty like DBMS_ UTILITY.FORMAT_CALL_STACK

• Export View as Table with Data Pump – fine grained projection of columns and rows that will be part of the Dump and subsequent Import

• Creation of multiple indexes on same set of columns is allowed– Although only one can be live at any one time

• Cross PDB queries

Page 38: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

DEFAULT

• Default applied (also) when NULL was explicitly specified

• Default based on Sequence

• Identity Column that is automatically assigned generated sequence number value

• Meta Data Only Defaults– Data applies to potentially many records but hard takes up any space – only some

meta-data are required to declaratively describe the data

alter table emp

modify (sal number(10,2)

DEFAULT ON NULL 1000

)

alter table emp

modify (empno number(5) NOT NULL

DEFAULT ON NULL EMPNO_SEQ.NEXTVAL

)

Page 39: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

The Top-3 Earning Employees

• What can you say about the result of this query with respect to the question: "Who are our top three earning employees?"

A. Correct AnswerB. Sometimes correctC. Correct if there are never duplicate

salariesD. Not Correct

Page 40: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

In-Line Views

Page 41: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

TOP-N Queries in 12c

• Last part of a query to be evaluated – to fetch only selected rows from the result set:

– To select the next set of rows:

select *from emporderby sal descFETCH FIRST 3 ROWS ONLY;

select *from emporderby sal descOFFSET 3 FETCH NEXT 4 ROWS ONLY;

Page 42: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Apply for joining

• APPLY is used to join with a Collection

• The function employees_in_department returns a collection (TABLE OF VARCHAR2 in this case)

• The function takes the DEPTNO value from the DEPT records as input

• Only when the returned collection is not empty will the DEPT record be produced by this join

• Use OUTER APPLY to get a result row even for an empty collection

SELECT *FROM DEPT d CROSS APPLY employees_in_department(deptno) staff

DDDD

Page 43: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Data Masking

Flash

backJSO

N

Top N

Pattern

Match

Inline

PL/S

QL SQ

L Translation

Dat

a

M

aski

ng

(+)

Page 44: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Data Redaction

• At runtime, you can optionally have the query results modified to reset/scramble/randomize sensitive data– Through ‘data redaction’ policies associated with tables and view and applied at

query time

• Because the data is masked in real-time, Data Redaction is well suited to environments in which data is constantly changing.

• You can create the Data Redaction policies in one central location and easily manage them from there.

SQL engine SQL

POLICY

POLICY

RESULTS

Page 45: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

My first Data redaction policy

• Access to DBMS_REDACT package

• Create Data Redaction Policy for SAL column in EMP table – hide salaries from view

• Find that querying EMP has changed forever…– Note: the expression can be used to dynamically decide whether or not to apply the

policy

grant execute on dbms_redact to scott;

BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'scott', object_name => 'emp', column_name => 'sal', policy_name => 'hide_salary', function_type => DBMS_REDACT.FULL, expression => '1=1' );END;

Page 46: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Querying EMP with DATA REDACTION in place

• Note: drop Redaction Policy

DBMS_REDACT.DROP_POLICY ( object_schema => 'scott' , object_name => 'emp' , policy_name => 'hide_salary' );

Page 47: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Partial Column masking – mask Day and month from hiredate

BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'scott', object_name => 'emp', column_name => 'hiredate', policy_name => 'partially mask hiredate', expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')!= ''GOD''' function_type => DBMS_REDACT.PARTIAL, function_parameters => 'm1d31YHMS', expression => '1=1' );END;

Page 48: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

SQL Statement Preprocessor

• A mechanism to allow the text of a SQL statement, submitted from a client program (e.g. via ODBC or JDBC), to be translated by user-supplied code before it is submitted to the Oracle Database SQL compiler. – Additionally, this feature can satisfy any other use case where it is expedient to

intervene between the SQL statement that the client submits and what is actually executed

– Some associatations: VPD policies, Data Redaction, MV query rewrite, Advanced Query Rewrite, Temporal Validity (time slice)

ApplicationSQLPre

processor

SQL engine SQL

Page 49: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

SQL Translation – set up

AS SYS:grant create sql translation profile to oow;grant alter session to oow;

AS Application Owner:-- Create a Translation Profileexec dbms_sql_translator.create_profile('HR_PROFILE');

BEGIN DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION( profile_name => 'HR_PROFILE', sql_text => 'select ename, job, hiredate from emp', translated_text => 'select initcap(ename) as ename , job, hiredate from emp where job<>''MANAGER'' ‘ );END;

select * FROM USER_SQL_TRANSLATION_PROFILES;select * from USER_SQL_TRANSLATIONS;

Page 50: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

SQL Translation – in action

-- to enable the profile (usually in logon trigger)alter session set sql_translation_profile = HR_PROFILE

-- to pretend we are a foreign application, subject to -- SQL Translationalter session set events = '10601 trace name context forever, level 32';

-- execute query that is to be translatedselect ename, job, hiredate from emp

-- results are produced as if the translated text had been submitted

SQLPre processor

SQL engine SQL

Page 51: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

53

SQL Translation

• Support for bind parameters• Support for rewriting PL/SQL calls• Translation of Error Messages (ORA-xxxx to SYB-yyyy or YOURAPP-zzz)• Out of the box translators for Sybase, SQL Server and some DB2• Optionally: register a custom translator package

– PROCEDURE TRANSLATE_SQL( sql_text IN CLOB, translated_text OUT CLOB);

Page 52: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

SQL Statement Preprocessor

• The translation code is named and is installed in the database using a PL/SQL API. It can be implemented programmatically, or by look-up, or by a suitable mixture of these.

• The mechanism also allows Oracle error codes and American National Standards Institute (ANSI) SQLSTATES to be translated by user-supplied code.

• The motivating use case is to allow extant client-side application code, written for a different vendor's database (and therefore for a SQL dialect other than Oracle's), to run unchanged against an Oracle Database by emulating the syntax and semantics of the other SQL dialect thereby greatly reducing the cost of migration.

• Additionally, this feature can satisfy any other use case where it is expedient to intervene between the SQL statement that the client submits and what is actually executed.

• See: Oracle Database SQL Translation Installation, Configuration, and User's Guide for details

Page 53: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

55

Looking into the future…

OUR_PRODUCTSNAME PRICE

select name, pricefrom our_products

Page 54: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

56

Looking further into the future…

OUR_PRODUCTSNAME PRICE

select name, pricefrom our_products

begin DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME ( level => 'ASOF' , query_time => TO_TIMESTAMP('01-10-2018', 'DD-MM-YYYY') );end;

Page 55: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

57

Current situation …

OUR_PRODUCTSNAME PRICE

select name, pricefrom our_products

begin DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME ( level => 'CURRENT');end;

Page 56: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

58

All data in the table(the default setting)

OUR_PRODUCTSNAME PRICE

select name, pricefrom our_products

begin DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME ( level => 'ALL');end;

Page 57: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

59

All data in the table(the default setting)

OUR_PRODUCTS

NAME PRICE

select name, price, start_date, end_datefrom our_productsorderby start_date

START_DATE END_DATE

begin DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME ( level => 'ALL');end;

Page 58: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Make the database aware of the time based business validity of records

• Add timestamp columns indicating start and end of valid time for a record• Specify a PERIOD for the table

• Note:– A table can have multiple sets of columns, describing multiple types of validness– Beyond 12.1.0.2 many Temporal Validity enhancements are expected:

• Unique Constraints, Foreign Key references, (auto) Join conditions, gap and overlap checks, aggregation

create table our_products( name varchar2(100), price number(7,2), start_date timestamp, end_date timestamp, PERIOD FOR offer_time (start_date, end_date));

Page 59: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Valid time aware flashback queries

• Select all product prices on offer at a certain moment in time

• Perform all queries for records that are valid at a certain point in time – past or future

• Return all records currently (session time) valid

• Return all records (default)

SELECT * FROM OUR_PRODUCTS AS OF PERIOD FOR offer_time TO_TIMESTAMP('01-10-2014','DD-MM-YYYY')

EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time ( 'ASOF' , TO_TIMESTAMP('01-05-2016','DD-MM-YYYY') );

EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT');

EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL');

Page 60: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

64

Oracle Database 12c for Database Developers

Flash

backJSO

N

Top N

Pattern

Match

Inline

PL/S

QL SQ

L Translation

Dat

a

M

aski

ng

(+)

Security

Page 61: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

White List

• A white list of allowed invokers can be defined for a PL/SQL unit– supports the robust implementation of a module, consisting of a main unit and

helper units, by allowing the helper units to be inaccessible from anywhere except the unit they are intended to help.

schema

Page 62: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

accessible by clause

package Helper authid Definer accessible by (Good_Guy, Bad_Guy)is procedure p;end Helper;

package body Good_Guy is procedure p is begin Helper.p(); ... end p;end Good_Guy;

package body Bad_Guy is procedure p is begin Helper.p(); ... end p;end Bad_Guy;

PLS-00904: insufficient privilege to access object HELPER

_______

Page 63: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

67

(More) Security related features

• Attach database roles to the program units functions, procedures, packages, and types. – The role then becomes enabled during execution of the program unit (but not during

compilation of the program unit).

• A new built-in namespace, SYS_SESSION_ROLES, allows you to determine if a specified role is enabled for the querying user

• View is either – BEQUEATH DEFINER (the default), which behaves like a Definer’s Rights unit

(functions in the view are executed using the view owner’s rights)– or BEQUEATH CURRENT_USER, which behaves somewhat like an invoker’s rights

unit (functions in the view are executed using the current user’s rights)

• An Invoker's Rights Function Can Be Result-Cached• READ privilege to allow SELECT but no SELECT FOR UPDATE• Invoker’s rights procedure calls only can run with the privileges of the

invoker if the procedure’s owner has the INHERIT PRIVILEGES privilege on the invoker (do not stealthily use invoker’s privileges as owner)– or if the procedure’s owner has the INHERIT ANY PRIVILEGES privilege

Page 64: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Inherit or not in Invoker rights program units

• When a user runs an invoker's rights procedure (or program unit), it runs with the privileges of the invoking user.

• As the procedure runs, the procedure’s owner temporarily has access to the invoking user's privileges.

• [If the procedure owner has fewer privileges than an invoking user,] the procedure owner could use the invoking user’s privileges to perform operations

procedure

owner

Invoker’s rights

invoker

procedure Special_Table

select

privilegeTap_Table

Page 65: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Pattern Matching

Flash

backJSO

N

Top N

Pattern

Match

Inline

PL/S

QL SQ

L Translation

Dat

a

M

aski

ng

(+)

Page 66: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

70

Raw Data Refinementbased on pattern matching

14,016,114,116,116,013,114,016,013,113,014,116,014,113,014,116,013,114,0

Processing• Information• Conclusion• Alert• Recommendation• Action

Page 67: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

71

Raw, fine grained Tennis Results: rally points

Challenge: • Derive the match winner and final

score (per set and for the entire match) from this raw data

Match Id, Player [who scored]14,016,114,116,116,013,114,0

Page 68: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

72

Using MATCH_RECOGNIZE to process data looking for patterns

• MATCH_RECOGNIZE will analyze, filter, aggregate and reformat the data – based on matches patterns between subsequent rows in the source rows

with rallypoints as( select column_value player , rownum seq from table(number_tbl(1,0,0,1,0,0,1,1,1,0,1,1,1,0,0,0,0)))SELECT winner, gameNoFROM rallypoints MATCH_RECOGNIZE ( ...) MR

source row set MATCH_RECOGNIZE

Result Set fed to SELECT

Page 69: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

73

Using MATCH_RECOGNIZE to process data looking for patterns

with rallypoints as( select column_value player , rownum seq from table(number_tbl(1,0,0,1,0,0,1,1,1,0,1,1,1,0,0,0,0)))SELECT winner, gameNoFROM rallypoints MATCH_RECOGNIZE ( ORDER BY seq MEASURES C.seq AS seq, C.player as winner , MATCH_NUMBER() AS gameNo ONE ROW PER MATCH PATTERN (A+? C) DEFINE C as (case C.player when 1 then sum(A.player) else (sum(abs((A.player-1)))) end >= 3 and case C.player when 1 then sum(A.player*2-1) else (sum(1-A.player*2)) end >= 1 )) MR

- The first player to have won more than 4 points

- and have won two or more points more than his opponent

Page 70: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

74

Multi-Level processing

Rallypoints Won Games Won Sets Won Matches

Page 71: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

75

Using MATCH_RECOGNIZE to process data looking for patterns

with rallypoints as( select column_value player , rownum seq from table(number_tbl(1,0,0,1,0,0,1,1,1,0,1,1,1,0,0,0,0, …))), gamepoints as (SELECT winner, gameNoFROM rallypoints MATCH_RECOGNIZE () MR), setpoints as (SELECT winner, gameNoFROM gamepoints MATCH_RECOGNIZE () MR), matchpoints as (SELECT winner, gameNoFROM setpoints MATCH_RECOGNIZE () MR)select * from matchpoints

Page 72: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Who is afraid of Red, Yellow and blue

• Table Events– Column Seq number(5)– Column Payload varchar2(200)

Page 73: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Solution using Lead

• With LEAD it is easy to compare a row with its successor(s)– As long as the pattern is fixed, LEAD will suffice

with look_ahead_events as( SELECT e.* , lead(payload) over (order by seq) next_color , lead(payload,2) over (order by seq) second_next_color FROM events e)select seqfrom look_ahead_eventswhere payload ='red' and next_color ='yellow' and second_next_color='blue'

Page 74: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Find the pattern red, yellow and blue

• Using the new 12c Match Recognize operator for finding patterns in relational data

SELECT *FROM events MATCH_RECOGNIZE ( ORDER BY seq MEASURES RED.seq AS redseq , MATCH_NUMBER() AS match_num ALL ROWS PER MATCH PATTERN (RED YELLOW BLUE) DEFINE RED AS RED.payload ='red', YELLOW AS YELLOW.payload ='yellow', BLUE AS BLUE.payload ='blue') MRORDER BY MR.redseq, MR.seq;

Page 75: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Match_recognize for finding patterns in relational data

• The expression MATCH_RECOGNIZE provides native SQL support to find patterns in sequences of rows

• Match_recognize returns Measures for selected (pattern matched) rows– Similar to MODEL clause

• Match Conditions are expressed in columns from the Table Source, aggregate functions and pattern functions FIRST, PREV, NEXT, LAST

• Patterns are regular expressions using match conditions to express a special sequence of rows satisfying the conditions

Table Source

& Where

Match_Recognize

Process and Filter

Select &Order By

Page 76: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Did we ever consecutively hire three employees in the same job?

• Find a string of three subsequent hires where each hire has the same job• Order by hiredate, pattern is two records that each have the same job as

their predecessor

SELECT *FROM EMPMATCH_RECOGNIZE ( ORDER BY hiredate MEASURES SAME_JOB.hiredate AS hireday , MATCH_NUMBER() AS match_num ALL ROWS PER MATCH PATTERN (SAME_JOB{3}) DEFINE SAME_JOB AS SAME_JOB.job = FIRST(SAME_JOB.job)) MR

Page 77: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Pattern clause is a regular expression

• Supported operators for the pattern clause include:– * for 0 or more iterations– + for 1 or more iterations– ? for 0 or 1 iterations– { n } for exactly n iterations (n > 0)– { n, } for n or more iterations (n >= 0)– { n, m } for between n and m (inclusive) iterations (0 <= n <= m, 0 < m)– { , m } for between 0 and m (inclusive) iterations (m > 0)– reluctant qualifiers - *?, +?, ??, {n}?, {n,}?, { n, m }?, {,m}?– | for alternation (OR)– grouping using () parentheses– exclusion using {- and -}– empty pattern using ()– ^ and $ for start and end of a partition

Page 78: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Find the longest sequence of related observations

• Records are ordered

• Each record is qualified: assignedto a certain category

• Examples:– Voting records– Ball possession in football– Days with or without rain– Passing vehicles (make and model

or category)– DNA records

• The challenge: find the longest stringof consecutive observations in the same category

Page 79: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Find the longest sequence of related observations

SELECT section_category, section_startFROM observations MATCH_RECOGNIZE ( ORDER BY seq MEASURES SAME_CATEGORY.category as section_category , FIRST(SAME_CATEGORY.seq) as section_start ONE ROW PER MATCH PATTERN (SAME_CATEGORY* DIFFERENT_CATEGORY) -- as many times as possible DEFINE SAME_CATEGORY AS SAME_CATEGORY.category = FIRST(SAME_CATEGORY.category) , DIFFERENT_CATEGORY AS DIFFERENT_CATEGORY.category != NEXT(DIFFERENT_CATEGORY.category)) MRorder by rows_in_section desc)

Page 80: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Suppose we allow a single interruption of a sequence

• One record with a different categorywill not end the sequence – it might afterall be a fluke or an incident

• Rewrite the pattern match to also accept one entry with a different category

ONE ROW PER MATCH AFTER MATCH SKIP TO NEXT ROW -- a next row in the current match may be start of a next string PATTERN (SAME_CATEGORY* DIFFERENT_CATEGORY{0,1} SAME_CATEGORY* ) DEFINE SAME_CATEGORY AS SAME_CATEGORY.category = FIRST(SAME_CATEGORY.category) , DIFFERENT_CATEGORY AS DIFFERENT_CATEGORY.category != SAME_CATEGORY.category

Page 81: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Find sequence (with one accepted interruption) from all records

SELECT substr(section_category,1,1) cat, section_start, seqFROM observations MATCH_RECOGNIZE ( ORDER BY seq MEASURES SAME_CATEGORY.category as section_category , FIRST(SAME_CATEGORY.seq) as section_start , seq as seq ONE ROW PER MATCH AFTER MATCH SKIP TO NEXT ROW -- a next row in the current match may be -- start of a next string PATTERN (SAME_CATEGORY* DIFFERENT_CATEGORY{0,1} SAME_CATEGORY* ) DEFINE SAME_CATEGORY AS SAME_CATEGORY.category = FIRST(SAME_CATEGORY.category) , DIFFERENT_CATEGORY AS DIFFERENT_CATEGORY.category != SAME_CATEGORY.category) MRorder by rows_in_section desc

Page 82: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Suspicious transactions

• Find occurrences of three or more money transfers (> 10k) within 24 hours – not necessarily consecutive

Account Transfer

TimestampAmountTo_Account

Account_NumberHolder_Name

Page 83: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Suspicious transactionsselect *from transfers tMATCH_RECOGNIZE ( PARTITION BY from_account ORDER BY transfer_time MEASURES MATCH_NUMBER() AS match_num , sum(amount) as total_amount , classifier() as match_role ALL ROWS PER MATCH PATTERN (SUSPICIOUS_TRANSFER NORMAL_TRANSFER* SUSPICIOUS_TRANSFER NORMAL_TRANSFER* SUSPICIOUS_TRANSFER) DEFINE SUSPICIOUS_TRANSFER as SUSPICIOUS_TRANSFER.amount > 10000 and SUSPICIOUS_TRANSFER.transfer_time < FIRST(SUSPICIOUS_TRANSFER.transfer_time + INTERVAL '24' HOUR) , NORMAL_TRANSFER as NORMAL_TRANSFER.amount <= 10000 ) MR

Page 84: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

No Corner cutting

RegistrationRegistration

PointCheckPoint_LabelDistance_from_Start

Runner_IdTimestamp

Page 85: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

No Corner cutting

• Patterns to look for:– Checkpoints passed in the wrong order or checkpoints missed altogether– Suspicious accelerations – stretch with > 20% higher average speed than prior or

later stretches

• Additional analysis– Fastest stretch by anyone– Section that is the fastest section for most runners

• Because of downhill or favorable wind

– Top 3 runners over any selected section

RegistrationRegistration

PointCheckPoint_LabelDistance_from_Start

Runner_IdTimestamp

Page 86: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Find suspicious speeds…

• When a runner increases speed by more than 20% - something irregular may be going on…

match_recognize ( partition by runner_id order by id ALL ROWS PER MATCH PATTERN (SUSPICIOUS_SPEED+ ) DEFINE SUSPICIOUS_SPEED as (distance_from_start- PREV(distance_from_start)/ ( extract ( hour from registration_time) + (extract(minute from registration_time)/60) + (extract(second from registration_time)/3600) - extract ( hour from PREV(registration_time)) + (extract(minute from PREV(registration_time))/60) + (extract(second from PREV(registration_time))/3600) )) > 1.2 * (PREV(distance_from_start)- PREV(distance_from_start,2)/ ( extract ( hour from PREV(registration_time)) + (extract(minute from PREV(registration_time))/60) … - extract ( hour from PREV(registration_time,2)) + (extract(minute from PREV(registration_time,2))/60) + … )) ) MR

Page 87: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

Find suspicious speeds…(the easier LAG based solution)

with runner_data as( select r.* , leg_distance/( extract ( hour from leg_time) + (extract(minute from leg_time)/60) + (extract(second from leg_time)/3600) ) leg_speed from ( select r.*, rp.* , rp.distance_from_start - lag(rp.distance_from_start,1,0) over

(partition by runner_id order by rp.id) leg_distance , r.registration_time – lag(r.registration_time,1,INTERVAL '0' MINUTE) over (partition by runner_id order by rp.id) leg_time from registrations r join registration_points rp on (rp.id = checkpoint_id) ) r ), runner_data_now_and_previous as( select runner_data.* , lag(leg_speed) over ( partition by runner_id order by checkpoint_id) previous_speed from runner_data)select *from runner_data_now_and_previous where leg_speed > 1.2 * previous_speed

Page 88: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers

95

Summary

Flash

backJSO

N

Top N

Pattern

Match

Inline

PL/S

QL SQ

L Translation

Dat

a

M

aski

ng

(+)

Security

Scripts can be downloaded from https://github.com/lucasjellema/OracleDatabase12c-development-demonstration

Page 89: Lucas Jellema Oracle OpenWorld 2014, San Francisco, CA, USA Everything That Is Really Useful in Oracle Database 12c for Application Developers