Do You Develop Your PL/SQL Directly in the Database?
- by thatjeffsmith
I know this sounds like a REALLY weird question for many of you. Let me make one thing clear right away though, I am NOT talking about creating and replacing PLSQL objects directly into a production environment. Do we really need to talk about developers in production again?
No, what I am talking about is a developer doing their work from start to finish in a development database. These are generally available to a development team for building the next and greatest version of your databases and database applications. And of course you are using a third party source control system, right?
Last week I was in Tampa, FL presenting at the monthly Suncoast Oracle User’s Group meeting. Had a wonderful time, great questions and back-and-forth. My favorite heckler was there, @oraclenered, AKA Chet Justice. I was in the middle of talking about how it’s better to do your PLSQL work in the Procedure Editor when Chet pipes up -
Don’t do it that way, that’s wrong
Just press play to edit the PLSQL directly in the database
Or something along those lines. I didn’t get what the heck he was talking about. I had been showing how the Procedure Editor gives you much better feedback and support when working with PLSQL. After a few back-and-forths I got to what Chet’s main objection was, and again I’m going to paraphrase:
You should develop offline in your SQL worksheet. Don’t do anything in the database until it’s done.
I didn’t understand. Were developers expected to be able to internalize and mentally model the PL/SQL engine, see where their errors were, etc in these offline scripts?
No, please give Chet more credit than that.
What is the ideal Oracle Development Environment?
If I were back in the ‘real world’ of database development, I would do all of my development outside of the ‘dev’ instance. My development process looks a little something like this:
Do I have a program that already does something like this – copy and paste
Has some smart person already written something like this – copy and paste
Start typing in the white-screen-of-panic and bungle along until I get something that half-works
Tweek, debug, test until I have fooled my subconscious into thinking that it’s ‘good’
As you might understand, I don’t want my co-workers to see the evolution of my code. It would seriously freak them out and I probably wouldn’t have a job anymore (don’t remind me that I already worked myself out of development.)
So here’s what I like to do:
Run a Local Instance of Oracle on my Machine and Develop My Code Privately
I take a copy of development – that’s what source control is for afterall – and run it where no one else can see it. I now get to be my own DBA. If I need a trace – no problem. If I want to run an ASH report, no worries. If I need to create a directory or run some DataPump jobs, that’s all on me.
Now when I get my code ‘up to snuff,’ then I will check it into source control and compile it into the official development instance. So my teammates suddenly go from seeing no program, to a mostly complete program.
Is this right? If not, it doesn’t seem wrong to me. And after talking to Chet in the car on the way to the local cigar bar, it seems that he’s of the same opinion.
So what’s so wrong with coding directly into a development instance?
I think ‘wrong’ is a bit strong here. But there are a few pitfalls that you might want to look out for. A few come to mind – and I’m sure Chet could add many more as my memory fails me at the moment. But here goes:
Development instance isn’t properly backed up – would hate to lose that work
Development is wiped once a week and copied over from Prod – don’t laugh
Someone clobbers your code
You accidentally on purpose clobber someone else’s code
The more developers you have in a single fish pond, the greater chance something ‘bad’ will happen
This Isn’t One of Those Posts Where I Tell You What You Should Be Doing
I realize many shops won’t be open to allowing developers to stage their own local copies of Oracle. But I would at least be aware that many of your developers are probably doing this anyway – with or without your tacit approval.
SQL Developer can do local file tracking, but you should be using Source Control too!
I will say that I think it’s imperative that you control your source code outside the database, even if your development team is comprised of a single developer. Store your source code in a file, and control that file in something like Subversion. You would be shocked at the number of teams that do not use a source control system. I know I continue to be shocked no matter how many times I meet another team running by the seat-of-their-pants.
I’d love to hear how your development process works. And of course I want to know how SQL Developer and the rest of our tools can better support your processes.
And one last thing, if you want a fun and interactive presentation experience, be sure to have Chet in the room
