Applications with SQL Maintained Outside of Stored Procedures

Posted on Nov 24, 2009 in Technical Tips | 0 comments



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.

Bookmark and Share

Related Information:

  1. MySQL and SQL Server – Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data
    Recursive queries are something I...
  2. Oracle Rename Table – Example Syntax and Dependencies
    Renaming a table in Oracle...
  3. Discoverer – Public Connections for Oracle Applications with SSO
    While using SSO, in order...
  4. Business Applications
    M&S has worked with many...
  5. Database Systems
    M&S Consulting has developed solutions...
  6. Oracle AS: Undeploying Multiple Applications (including BPEL Human Tasks)
    Undeploying a single application in...
  7. Oracle DBMS_SCHEDULER vs DBMS_JOB (Create, Run, Monitor, Remove)
     DBMS_SCHEDULER is a newer,...
  8. SQL to Query Oracle, Return XML – with SQLX
    Like many days, I had...

Leave a Reply