Re: Pro*c vs Stored procs

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.

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.

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.

There are doubtless many more arguments in favour of one approach or the other!

Hope that helps a little.

Graham.   Received on Wed Jun 17 1998 - 14:39:11 CDT