DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • GenAI: From Prompt to Production
  • Using AUTHID Parameter in Oracle PL/SQL
  • How to Use Self Join and WITH Clause in Oracle
  • SQL Server to Postgres Database Migration

Trending

  • Apache Doris vs Elasticsearch: An In-Depth Comparative Analysis
  • Rust and WebAssembly: Unlocking High-Performance Web Apps
  • Comparing SaaS vs. PaaS for Kafka and Flink Data Streaming
  • Scaling in Practice: Caching and Rate-Limiting With Redis and Next.js
  1. DZone
  2. Data Engineering
  3. Databases
  4. The SmartDB Resource Center

The SmartDB Resource Center

I put together this blog post for those interested in learning more about the SmartDB architecture and how to apply it in your applications.

By 
Steven Feuerstein user avatar
Steven Feuerstein
·
May. 10, 18 · Analysis
Likes (1)
Comment
Save
Tweet
Share
4.9K Views

Join the DZone community and get the full member experience.

Join For Free

I put together this blog post for those interested in learning more about the SmartDB (also or formerly known as "ThickDB") architecture and how to apply it in your applications. I will update it as more resources become available.

What Is SmartDB?

Bryn Llewellyn, PL/SQL Product Manager, offers this description:

Large software systems must be built from modules. A module hides its implementation behind an interface that exposes its functionality. This is computer science’s most famous principle. For applications that use an Oracle Database, the database is, of course, one of the modules. The implementation details are the tables and the SQL statements that manipulate them. These are hidden behind a PL/SQL interface.

This is the Smart Database paradigm: select, insert, update, delete, merge, commit, and rollback are issued only from database PL/SQL. Developers and end-users of applications built this way are happy with their correctness, maintainability, security, and performance. But when developers follow the NoPlsql paradigm, their applications have problems in each of these areas and end-users suffer.

Experts

The two leading proponents of SmartDB from Oracle are:

Bryn Llewellyn, Product Manager for PL/SQL and Edition-based Redefinition

Bryn Llewellyn has worked in the software field for more than thirty-five years. He joined Oracle UK in 1990 at the European Development Center to work on the Oracle Designer team. He transferred to the Oracle Text team and then into consulting as the text specialist for Europe. He relocated to Redwood Shores in 1996 to join the Oracle Text Technical Marketing Group. He has been the product manager for PL/SQL since 2001. In 2005, he became responsible, additionally, for edition-based redefinition (EBR for short). This is the Oracle Database capability that supports online application upgrade.

It’s hard for Bryn to remember his life before Oracle. He started off doing image analysis and pattern recognition at Oxford University (programming in FORTRAN) and then worked in Oslo, first at the Norwegian Computing Center and then in a startup. In Norway, Bryn programmed in Simula (its inventors were his close colleagues). This language is recognized as the first object-oriented programming language and was the inspiration for Smalltalk and C++. Bryn is an Oak Table member. 

Follow Bryn on Twitter and subscribe to his blog.

Toon Koppelaars, Real World Performance Team

Toon has been part of the Oracle eco-system since 1987. He is currently a member of Oracle's Real World Performance team. RWP troubleshoots application performance issues in and around the DBMS. The way applications currently use (or, rather, abuse) the DBMS is often at the root of these performance issues. Prior to joining the RWP team, Toon was mainly involved in database application development. He is the co-author of "Applied Mathematics for Database Professionals" (Apress 2016), a member of the OakTable network (http://www.oaktable.net/) and alumni Oracle ACE-Director. His special interests are architecting applications for performance and scalability, database design, and business rules/constraints modeling. He is a long-time champion of the Smart Database paradigm, as witnessed by his authorship of the Helsinki Declaration (IT Version) in 2009.

Follow Toon on Twitter.

AskTOM Office Hours on SmartDB

AskTOM, famous for its exhaustive Q&A on Oracle Database, has added free, monthly training, and Q&A, in the guise of Office Hours.

Bryn and Toon offer a monthly series on SmartDB. Subscribe here for reminders to stay up on the very latest with SmartDB!

Resources

NoPLSql and Thick Database Approaches With Toon Koppelaars 

Which one do you think requires a bigger database server?

Toon Koppelaars describes an experiment to measure the work done by Oracle Database to complete a specific task using different approaches. The NoPlsql approach treats the database as no more than a persistence layer, using only naive single-row SQL statements; it implements all business logic outside of it. The Thick Database approach treats the database as a processing engine; it uses a combination of sophisticated set-based SQL statements and PL/SQL to implement all business logic inside it. “No business logic in the database” advocates take note: the Thick Database approach gets the task done with far less database work than the NoPlsql approach.  

Guarding Your Data Behind a Hard Shell PL/SQL API

This session examines in practical detail how to ensure that the hard shell of a database’s PL/SQL API is impenetrable. It advocates strict adherence to the principle of least privilege by using a four-schema model (data, code implementation, API, and connect) and invokers rights units together with code-based access control. Scrupulous care is taken to ensure that the privileges needed for installation and patching are not available at runtime, and the approach is reinforced by secure error-handling. 

The Database: Persistence Layer (NoPlsql) or Processing Engine (SmartDB)?

Slide deck from Toon's presentations at ODTUG's Kscope17 conference. Toon goes deep into the question of where business logic should reside, and the benefits you get from putting that logic into the database.

Also: Why SmartDB?

How to Install a #SmartDB Application Back-End

Bryn Llewellyn offers a "sketch" of how developers and DBAs should set up their application in the database to follow a SmartDB architecture.

Why Use PL/SQL?

Bryn Llewellyn's definitive white paper on the key advantages accrued when you use the PL/SQL language, to build secure, maintainable, high-performance applications that guarantee data integrity and consistency.

Doing SQL From PL/SQL: Best and Worst Practices 

Assuming you buy into the SmartDB paradigm and will enclose your SQL statements inside PL/SQL "hard shell," this white paper from Bryn Llewellyn will help you do it properly.

Moovit: A View From the Trenches

Millions of people develop applications on top of Oracle Database. The most secure and optimized of those applications take full advantage of SQL and PL/SQL. In this CodeTalk webcast, Steven Feuerstein interviews Oren Nakdimon of Moovit (lead developer for the backend of this popular transit app, to find out just how he and his small team have made the most of PL/SQL, and how they manage their PL/SQL code base. 

How to Prove That Your SmartDB App Is Secure

If you are guarding your data behind a hard shell PL/SQL API as Bryn Llewellyn, Toon Koppelaars and others recommend, then it should be quite easy to prove, that your PL/SQL application is secured against SQL injection attacks. The basic idea is 1) that you do not expose data via tables nor views to Oracle users used in the middle-tier, by end-users and in the GUI; and 2) that you use only static SQL within PL/SQL packages. By following these two rules, you ensure that only SQL statements with bind variables are used in your application, making the injection of unwanted SQL fragments impossible. In this blog post, Philipp Salvisberg shows how to check if an application is complying to these two rules.

Database application PL/SQL Business logic sql

Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • GenAI: From Prompt to Production
  • Using AUTHID Parameter in Oracle PL/SQL
  • How to Use Self Join and WITH Clause in Oracle
  • SQL Server to Postgres Database Migration

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!