>I have one more related question here (maybe should start another thread). While trying to make this work, every once in a while a thought creeps into my head -- why go to the trouble when you already know how to do CRUD to Oracle via SPT and can encapsulate the SQL code into data access objects? I get this thought, despite the fact that I'm a big fan of using (the much simpler) CRUD SPs when the data is in SQL Server.

>Would you agree/disagree with any of these reasons that I give myself to continue trying to get this to work?

>1. Data access is "encapsulated" in the server, easing the job of coding middle-tier or UI (depending on whether you're using C/S or n-Tier) components. (But still, *someone* has to code the SPs, or at least code the builder and run it now and then).

>2. In a shop with a DBA, SPs may be the *only* way data can be updated and/or accessed (not an issue for me though).

>3. Performance will be better because Oracle can query plan the stored procedure (yeah, but it can also query plan the SPT we pass it, right?).

>4. Once I learn enough about PL/SQL and/or triggers, I can put the burden of maintaining data integrity on the server rather than coding that in my middle tier. For example, I'm envisioning that my eventual OrderDelete procedure in my eventual OrderData package will include two DELETE statements (one FROM Orders WHERE pk = nOrderPK and one FROM OrderDetail WHERE OrderFK = nOrderPK), both wrapped together in a transaction. IOW, from the middle-tier or UI, I'll need only SQLEXEC({call OrderData.OrderDelete(1)}) to delete the entire order (or fail, but leave the entire order intact). [But I could also do this with a trigger or I could also put code in the middle-tier to handle the cascade and the transaction.]

>I'm sure there are other arguments, but those are the main ones that creep into my thinking. #4 seems, at least in my environment, to be the only one that keeps me trying to get these Oracle packages to work. What are your (or anyone else's) thoughts wrt these or other reasons for going the SP route?

>Thanks,

Kelly,
To me #1 and #2 are why I am looking into it. Builders are "relatively" easy because the meta data is already there for you in the user_tables and dba_tables views. Also, I am a firm believer in using SPs when I can because of the security invloved. I may not want just anyone opening SQL Plus and inserting, updating, etc. without following some rules.

Yes you could build these rules into triggers but that's another object and it's a PITA to switch between all the different Oracle objects (at least to me). I would rather have all the code for a given business object (e.g. Inventory, Order) in a single package.

CRUD SPs also make it easier to update things as needed. If you build data objects that do anything other than call your SPs, you have to change them if a query needs to changed (for whatever reason). If you have these objects centralized as web services, it's not that big a maintenance headache but who has that yet? < s > Most n-tier systems are still local n-tier (objects compiled into a single desktop application) and not distributed. When something changes, you have to update it on moer than one system. If things are in SPs, you do it on the server and you're done (JVP mentioned this in one of his talks and it stuck with me).

I would continue going the way you're going. I just wish Oracle was as easy to return a cursor as SQL Server. It definitely is a lot of typing!