Procedures or plain SQL

Does anyone have any comments on the following situation?

Application:  		Client-Server Phone Support System
Front-end tools:	Visual Basic
Back-end:		Oracle 7.0.16 and another proprietary database
			on a mainframe (non relational).

To isolate the VB developers from the backend databases and needing to know in detail the database design and the database language, we are developing a data manager that sits inbetween VB and the two databases.

To assist with the development of the datamanager, we are proposing to provide procedures for most if not all of the DML statements.

This is where I step in and disagree with the approach of using procedures for all DML statements. The speed/cache advantage of using procedures/PL-SQL is also obtained via plain old SQL statements in the datamanager as there will be only one version of DM distributed to the clients PC.

Some of the silly procedures are
- user_edit_cancel()

• thingy_edit_cancel()
• obj_edit_cancel() These procedure do nothing but execute ROLLBACK.

To select ten users from the database we have a procedure to select ten records and return them via the procedures parameter.

As you can see it gets silly.

Other than the largish amount of memory and the slight overhead of executing a procedure as compared to a simple SQL statement, do you see any reasons why we should or should not use procedures for all DML statements?

Do you have any experience projects that use procedure EXTENSIVELY.

ta
tony

 _____       ________ / ___ |Tony Jambu, Database Consultant
  /_  _        /_ __ /      |Wizard Consulting, Aust (ACN 065934778)
 /(_)/ )(_/ \_/(///(/_)/_(  |CIS:100250.2003_at_compuserve.com FAX:+61-3-2536173
 \_______/                  |EMAIL:TJambu_at_wizard.com.au PHONE: +61-3-2536385