dynamic sql vs overloading

Hi,

we are writing an application in C++ that needs to query Oracle and modify some data in underlying tables. As we have a lot of methods and attributes I was asked for the advantages of using dynamic SQL vs using static calls to a package with overloading of the parameters passed in package's procedures where necessary.

I know that dynamic SQL is useful for DML statements, and for flexibility, and I know that packages are useful for improving performance. But which solution is better in terms of performace ?

Therefore I thougt to share my doubts with You, as I really lack in experience in this field.

Thanks
Paolo

I'm not exactly sure what you are asking.

What it *sounds* like is that you are asking about hard-coding SQL in the app vs calling procedures. That one's simple: NEVER PUT SQL IN THE APP!! Period. Done. End of Story. (My opinion, of course) This is a big issue and I don't want to get into it all now, but there is a supportable argument about using views instead of packages for the query-portion of the abstraction layer. What is *not* debated is the need to remove *all* DML SQL from the app. Procedures should *always* be used for DML.

Given that, you must realize that *Dynamic* SQL can still be used inside the procedures (or the packages). A package is simply a collection of procedures and functions (and types and attributes, etc.). All access to the database should be done through packages.

Now, as to the parameter issue. The problem, as I understand it, is to allow the ability to do updates on a table when you don't know what combination of columns they might provide. You then want to know if overloading an update procedure will work. Well, it would, but it would be *major* over-kill. Mainly because if there 5 columns in the table, you must make 5! procedures to cover every permutation. A better solution is to make a single procedure with all the columns optional. Then, only update the columns for the parameters that were passed - using Dynamic SQL inside the procedure. Of course, you may still need several procedures, as you may not always have the PK handy. So, UpdByPK, UpdByAK1, etc. would work for single-record updates. UpdByFK1, etc. would work for multi-record updates.

Make sense?

- Chris

BTW - *warning - book reference :D*
The ERwin template that I am working on (and will be in my book) will generate such procedures for each table automagically.