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
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.
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 🙂
26 Comments
I have 8 years of experience being Oracle PL-SQL Developer. I worked in sql developer IDE, TOAD and Allround Automation’s PL-SQL Developer IDE.
I really become a fan of sql developers light environment developer IDE but there are some annoying issue with it.
1. When we cancel execution of he SQL query – it got disconnected with database and it require to reconnect back again.
2. For debugging and unit testing it require so many rights and manual interventions need to take in SQL developer in compare to Allround Automation’s PL-SQL developer tool but it is compare to slow while debugging.
As of now in the market there are so many IDEs available for Java – Eclipse is the best example among them – it is light weight and robust debugging support having tool available in the market but again it do not support oracle pl-sql to debug.
We really are hoping that SQL developer tool may came up with the release which solve these issues.
1 – weird, normally i see it hang while it’s waiting for the db to come back, or it responds pretty quickly, but not a disconnect. Try doing a thick connection, cancel requests are more reliable over sqlnet vs jdbc
2 – we use JDWP for debugging, because it’s a java thing, and it’s more powerful than the older dbms_debug interface. Can you say more around the ‘slow’? I’ve used toad’s debugger for years and ours, and I’ve not seen any differences in performance.
We’re considering doing more for Eclipse – but if we did a debugger, it’s be JDWP based, so same requirements in the DB/network as you see in SQL Developer today.
Interesting perspective. How about this scenario … my local drive doesn’t get backed up, except my folder under Users. All data sits on network drives that get backed up. I have no issue with installing a db locally (and I currently have it), but if I loose the drive I loose my work since whenever I checked in last time the changes.
just take it another step. backup your database on a regular basis and put the media on your Users folder
So you want me to do some more work ( 😉 … just kidding) … versus admin worrying about backups on the network drives …. I suppose I could put the media on the network drive.
More work, no, not necessarily. You should always backup your work though, esp your code.
We backup everything, servers and network drives. Local machine images don’t get backed up (would be a lot of extra space), except User folders and some other scripted locations.
code is just text files, less room needed than for ppts and word docs. just back up your sql/plsql stuff there and don’t worry about it. or buy an external drive and backup your VM each night
How would you typically push the code out/deploy to development or other env dbs after you are done the development in your local, private db? Would you write like an ant extension to make a jdbc call to databases and deploy your pl/sql objects to all dbs when ready or does PL/SQL developer, SQL developer or Toad IDEs have options for this?
I’d check it into source control.
Hi
I’m enjoying reading this discussion. Do you have already have a post or would write one talking about: Now that you’ve done you’re development, how to manage the migration into production.
For some objects it’s trivial to just grab the most recent SVN revision of a DDL statement and execute in prod, but tables for instance, ALTER table is very different from CREATE table. And I don’t want to drop a 10gb table just to add one column and maybe do a one-time back fill of the data and then going forward this new column would get populated by the application.
So I’m just curious how others are managing DEV + SVN, and now you have to get these changes into PROD.
A good question for others here – or for another forum space like the ORACLE-L mailing list.
I do believe that there is some form of human interaction involved in order to keep track on if Bill is working on DEB_PACK.STORE_ORDER_INFO or “things” that are depending on that very part of the DEB_PACK package.
We have regular meetings on how, what and who should be doing items. Otherwise it does not matter if someone change a package locally, centrally, in production, in test or in development environment. It will be clobbered anytime…
The places I’ve worked at mostly use a single development database instance as a playpen for no more than about 2-3 developers.
One client used to have a DBA whose idea of deploying changes was to run a diff between Prod and Dev and run the resulting DDL in Prod. As you can imagine, a fair amount of rubbish found its way into Prod.
Nowadays, each developer is responsible for building a set of scripts to be handed over to the DBA to run in UAT and Prod. If they’re lucky, the scripts might be QA’ed by a senior dev first. Every now and then we’ll ask the DBA to refresh dev from prod, and all the developers are responsible for re-deploying all their scripts – which encourages the habit of checking their scripts in regularly, so that they’re always ready to re-deploy their changes to Dev.
We use TOAD or SQL Developer; some developers use the procedure editor to compile their code; but personally I prefer to use the “Run in SQL*Plus” option so I know if there’s going to be an issue with the script when the DBA finally gets it.
“But it worked when I compiled it in TOAD” is not an accepted excuse.
If the scripts are to be ran in SQL*Plus in production, then they MUST be tested using the same process. And you can replace SQL*Plus with fill-in-the-blank _______________.
Blindly running DIFF scripts would scare me. Trust but verify, except less trust and more verify!
I do development in the development DB. But I have been trying to get everything installed locally. I seem to keep running into setup issues with the software(not Oracle). And with the deadlines we have I do not have a week to devote to figuring it out. Hopefully once everything slows down I can get it working locally.
When you do get that time, I’m convinced the return on that investment in time will pay itself back for a very long time to come. Good luck getting that going!
Flashback on the data dictionary is a pain. You need to be system, or muck around with privileges too much. Extended auditting is better. You can see the SQL executed with that.
But I wouldn’t use that on a dev DB where people are recompiling code every 15 minutes.
So the pain isn’t worth the cost of auditing overhead? Would a dev care if her create or replace took an extra moment of two?
I agree with all of the issues both of you have raised here; which is the reason why we have developed a product at dbMaestro that answers to these disadvantages.
The issues that you mention can be summarized in these categories:
• How to control changes when working out of the IDE (when working with both SQL developer, SQL*Plus etc)
• Having a Version Control of all change types (structure and not less important content of lookup tables)
• Prevent Out-Of-Process changes (unplanned, undocumented, unmanaged etc)
• Prevent Code-Overrides between colleagues
• Having a repository of changes and ability to easily rollback changes and basically know who was doing what to the database
These issues are solved in our TeamWork solution by using a change policy enforcement embedded in the database engine, so we are transparent to which Oracle IDE you work with. This way, in order to modify an object (structure or content) you need to use a Check-out action.. Otherwise you will get an error from the Oracle engine. You cannot Check-Out an object that it is already Checked-Out by a colleague. When you Check-In, the object definition (or content) that exists in the database is stored in our repository, so you don’t need to sync between scripts and database any more.
We thought of using the Edition-Based Redefinition in order to support parallel development of the same object and use it as a private working environment before publishing the changes (similar to having a local instance of Oracle on my machine and develop my code privately). But we found that Edition-Based Redefinition does not support all main object types and of course it does not enable having several versions of lookup table content.
As our solution does not include an Oracle IDE (we preferred to focus on better database change management solutions, and collaborate with good existing IDEs) we intend on integrating with Oracle SQL Developer IDE as the first IDE.
I hope you find this information fruitful.
Uri
The complexity of this challenge is demonstrated by the number of solutions developed to address the pains of a plsql development team. Of course we have the new Lifecycle Management Pack in EM12c with integration support in SQL Developer, but I think the community benefits when we have more vendors encouraging them to buy..err..develop their software using industry accepted best practices.
You want us to implement a ‘Team Coding’ like feature? I understand why you like it, but I was never a big fan of it. It did nothing to prevent someone from going to SQL*Plus or any other tool to change the database objects.
As for editions, that’s a very interesting idea. Edition-Based Redefinition is one of those features than can be a real game changer. Let me think on that awhile.
And I’m not saying ‘No’ to Team Coding. Maybe if you can get enough of your friends to vote on it in the Exchange we can talk about it 🙂
That was a big bite Dom! Thanks for taking the time to share your experience with this topic.
I agree, it is hard to sync the database, filesystems, and source control repositories, and that’s probably why a lot of folks don’t even try.
Your idea to enable auditing in your development instance is a great one, and not something I have ever heard anyone mention before. Definitely a great resource to have around when something ‘weird’ happens with the database.
I’m confused though, if everyone in your system logs in as the schema owner, how does that-other-tool prevent people from editing the same objects at the same time – everyone is using the same account?
Ah, one other thing — if I had to request enhancements to SQL Developer in this area I’d like 2 things:
1. Something similar to Toad’s basic locking feature for editing PL/SQL code
2. More obvious indication of the edition I’m working in
Ok, I’ll bite.
I’ve managed a shop where we did all of our development directly in the central development database — staff of roughly 6-8 database-only developers. And yes, they all logged into the database as the schema owner to develop PL/SQL code — about 100 packages under management.
We standardized on Toad for development, using the rudimentary built-in locking feature of Toad that prevented more than one person from editing PL/SQL packages at the same time. Everyone used the editor in Toad to edit PL/SQL code so this worked for us.
Also, since I’m addicted to DDL auditing we turned that on in all databases to capture every structural change event — including the workstation, username, time, etc. of the change — made it easy to track down who had changed what. I’m a huge fan of this capability in every database I work with — no more “What happened?” nonsense about “Someone changed something”.
It’s hard to synchronize the file-system, source-code repository and database objects — with 3 potential locations there are a lot of potential disconnects. The SVN built-in capabilities in SQL Developer help, as well as the object-by-object unload scripting (which is a godsend for this).
In theory (I’ve never tried it) you also ought to be able to run flash back queries against the user_source table as well to capture and track source code changes — if it works it’s probably trivial to write a query that shows the change history for an object.
Of course, maybe if you’re really aggressive you have the database create a new edition every week (every day?) and have logon triggers that push you into the latest edition — that way your changes are tracked in editions. (Again, haven’t tried this, but wonder if it would work).
In the future I think you’ll see more of developer’s each having their own local copy of the database which is merged into the source code repository and then the central database — but that model is still maturing.
We are using files without extension with version control CVS. With the latest versions of SQL Developer (or CVS?), I am not able to see local history. There is already posting on OTN that talkes about the same issue:
https://forums.oracle.com/forums/thread.jspa?messageID=2664687�
The difference is File Type = UnrecognizedTextNode vs. PlSqlNode. I tried to add not able to add to the file type in Preferences to recognize files without extensions as SQL or PL/SQL but it does not work. Any idea?