No, your DBA is being silly (i actually have much stronger words for what they are being -- but this being a public forum and all)

You can justify for very good reasons why DBMS_OUTPUT should there.

Your DBA cannot justify squat.

Maybe your DBA thinks Oracle should remove the EVENT system we have for dumping debug? Maybe your DBA thinks the code for generating trace files should be removed from the kernel? Maybe your DBA wants to make it physically impossible to track down issues and problems?

Maybe in order to "make them happy", you could global change DBMS_OUTPUT.PUT_LINE to "MY_PKG.P" and create a package

create package my_pkg
as
procedure p( p_str in varchar2 );
end;
/

create package body my_pkg
as

procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;

end;
/

in test and in production:


create package my_pkg
as
procedure p( p_str in varchar2 );
end;
/

create package body my_pkg
as

procedure p ( p_str in varchar2 )
is
begin
NULL;
end;

end;
/


That way

a) you can debug in dev with lines > 255 even...
b) in production, your DBA is a happy camper
c) when you have a "bug" you make them put the right body back in, in order
to get the debug back out.

so? when they want to debug their code -- eg: they get the call "hey, code broke, help" -- what do they do then?

why bother commenting it out? it is a waste of time and energy. Then you have code in production that is not the same as code in dev and test. The slightest difference could cause issues.

Use my "my_pkg.p" approach -- then they could turn p into something that uses utl_file instead of dbms_output even -- this stuff is way to precious to comment out. It has saved my butt a gazzilion times over -- even I have bugs in my code. without this sort of stuff -- i would NEVER be able to fix it. I've dedicated a rather large section of my next book to this topic and how it is so short sighted to outlaw or remove it!!!


a) who said they had to be kept in sync? The developers having their own database is so that they can test ideas, play with concepts. Development takes place in development. If they develop on their desktop -- they have to deploy their changes to development for testing.

b) so? they are - as i said -- to test with, to play with. It is not dev -- it is a playground. A total loss of these databases is not material to anything.

c) that is a problem in your PROCESS then, doesn't matter if they have a database or not on their machine does it? You have this problem with or without a playground

d) why? why do they come "running to you" for an obvious error on their part???? not getting it.

e) why????? I'm not getting it at all. You are talking about migrating data here -- not a test case.

f) BECAUSE THEY DO THEIR REAL WORK IN DEV which gets migrated to TEST which gets rolled out to PRODUCTION. the thing on their desktop -- lets them tune queries without having to bend your arm to get the trace files. Lets them use autotrace without forcing you to give access to v$ tables. Lets them test ideas without asking you to grant them privs (eg: create any context, wanna test out this context thing -- see how/if it works -- prove that it is the best AND THEN I'll approach that DBA guy and see if I can bend his arm to grant this to me in DEV)


But to make them physically REMOVE Debug code -- boy -- you are way off base -- way way off base. Your basic premise is sooooo wrong (my 20cents worth, my experience).

When I said "no insert/update/delete/select" statements -- I was (implicitly -- should have stated it) refering to hand generated code like VB, Java.

Yes, if using a tool like Powerbuilder or Forms -- having select/insert/update/delete is virtually inescapable for practical reasons.

dbms_output is TURNED OFF by default. Your premise is wrong. to make they PHYSICALLY REMOVE IT IS WRONG.


There is overhead -- overhead something you can and should avoid.

Then there is mandatory stuff -- stuff that must be in there forever. This falls into this category. You want to see some? Just change the NO to YES in the URL above. I would Never in a billion years remove my debug/timing code. I need it CONSTANTLY.

You said (i quote)

I recently recommended that developers should comment out the
dbms_output.put_line from the code which goes into production.


you made them REMOVE IT -- it is commented out. It is not there. They cannot get it again without physically changing code and putting it back in and recompiling. You let them do that in prod when there is an issue???

I'm not judging you -- I'm saying "you are wrong to do this". Period.


sigh -- why is everyone missing this point of the database on the desktop. You use it to test ideas, to learn, to try things out. You want to find out which approach takes more latches (latches being locks, locks being serialization devices, serialization devices being the thing that kills scalability)? You need a single user instance running in isolation. It is a playground, a scratch database. It is not your development box, it is a playground, It gets wiped out -- big deal, nothing lost, just use dbca and recreate it. See also:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6349391411093 <code>
where we discussed this playground database.



I agree a well controlled development, test, production setup is mandatory, I think I've said that more then once here as well. However, if the developer wants to prove that bind variables are better then not using bind variables -- well, they need to flush the shared pool for example. Will you let them do that? Oh yeah, they need to kick everyone else off during the test to make the numbers meaningful. Will you let them do that? No, of course not. So, what can they do then? Well, if they have that little playground instance....

And if the developer cannot migrate his code from his machine to DEV -- how pray tell will it go from DEV to TEST magically? Also, I never said "develop on their desktop" -- that is what DEV is all about. Nuff said on that topic.

Your example with the COM stuff -- that'll happen between DEV and TEST and TEST and PROD. Thing is -- if what you are testing is database stuff -- it won't happen between Oracle and Oracle -- I develop on linux to deploy to solaris or even windows all of the time. We have the same bugs on all platforms so it ports pretty easy.


UAT was not delayed. UAT did what UAT was designed to do. Catch these issues.

And with the developers not being able to have their own schema to test with -- that's a particularly bad example. First -- the pain inflicted here was 100% self induced -- the programmer was the entire cause. What if they had just written the code and not moved it into source code control or something. Having their own schema is not the cause here -- a person making a mistake -- yes. Tell me -- when this same programmer who can no longer test in their own schema AND cannot have a local database wants to compile new functionality into a package that everyone uses -- what then? How many hours will be wasted by:

o the programmer waiting for everyone to stop using it
o the other developers waiting for this programmer to work the kinks out -- you know, debug this piece of code that lots of people use


All of the issues you describe happened without desktop databases -- it is all a project management issue -- all of it policy and procedures. I'm not saying to develop on the desktop -- but boy, if you want to test an idea, having it there is a nice thing.

However, we got so far off topic here -- we were talking debug code which you are making them physically remove. That is a bad policy period. The Oracle data is heavily instrumented and we can turn it on at will (hey, you know that, Support had you do that to track down an issue!!!)

You seem to say "i want homogenous code" but you make them comment out code -- it is no longer homogenous. Perhaps there was a side effect from calling dbms_output that made the code work in test and dev. Removing the call to dbms_output introduces a bug that was not detected. So following your methods we must not only remove the dbms_output from prod but also from test and therefore DEV.

True story: Some C code. Littered with fprintfs to a debug file (had

if debug fprintf( .... )

all over). Someone says "hey, code will run faster if we don't have those if's, comment them out. don't need to test or anything, same code right..."

Well, C being C -- the physical removal of code changed some pointer locations and exposed a nasty application related bug that wasn't exposed when the fprint's were in the source. Bummer, works in test -- debug shows it works -- doesn't crash, all is well in the world. But production -- doesn't work at all. Why? it isn't the code we developed. It isn't the code we tested. It is code sans debug code. So, not only were we handicapped in trying to figure out what was wrong -- we had no debug we could run -- we couldn't reproduce the issue (cause there we did have debug on).

What if the dbms_output somewhere has:

dbms_output.put_line( 'and the answer = ' || f(x) );

well, commenting that out blindly -- just making them remove it -- could easily materially affect the code. What does F do? Is it important? Maybe they really wanted f as a procedure, didn't care about the return value and this was a cheap way to call it (in their mind). Blindly removing the dbms_outputs -- well, that could change the outcome.

You haven't caused any offense, I'm still trying to convince you that leaving that debug code in there is justified and the right thing to do. Making them physically remove it is a very bad idea. If you want to outlaw a scratch database on the desktop -- so be it. I only see positives, no negatives. Every negative you came up with was pretty much policy based. Take the COM+ dll example. Do you think there would have been ZERO hours of fixing if they had the "bad dlls" from the beginning? No -- they would have fought the DLL he%#@ for N hours on the real machine as well (been there, done that, never will I program windows again personally). Not a chance -- they would have spent 4 hours on the dev machine saying "why isn't this working as it should".


Anyway.

everything will have this "issue" that the developers can ignore the tools. Everything.

You need to use a check in/check out metaphor if you want source code control, if you want configuration management, if you want to know whats going on

This is no different then if you were writing a C program and some wanted to use visual C and others wanted to use borland C or whatever. You would need to use source code control tools.

PLSQL is just code.
Java is just code.
VB is just code.

if you want configuration management, versions, trackability -- you'll be using source code control tools to do it.

yes -- in fact I pretty much do not put the empty body in myself -- I let the code stay there since having it there saves so much time at the end of the day (when you need it, you NEED it)

the "compile an empty body" is for those who would not otherwise let the code in even.

you can use cvs, rcs, whatever you want with jdeveloper (which also does plsql and does debugging of plsql and so on).

they would check code out of CVS (or whatever), modify it, compile it, then check it back in. anyone that just read code out of database and modified it would get 15 demerits and be made fun of, unless they do it like 10 times then they are shunned from the group and given nothing interesting to do.

Toad might even have hooks into such a system.

You might even have to introduce the concept of the real development database and not even grant them create procedure, YOU or your team checks out the code they've checked in and compile it for them after they test in their testing instance.

code is code, ddl is code, inserts are code, creates are code, code is code

and code needs Configuration Managment, especially if you have more than 2 or 3 coders.

the problem would be is that if everyone logs in as "themselves", they would need "create any" privs in order to create the objects in some other schema (very powerful). You would be able to use auditing to track this however.

Better would be - developers test schema changes in their own little database (to get syntax right) and then submit to development dba who checks in changes, then reviews changes, then runs changes.

you need a real DBA then - you have an infrastructure (political, non-technical) problem here.

To create something in XXXDEV you would need to either:

a) log in as XXXDEV in the first place (not as "yourself")
b) grant CREATE ANY privs to the developers and they can create table XXXDEV.T ...
c) create a procedure in XXXDEV that accepts a string and executes it, grant execute on this procedure to the developers, then they can:

exec xxxdev.run_this( q'| create table t ( x varchar2(5) default 'x' ) |' );

(in 10g, quoted identifiers can look like that, in 9i and before

exec xxxdev.run_this( 'create table t ( x varchar2(5) default ''x'' )' );

would have to be used)


a) not a good solution, what you have now.
b) not a good solution, create any privs are extremely powerful
c) so so solution - xxxdev needs to have all CREATE privs granted directly (not via a role, roles not active in that definer rights procedure), quotes become an issue, sql is hidden in strings....

1) yes.

2) it is no work? Developers test ideas in their schema (immediately, without waiting). When idea is fully baked, developer checks in change so that it can be configuration managed and rolled out (with ability to un-roll it out) to the rest of the planet.

It should be no work - less work anyway than "every one in the pool, do whatever you want whenever you want to the stuff we are all sharing, it'll be fun!"

3) no you don't. Or, call the person doing the DBA like stuff something else, maybe "head developer responsible for change management".

why not use a debugger? it is free (sql developer, on otn)


see also http://asktom.oracle.com/tkyte/debugf for an approach.

... You probably mean each developer will create code in his own schema but it will
run against the database table in the application schema.
...

that is one approach.

sam - there are dozens of possible setups, it would depend.

I would actually like the developers to have their own scratch instance on their desktop for initial testing if I had my way (personal edition - all features)



... (make
sure no dnamic sql is run because they will delete objects that way)
...

that statement makes no sense. you get nothing special or magic from dynamic sql, it is not any different than 'static' sql - since static sql is just an attribute of a precompiler (like plsql has static sql, but it is all really dynamic sql under the covers)