Re: Query table vs query Stored procedures/views???

Spencer

We do intend to use stored procedures to do any type of data manipulation that is fired off by the user and will then affect a large number of records (special costing processes etc).

As for the reason as to why Oracle 8i. - well that was executive orders!

Spencer wrote:
>
> "Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote in message
> news:3B3CB835.6555E3FD_at_attws.com...
> > Charles McDonald wrote:
> >
> > > 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
> >
> > I think this is more a question of religion than hard cold fact. And
> > application security requirements weigh heavily in the decision making
> > process.
> >
> > My answer would be as follows:
> > Given no performance issues I would perform all table maintenance
 activities
> > through procedures written in packages. When performance becomes an issue
> > ... you make the compromises necessary to achieve adequate performance
> > without compromising data integrity and security.
> >
> > Daniel A. Morgan
> >
>
> i'd have to agree with Daniel. you are going to get a variety of
> approaches... based more on personal bias and experience...
>
> to achieve maximum availability, security, performance, and flexibility...
>
> the application should connect to the database as a user OTHER than
> the owner of the objects, and be granted only the required privileges...
>
> all DML (insert/update/delete) statements should be in PL/SQL packages.
> complex SELECT statements should also be stored in PL/SQL packages
> (functions that return result sets as ref cursors.) any other queries in
> the
> application should be performed against views... or, actually, a private
> synonym that refers to a view...
>
> i agree that this adds a level of "complexity" on the database, and that
> complexity is to be avoided... not avoided "at all costs"... but avoided
> where it does not provide any benefit...
>
> as far as avoiding "vendor lockin" for the database, you've already selected
> several technologies that are available only from a single vendor that run
> only
> on that vendor's platform...
>
> again, as Daniel says, there are several factors that you need to weigh in
> making architecture decisions... but if you do not plan to use features
> like
> views or PL/SQL packages, which factors led you to you choose Oracle
> as the RDBMS ?
Received on Mon Jul 02 2001 - 09:39:02 CDT