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 Books for Developers

   Following are some of the best Oracle books for developers. The only thing that gets DBA’s more upset than making...

3. 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...

4. 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...

5. Oracle AS: Undeploying Multiple Applications (including BPEL Human Tasks)

   Undeploying a single application in Oracle Application Server is pretty straightforward. However, I had a scenario where I needed to...

6. Oracle DBMS_SCHEDULER vs DBMS_JOB (Create, Run, Monitor, Remove)

    DBMS_SCHEDULER is a newer, more complex job scheduling engine released in 10g, intended to replace DBMS_JOB going forward. DBMS_JOB,...