Skip to Main Content

#SmartDB Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

June 19, 2018

18:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.


Should the database be used as a persistence layer only, or a full-fledged processing engine that runs business logic too? We call the latter the #SmartDB approach. Use these sessions to explore with Bryn Llewellyn and Toon Koppelaars the #SmartDB approach for building secure, high performance applications on Oracle Database through PL/SQL APIs.

In this session, we asserted that SmartDB means exactly that the only database user(s) for which outside-of-the-database clients know the connect credentials own empty schemas and have no privileges except for the EXECUTE on the designed set of PL/SQL subprograms that jointly define the database’s API. We classified these API subprograms into two groups: XAPIs—which change data; and QAPIs which only read data. We stressed that each XAPI subprogram must have COMMIT as its last ordinary executable statement—and that each must issue rollback if an error occurs. We stressed too that COMMIT may be done *only* in a XAPI subprogram. We went on to discuss the possible use of a TAPI (table API) in pursuit of DRY in the implementation of XAPIs and QAPIs. And we were able to separate the notion of generation (not restricted to juts TAPIs) from the notion of the TAPI itself, as a PL/SQL encapsulation of operations upon a single table.

01:22 Separation of Concerns through APIs by Toon
23:02 Q&A Begins

Your Experts



    Toon has been part of the Oracle eco-system since 1987. He is currently a member of Oracle's Real-World Performance team. The RWP-team troubleshoots application performance issues in and around the DBMS. The way applications currently use the DBMS, is often at the root of these performance issues. Prior to joining the RWP team, Toon has been mainly involved in database application development. His special interests are: architecting applications for performance and scalability, database design, and business rules / constraints modeling (see his idea on SQL Assertions here He is a long-time champion of the Smart Database paradigm, i.e. using the database as a processing engine, and not just as a persistence layer.

All Sessions