Applications with SQL Maintained Outside of Stored Procedures

By M&S Consulting (Ashok) on Nov 24th, 2009
Filed Under Technical Tips // Tags: ,

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

Leave a Reply


Archives

Recent Comments

  • Kavitha Muniraj said: Hi, Could you please send a script which checks the status of forms and reports of oracle...
  • Siddharth said: Hey thanks a lot !! Have been lookin all over for this :) My PC may not be able t handle FlashBuilder...
  • Cyrex said: Hey man why I cant install Apex 4? is there anyone can help me.,?
  • sharanabasava said: ALTER TABLE [my_table_name] RENAME COLUMN [current_column_name] TO [new_column_name]; Sir,I...
  • Huub Vankan said: Thanks for this easy solution. Indeed strange that is does not work in the (x86) folder….

Calendar

July 2010
M T W T F S S
« Jun    
 1234
567891011
12131415161718
19202122232425
262728293031  

Featured Testimonial

"M&S has taken 'partnership' to a new level. They are not only a deeply skilled service provider, the M&S team is actually a mission critical component to our operations, taking on new initiatives as well as maintaining custom, open source, and COTS solutions."

CEO, MedPivot

Free SQL Service