| Using SQL or PLSQL [message #7310] |
Tue, 03 June 2003 23:52  |
Naveen
Messages: 75
Registered: June 2001
|
Member |
|
|
Hi all,
We are developing an application with Oracle(8.1.5) as backend. My question is, when should i ask my programmers to use simple SQL and when they should call my procedures. Is there any rule set. If the application is selecting the data, then should i only write a simple sql and if inserting or deleting then procedures. What Tom (asktom.oracle.com) says is that, if it is possible with one single sql stmt then go for sql, if it is not possible go for plsql. But in some other quetion he recommends to go for procedures( actually packages) even to return result sets. I am confused. Please someone help me.
Thank you
Naveen
|
|
|
|
| Re: Using SQL or PLSQL [message #7311 is a reply to message #7310] |
Wed, 04 June 2003 00:13  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
It is certainly a very subjective issue. I have worked with all three configurations:
1) All SQL in the app - virtually none in procs.
2) Selects in the app - all other DML in procs.
3) All DML in procs.
From a database perspective, I certainly would favor option #3. It gives the DBA/developer complete control over the SQL that is run and enhances security by not letting any app user have access to the tables themselves. It is great for tuning (all queries are grouped in one place - the backend). You never have to randomly look at the v$sql view or trace a session to see just what SQL the app is executing - or trying to execute. You can slipstream tuning/query optimizations without disturbing the application .exe.
On the other hand, I can put on my frontend developer hat and see that if I'm creating a new window, it would be so much easier if I could just drop in the SQL that I need. Do I really have to submit a request to the Oracle developers (who are usually a separate team) to add a new proc and result set? Even if you do both front and backend work, do you really want to go the extra mile and do those steps?
What are some other opinions here?
|
|
|
|
| Re: Using SQL or PLSQL [message #7317 is a reply to message #7311] |
Wed, 04 June 2003 06:18 |
Naveen
Messages: 75
Registered: June 2001
|
Member |
|
|
Hi Todd,
Thank you for your reply. Which of the three configurations you have listed, performs well. Which would you advice me to opt for. My front end is Java/JSp and we are using Model-view-Controller frame work. Which option is best for this architecture.
Thank you.
Naveen.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
