How do you put their SQL in the database? views? or returning CURSOR references from procedures?

Do you recommend putting all SQL code (selects, too) in procedures?

Gosh, what would be a good way to put SQL into the database instead of leaving it on the client-side? Oh, wait, I know, maybe something like... a stored procedure? or even better, a package and package body containing the procedures/functions? Then you could use simply use the *_SOURCE views if you needed to see the, um, well, source...

Also, I would never give a developer create privileges (or the CONNECT/RESOURCE roles) on PROD. EVERY object (ie, table, package, index, etc...) that goes into production goes through one of our DBA's desks first. Non-admin users get 'create session' and that is all they need (as far as system privs). Now, maybe if all our developers promise to read Tom's book and follow the guidelines, it would be a different situation... :) I can guarantee from the code that has been sent back to the developers lately, not much reading has been going on :( I mean, is it really that hard to use a bind variable, for instance? Ah, but I digress...

It's DBA job to move the code from the Development Database
to production database. Developer's shouldn't have access
for the production database. Any code before moved into
prodcution should be viewed by the DBA

I don't believe it is the DBA's job to give code the once over before putting it into a production environment. That's why developers are paid big bucks is because they know what they're doing (sometimes!). That's why a successful development team will always have an experienced developer who a) knows his code and b) knows the business to look over any code before releasing to production. As part of our development standards a developer must produce documented proof that they are using the most effecient method for retrieving data, whether that's via tkprof output or explain plans.

I don't believe it is the DBA's job to give code the once over before putting it into a production environment. That's why developers are paid big bucks is because they know what they're doing (sometimes!). That's why a successful development team will always have an experienced developer who a) knows his code and b) knows the business to look over any code before releasing to production. As part of our development standards a developer must produce documented proof that they are using the most effecient method for retrieving data, whether that's via tkprof output or explain plans.

Lionel misinterpreted my question. We use packages and package bodies extensively in our application; in fact, we don't use UPDATE or DELETE from clients at all. We do, however, use SELECT statements in the client. Is this a bad practice?
Perhaps my question should have been "Are you suggesting that we code SELECT statements into packages and return the cursor as an ORACLETYPES.CURSOR?"
In (the knowledgable) Tom's answer, he states "if you have them put their SQL in the database--you can fix (they can fix) poorly performing queries in the stored procedures...."
I assume he also means SELECT statements. Am I wrong?

Tom (from FL):

Didn't mean to go tripping on you, and, yes, I did misinterpret your question - my bad. Let's just say yesterday was one of those days where we had a slew of "consultants" in-house that "had never worked with Oracle before, but we know SQL Server." Like that is a big help. Anyway, what we do here (even though you didn't ask) is encourage developers to put as much in the db as possible. If the client allows it (and the developer can do it), I encourage returning ref cursors. Most client-side development here is done using VB and using Oracle Objects for OLE on the client-side allows ref cursors with no problem. Having said that, though, I am not a developer, so a developer may have better input. Sorry for the confusion again.

-LM

imho even experienced developers forget what the differences between DEV and PROD. So I require that a DBA install and compile stored procedures. I find that most developers are either inexperienced with Oracle or are not knowledgable about the configuration of the production environment.