Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Query table vs query Stored procedures/views???
Hello all
Re: Run inserts and select querys directly on the database tables or should all table access be via views /stored procedures?
I have mentioned this ont this newsgroup before, but did not actually come up with a definitive answer. I realise that it is more an 'it depends' type of issue, but the application architecture is now better known, and this may have impact.
The application is to be an n-tier app developed in VB6.
The client will be developed in VB and will access a Business Services
layer which will reside in COM+ on an application server. This Business
layer will apply business rules.
The Business services layer will in turn access a Data services layer,
which will reside inside COM+ on the same application server. The Data
services layer will be responsible for all database access, which will
include kicking off stored procedures. Since we wish to be able to use
the connection pooling facility of COM+ a single user ID will be used
for all dB access with security being applied in the middle tier.
Keeping in mind that a primary goal of the architecture is ease of maintanance and enhancement what approach should be followed?
We could use the data services layer to build SQL strings as required, and then use these directly on the tables, or we could call stored procedures with parameters for the filters which then builds and executes the query, returning the result to the data services layer.
Conversly, when writing records to the dB we could use stored procedures with a host array which does the inserts rather than firing off a host of inserts from the data services layer.
I kinda imagine a primary reason for limiting access to be via views and stored procedures rather than direct table access is for data security. This should not be an issue since the users do not have Oracle logins. The feeling is that it will be complicating the issue if we need to keep track of a large set of oracle objects.
Later
Charles Received on Fri Jun 29 2001 - 10:07:17 CDT