I have seen multiple custom solutions and packaged applications store their SQL outside of stored procedures. I am focusing this article on custom enterprise solutions, which I feel is one of the fastest growing areas in development. Some people who choose to store SQL outside of their database have touted database independence/abstraction, others easier maintenance, and I’m sure many other “excuses” have been communicated. In my opinion, if you have 1) a custom enterprise application and 2) an Oracle database(s) as the backend, all of your SQL — and associated DB logic — should be written, stored, maintained, etc. within Oracle stored procedures. I can dream up very unique scenarios where a valid argument could possibly begin to be made to keep SQL outside — but I think 99% of places that have done this, or will be considering this, really should have kept SQL inside their DB.

I’d like to hear your reasons for maintaining your Oracle SQL outside of Stored Procedures.

Related Information:

1. MySQL and SQL Server - Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data Recursive queries are something I have used many, many times over the years to build dynamic, n-tiered hierarchies. Oracle’s CONNECT...
2. Oracle Rename Table - Example Syntax and Dependencies Renaming a table in Oracle is simple. Following is the generic Oracle table rename syntax: 1 ALTER TABLE current_table_name RENAME...
3. Discoverer - Public Connections for Oracle Applications with SSO While using SSO, in order to create a public connection to Oracle Discoverer for Oracle Applications, I suggest creating a...
4. Oracle APEX Tutorial 8 - Up and Downloading Files - Part 2 - Video Training Reports and data entry constitue the bulk of what most of these tutorials cover, and what many businesses need. However,...
5. SQL to Query Oracle, Return XML - with SQLX Like many days, I had a need to query data stored in Oracle. But today was different. I needed to...