Subscribe: BOBABLOG: Oracle, PHP and Extreme Programming
Added By: Feedage Forager Feedage Grade B rated
Language: English
database  find  good  great  idea  ideas  list  maybe  new  oracle  patch runner  patch  people  problem  sql  step  team  time 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: BOBABLOG: Oracle, PHP and Extreme Programming

BOBABLOG: Software development and Extreme Programming

Updated: 2017-08-29T09:08:17.142+01:00


Thanks Quincy, glad you appreciate the post!


Thanks Quincy, glad you appreciate the post!




I owe a huge debt to Clive Hughes, the UX expert w...


I owe a huge debt to Clive Hughes, the UX expert who has been working closely on our projects for the last 12 months for the lengthy discussions on approach as well as diligence and awareness in designing and implementing the processes we now follow.

Without his input our processes and systems would be much poorer...

Cheers for the feedback.It's good to hear that oth...


Cheers for the feedback.

It's good to hear that other people think along the same lines, especially when they reach the same conclusions independantly.

I've got to be honest, since we've developed our in-house tool I've found any great need to look into the problem in any more depth (our solution works really really well for us). I'm sure there must be similar tools out there that people have put together. I just don't know what they are.

As ever, I think the biggest problem is the slow uptake in the vast majority of the Oracle community to new ideas. It's not that the community doesn't have great people doing great things - it's just that there's a huge amount of 'Oracle' people making money off the back of doing things badly. I found I didn't really get moving until I'd crossed into PHP / Java environment and started to get some cross-polination.

It's probably not an Oracle problem as such - just something that happens when people work in one arena for too long without any real outside influences.

Still, back to the original question...

I haven't used any off-the-shelf tools, but take a look at Ruby's database migration support. I've not tried it, but it sounds very very tasty. May prove an inspiration..?

And if your tool is so good, why not open source it (once you get rid of access ;-)). I know, I could ask myself the same question...

And get yourself registered onto the agile DB group over on Yahoo groups: There's a lot of great people posting over there.

Once again, thanks for your input

Hi there,Excellent stuff. Strange to find after y...


Hi there,

Excellent stuff. Strange to find after years of working with Oracle, I’d independently built the tool using PL/SQL as the “patch runner” and MS Access for the screen based front end. Thought was being innovative – seems others came to the same conclusion.

The screens allow you to create “Applications” (so you can manage multiple applications or schemas in parallel), register schemas (users – to which changes will be applied), and register the database changes (create table, alter table etc).

I’ve personally used DVM (Database Version Manager), in four projects, and it’s based upon an idea developed to manage over fifty developers. It works a treat, and means we guarantee that what we developed in “DEV” works in “LIVE” with close to zero DBA script management time.

TOAD and Embarcado seem to rely upon schema differences (a shockingly complex and quite hit and miss affair ).

Question – is there a commercial version?

I find my own home grown tool absolutely vital – surprised it’s not been incorporated into a commercial product yet. Any ideas? Is there one aready I don’t know about?

Good description though !!

Suggestion:Publish. Whatever you learn should be r...



Publish. Whatever you learn should be recorded, as a quick note at least. This will create an expanding pool of knowledge, let people browse through and make connections. Also will tend to focus effort on answerable questions, or at least interestingly unanswerable ones.

Cross-pollinate. Encourage teamwork between job categories and functions. Developers should tag along on sales calls. Ad people should observe a development meeting.

Matt, that sounds spot on. Great idea.In our impl...


Matt, that sounds spot on. Great idea.

In our implementatio, the patch log has a timestamp that states the moment that each individual patch was installed, so we can easily use that. That gives us the opportunity to rollback to any state during the upgrade if we wanted to.

Mind you, the original post was written before we'd played with 10g much, and we toyed with other mechanisms in 9i before we realised that we never needed to back anything out anyway. Because the upgrades are so thoroughly tested we find that nothing ever actually goes wrong when you hit the live site.

Hi Rob,You say that there is no way to automatical...


Hi Rob,

You say that there is no way to automatically back out a patch that has failed. Instead you have to manually piece back together the destruction you have caused.

I thought of a potential solution to this, though I have not had chance to test it out yet.

Essentially at the start of each patch you write the current timestamp to an easily accessible place, potentially a specifically created table somewhere in Oracle. If the script fails then you make use of Oracles flashback database facility (10g onwards) and "flashback" to the last time stamp you wrote.

Please let me know what you think.


Matt T

This is a great list, and it's gotten me to finall...


This is a great list, and it's gotten me to finally toss Fowler into my Amazon checkout queue.

I found the GoF "Design Patterns" book rough going without some accompanying exegesis -- I'd put the HeadFirst book next to it as a twofer.

I'd add "Envisioning Information" by Tufte.

web hosting resources:


web hosting resources:

Stew, it seems the team agree with you and have go...


Stew, it seems the team agree with you and have got into the idea with gusto.

There's a chance that we might have to turn up the dial a little and hand out more than was originally planned.

I don't think it's unreasonable to expect your pro...


I don't think it's unreasonable to expect your programmers to read, or at least skim, one new book every 3 months.

As a software developer with 25 years under my belt (but I'm not a software engineer), I've felt for a long time that if you're not constantly learning new things about this craft, then you're falling behind. Subscribing to computer mags is one thing, but scanning the web for new tools and ideas about your specific work is a must-do. And reading significant works by solid authors is another. I still refer people to "The Mythical Man-Month", which sits on my office bookshelf.

Excellent list! I'll be passing it around the tea...


Excellent list! I'll be passing it around the team. Thanks.

Good points Phil. I'll have to check out that HBR...


Good points Phil. I'll have to check out that HBR article.

You're right. I shouldn't have been surprised when the team were up for the idea - after all, if they were the type of people to shirk such ideas we wouldn't have hired them in the first place.

It's funny though, having spoken to people outside of software development, even those for whom I have a lot of professional respect, there seemed to be a general opinion of "you can't really ask your team to do that". Maybe it was just the particular people I spoke to; maybe there's a more general apathy in the rest of the working world?

The latest Harvard Business Review touches on your...


The latest Harvard Business Review touches on your questions in an article discussing the tension between training and promotion in-house and hiring from outside.

What I took away was that absolutely you can ask staff to contribute to the learning process through use of their own time (or possibly even more than that) since there is no obligation on you to develop them in this way in the first place. You just need to make sure it's opt-out.

For new hires, I'd ask the question in an interview ('We have a scheme where the company provides books from a standard reading list for free. If you take the sceme up you're obviously expected to read them in your own time. If you join us would you be interested?'). Anything less than enthusiasm for the idea is probably a red flag since who wouldn't want free books?

Nice one Andy - Refactoring gets my vote too.I've ...


Nice one Andy - Refactoring gets my vote too.

I've not read "The Effective Executive" though, maybe that should hit my own reading list...

Also check this post out for other ideas:http://fe...


Also check this post out for other ideas:

What about Fowler's classic "Refactoring"?I'd also...


What about Fowler's classic "Refactoring"?

I'd also add Peter Drucker's "The Effective Executive" and no it's not a business book, Drucker considers any thought worker to be an executive.

I wouldn't have a problem with reading educational...


I wouldn't have a problem with reading educational books in my time. But heavy volumes can be a real pain on public transport.
Also consider a way of circulating articles, white papers and presentations.

Yep Shannon, you're on the money.It can get cumber...


Yep Shannon, you're on the money.

It can get cumbersome whichever way round you do it. At the moment we're still keeping our patches small and haven't added another layer of recording into the patch runner, though we're now at the point where we have more than a couple of hundred patch files sitting in our release. It's no big deal really I suppose, but we are considering producing a new greenfield build at a version before which we would not move. It's going to be a bit of a task, but it will speed our development builds up a little.

Whatever you do, whichever option you take, there are pros and cons. You've just got to make sure you understand what they are first, and then react to them when they change.

What if you record each step within your PL/SQL pr...


What if you record each step within your PL/SQL program and condition each step (Each step being a DDL activity)?

Your patch runner then not only examines for the version, but the last successfully implemented step within the versioned patch. Another possibility is to break out each DDL statement as a patch...this could get cumbersome as well!

Laubster.I reckon you've got it already. It almos...



I reckon you've got it already. It almost doesn't matter what you want to do in an error condition, all that matters is that you want to do something.

You can have the patches installed by SQL scripts and then have the controller exit on error, but the problem is the complete loss of context when that happens.

You can have your Shell / DOS / ANT / PHP / whatever script perform actions based on that exit, but you normally need to dig out your context from some log file, by interrogating the database, or similar in order to put something useful into your database log / e-mail / xml report file.

If you have some way of keeping the control in the PL/SQL layer then you have the native exception handling available to you and the context of the failure remains. It makes it much easier for you to perform actions based on the context.

Obviously, there are ways of getting round it and still running pure SQL scripts, we just found it was easier to stay native.

Plus, if you feel the platform independance of the patch running is important to you, you can define a return mechanism from the PL/SQL layer that results in your own proprietry exception mechanism being used to cross the boundry between PL/SQL and your patch runner implementation. This would be handy if you needed to implement the database layer in another language / against another platform. Your patch runner might deal with the e-mailing / logging / etc, but you still need to the PL/SQL layer to reform the expections into something more generic and handleable.

"The patch is written in a procedure so that we ca...


"The patch is written in a procedure so that we can use the Oracle exception handler to propagate fatal errors in the code to a controlling procedure, something that is far more difficult to do in a script."

Platform independence is another benefit of using plsql, but for a moment let's ignore that.

What does the controlling procedure do? In
I think the conclusion was that on an error, you've got to get your hands dirty (or at least investigate whether you got lucky). Maybe it writes an error status into the db, maybe it sends email... anything else?

I ask because I'm considering a controller written in shell that runs sqlplus (with WHENEVER SQLERROR EXIT) for the db work. Haven't yet envisioned a need for UTL_FILE and DBMS_SQL with that approach, but I haven't even started a design doc yet (and certainly not pseudocode).

Thanks so much for the wonderful blogs on this topic. :-)

No problem... good luck with the implementation.Ma...


No problem... good luck with the implementation.

Maybe the build script could detect which packages specs / bodies have changed and then only compile those? I'm sure that wouldn't prove too difficult.

Can you get the connection pool to refresh its connections - maybe just drop the lot and recreate them on request? You would get a brief drop in performance as each new connection was made but it should pass fairly quickly.

One method I've often considered for such circumstances, but never actually implemented, is to run two (or more) mirrored databases. Take one offline (drops all its connections) upgrade it and then bring up (allows connections) and then repeat with the other. That way you still get a drop in performance, but everything's available through the upgrade and the connections are refreshed.

Still - easy to say - never once thought about the details of how you might do it.

Let me know how the synonym idea goes... If I could find enough minutes I'd probably test it myself - but my lunch hour has now gone...

what if forgot to mention is that our company is t...


what if forgot to mention is that our company is trying to achieve downtime free deployments (or as little downtime as possible). This maybe a futile effort - but there is a lot of pressure to achieved this.