Receiver JDBC Scenarios can work for UPDATE, INSERT, DELETE, SELECT and EXECUTE actions. However Stored Procedure gives us some advantages and flexibility which makes it better. Moreover, all the transactional actions can be easily and efficiently handled via Stored Procs. As far as XI is concerned, i have carried out JDBC scenarios and found out some more advantages and disadvantages of using Stored Procs.
Advantages of Stored procedures
When used correctly, stored procedures can produce a big improvement in performance of Oracle databases.
- They are ideal for batch programs and complex business validation logic that need to interact with the database.
- The biggest benefits come from the use of packages which group together related functions, procedures and data structures.
- Oracle stored procedures are also ideal for simplifying complex SQL statements, but beware that there is an overhead in switching context from SQL to PL/SQL, but even taking this into account, they can still boost performance. You would obviously have to test this though, to make sure the context switching overheads won't outweigh the the performance improvements gained from using PL/SQL
What are the performance advantages ?
The main performance advantages arise from the fact that there is no need to send data back and forth across the network whilst it is being processed and from the already mentioned fact that stored procedures are tightly coupled with the database and are stored in pre-compiled form. This means that the data manipulation commands are processed much more efficiently than would be the case if an external program was used. The other benefit derives from the fact that the stored procedures will be run on the server hosting the database which is generally much more powerful than the pcs that run the client software.
When should stored procedures be used ?
Stored procedures are ideal when there is a complex piece of business logic that needs to be performed involving a lot of database access. If this logic is required in many different places, then even better.
For example when performing data entry, the front end program (written in VB or Oracle Forms or whatever) can perform simple validation such as checking that numbers are entered in numeric fields, proper dates are entered in date fields etc. But what if there is a need to validate information entered against existing data in the database due to business rules about the acceptance of the data and this validation involves a lot of manipulation of information from the database and complex business knowledge?
Then stored procedures come into their own - the client footprint is small, the network traffic is reduced and the server does all the hard work which is what it was designed to do, and due to the tight coupling of stored procedures with the Oracle database, the load on the server won't be that great.
How to get the maximum benefit of stored procedures
As mentioned in the definition a stored procedure can be a procedure, a function or a package (a grouping of related procedures, functions and data structures). The biggest benefit in the use of stored procedures comes from the use of packages, because whenever any element of the package is referenced, the whole package is loaded into memory and (assuming it is not aged out) remains in memory for future use. Also if other elements in the package are needed the overhead of calling them is very small, because they are in the same memory area. This means that you can and should group related program units together so that they can share data structures when needed and call each other with minimal overhead.
What else can you do with stored procedures ?
Stored procedures (preferably as part of a package) are also a great way to manipulate data extracted from a database before it is returned to the caller and hiding or encapsulating complex logic that needs to be performed in several places.
Stored procedures that are functions can also be embedded directly in SQL statements and used in the same way as the standard SQL functions such as TO_CHAR or TO_DATE. This only works with functions because they have to return a value implicitly. These functions can be stand alone but they are usually embedded within a package comprising related procedures and functions to take advantage of the fact that the whole package is loaded on first reference.
Disadvantages of Stored procedures