Re: Pro*c vs Stored procs

Graham Thornton wrote:
>
> Lisa Lewis <lewis_at_med.pitt.edu> wrote in article
> <6lpajh$pr9$1_at_usenet01.srv.cis.pitt.edu>...
> > Hi, I was hoping someone could give me some guidelines on whether to do
> most
> > of my interaction with the Oracle db via Embedded sql in a pro*c program
> or
> > via stored procedures that are called from a pro*c program. I am
> currently
> > doing everything in stored procedures even simple inserts into tables
> > because this is what I am most comfortable with. What are the
> ramifications
> > of developing this way. Is it better to use embedded SQL with Pro*c
> program
> > to do processing? What are the advantages and disadvantages? Can
> someone
> > please give me some guidelines. It would be greatly appreciated!!!!
> >
>
> As you've probably guessed, there and pluses and minuses to both
> approaches....
>
> Stored Procedures can be faster than Embedded SQL, since Oracle keeps them
> loaded and does not have to re-parse them each time, and can provide much
> greater flexibility and security than is possible with Embedded SQL.
> However, you will need to create a maintain a second set of source files
> besides the Pro*C files. In a client-server environment, stored procedures
> can reduce the number of "trips" to the database server, by performing some
> of application logic on the server.

One side note: if your packages start to get large in size, Oracle must swap them in and out of memory. Obviously, this is still faster than E-SQL, but does hinder performance under 'high volume' applications.

In addition, if your parameter list to the stored procedure, you will have to recompile the C code that calls the stored procedure.

> Embedded SQL reduces the maintenance headache, by keeping all of the
> application logic together, and you don't have to worry about packages or
> procedures becomming invalid, but often requires more complex SQL
> statements. Also note that PL/SQL does require some resource within the
> database, and not all databases (Oracle lite??) support stored procedures.
>
> Personally I use a great deal of packaged PL/SQL, since it provides me with
> the option of reorganising the underlying table architecture as new
> requirements become known, without having to alter the PL/SQL interface to
> the outside world. It also means that I do not have to spend valuable time
> writing complex embedded SQL statements - I can write a quick and dirty
> proc for now, and go back and optimise it later. For very simple queries
> however, I still tend to use Embedded SQL.

I also use a lot of packaged pl/sql for several reasons: 1. It is more flexible than E-SQL.
2. Encapsulation -- if you have certain rules for insert/update/delete on table(s), then you can place all those rules in one package, pass the data in, and make sure your rules are enforced. With E-SQL, you would have to find *each* location where the insert/update/delete takes place and modify the rules there. This can be problematic because it is easy to miss some code. The result is buggy code. 3. Our database has 2 interfaces: a telephony application (IVR) and an Oracle forms interface. The forms developers and I write pl-sql packages that are used by both applications; this is for encapsulation and because we're lazy! We don't want to write the same/similar code twice!

> Basically, I see no reason why you cannot take a mix-and-match approach,
> using either technique as and when you see fit. Of course, as with all
> development efforts, it is more important that you properly document what
> you've done.
>

I agree.

> There are doubtless many more arguments in favour of one approach or the
> other!
>
> Hope that helps a little.
>
> Graham.
>
>

--
Jeffery Cann
Fairway Systems, Inc.
Senior Software Engineer Received on Wed Jun 17 1998 - 15:50:24 CDT