PL/SQL is a database programming language. This means that your source code is compiled into and executed from within the Oracle Database. There are many fantastic consequences of this fact, many of which are explored in Bryn Llewellyn's Why Use PL/SQL? whitepaper.
But this also can mean that developers see the database as the natural repository for the original source code, and this is a bad mistake to make. It's not the sort of mistake any JavaScript or Java or php developer would ever make, because that code is not compiled into the database (well, you can compile Java into the database, but that's not where 99.99% of all Java code lives).
But it's a mistake that apparently too many Oracle Database developers make.
So here's the bottom line:
Hmm, that might sound kind of odd to some of you.
But it was not designed to be a development environment per se. The requirements developers have for writing code is certainly very different from requirements users have for accessing and changing their data.
So Do This
Suppose I need to create a package. I type the following in my worksheet in SQL Developer (a free, powerful IDE for Oracle Database):
Notice I used the ".pks" extension for my package specification. You could just use the default ".sql" for all your database code files, but I prefer to use specialized prefixes for my program units, views and more.
Then I compile my code, and start working on it. And any time I need to go back and work on the package specification some more, I open the file.
I never, never edit a program unit directly from the database. Sure, you can do that. It is "allowed." It's just not recommended. Seriously. Not. Recommended.
Lost Updates - Lost CODE Updates, That Is
You can double-click on your program unit name in the Schema Browser. That code will appear in an editor. You can make changes and compile it back into the database.
But what if another developer on your team is doing the same thing? A minute after you double-clicked on the program name, Sandra did, too. You make changes, which Sandra doesn't see. Sandra makes changes, which you can't see.
You compile your changes into the database. You give yourself a pat on the back: Job well done.
And then you go to lunch.
At which point, Sandra compiles her changes into the database. She eats lunch at her desk, editing and compiling changes to ten more program units. A busy, productive programmer.
And when you come back from lunch? Your changes are gone.
Note: Yalim Gerger (@yalimgerger) of Formspider offers a very interesting tool, Gitora, the offers a PL/SQL API to manage your database objects directly in Git. So if you really, really don't like my advice and really, really want to work on code directly in the database and avoid files entirely, check out Gitora.
But this also can mean that developers see the database as the natural repository for the original source code, and this is a bad mistake to make. It's not the sort of mistake any JavaScript or Java or php developer would ever make, because that code is not compiled into the database (well, you can compile Java into the database, but that's not where 99.99% of all Java code lives).
But it's a mistake that apparently too many Oracle Database developers make.
So here's the bottom line:
Store each PL/SQL program unit in its own file. Use a source code control system to manage those files. Compile them into the database as needed for development and testing.In other words: you should never keep the original version of your code in the database, especially if you are part of a development team, with multiple people needing to work on a given program unit, sometimes at the same time. Otherwise, chaos might ensure, resulting in "lost updates" to your code.
Hmm, that might sound kind of odd to some of you.
"Steven, who works for Oracle Corporation, who thinks that Oracle Database is a really great database, is telling us to avoid the database?"Well, I do think Oracle Database is a really great database. And I do think that you can trust it with your data, your applications, the care and feeding of your users. :-)
But it was not designed to be a development environment per se. The requirements developers have for writing code is certainly very different from requirements users have for accessing and changing their data.
So Do This
Suppose I need to create a package. I type the following in my worksheet in SQL Developer (a free, powerful IDE for Oracle Database):
Then the very next thing action I take, because I hate to lose any of my work, is to save this text to a file:
Notice I used the ".pks" extension for my package specification. You could just use the default ".sql" for all your database code files, but I prefer to use specialized prefixes for my program units, views and more.
Then I compile my code, and start working on it. And any time I need to go back and work on the package specification some more, I open the file.
I never, never edit a program unit directly from the database. Sure, you can do that. It is "allowed." It's just not recommended. Seriously. Not. Recommended.
Lost Updates - Lost CODE Updates, That Is
You can double-click on your program unit name in the Schema Browser. That code will appear in an editor. You can make changes and compile it back into the database.
But what if another developer on your team is doing the same thing? A minute after you double-clicked on the program name, Sandra did, too. You make changes, which Sandra doesn't see. Sandra makes changes, which you can't see.
You compile your changes into the database. You give yourself a pat on the back: Job well done.
And then you go to lunch.
At which point, Sandra compiles her changes into the database. She eats lunch at her desk, editing and compiling changes to ten more program units. A busy, productive programmer.
And when you come back from lunch? Your changes are gone.
There are several ways in which database programming is really different from Java, JavaScript, etc. programming. Some of these ways are super cool, such as: there is no need for a make utility or process. The database takes care of all of that for you.
But in other ways, writing PL/SQL program units is - or should be - no different from writing a Java class or a JavaScript...um...script.
You figure out the algorithm you need to implement the requirements.
You translate that algorithm into code. You type that code in an editor.
You save it to a file, and then you compile that file into the database.
You check in that file to your source code control system.
Need to make more changes? Check it out (or not, depending on how your tool works), open the file, and work on that file. And so on, and so forth.
By taking this approach, there is a much smaller chance that code changes will be lost.
And a much greater chance that a whole team of developers can work together efficiently to developer a highly secure, very efficient, easy to maintain base of code in PL/SQL that can be utilized by UI developers.
Note: Yalim Gerger (@yalimgerger) of Formspider offers a very interesting tool, Gitora, the offers a PL/SQL API to manage your database objects directly in Git. So if you really, really don't like my advice and really, really want to work on code directly in the database and avoid files entirely, check out Gitora.
Hi Steven,
ReplyDeleteyou are so right!
In our Company we even go further:
We are using Subversion (SVN) and every PL/SQL-source-code-file is marked with an SVN-property called "needs-lock".
So, if a developer wants to checkout and change a package one first has to get the lock for this package.
Why?
Because there is no "sandbox" for changing database packages and there is only one version installed in the database.
With the "needs-lock"-Property we ensure that there will be only one person doing changes on the package.
Greetings,
Marc (m.bisping@brillux.de)
Great post. It's a simple but powerful tip!
ReplyDeleteThis tip has to do with discipline that a developer must follow, always. But unfortunately most of them don't care about it, they usually just want to make the things work.
"They usually just want to make the things work"
DeleteI think that could serve as a kind of alternate definition of a developer. Very pragmatic.
Steven,
ReplyDeleteI agree with your approach.
However, do note that gitora saves your database code to file in your git repository and then checks it in to git.
It does this automatically.
It's not what you mean, but it's not "avoid files entirely" either.
Thanks for clarifying, Erik.
DeleteAs the oldie that I am, this post really made me smile :):):)
ReplyDeleteI ALWAYS write my code in a text editor, already since the old good days of the VMS mainframe ... and I kept that for Windows as well (guess which one ? NOTEPAD !).
As per my knowledge, the source control tools were implemented
( and cost a lot of money !) to do exactly this: prevent more than one person working in parallel on the same code object,
and this is why a check-out should be performed prior to any change.
But this does still allow several people to work on the same piece of code one after the other, if not simultaneously ...
Keeping my code ALWAYS, but ALWAYS saved in script files
is the only way by which I can be sure that it is completely secure against any alterations, either on purpose or by mistake ...
And, in addition, it was also very convenient as a "history"
of a piece of code, for answering later questions that always arise, when nobody is remembering exactly what happened in the past ...
So ... I finished my career as still a believer in the old good text file :):):)
Cheers,
Iudith Mentzel
Oldie but goodie.
DeleteGood to Know this :).
ReplyDeleteIt is always good to follow Best practices to avoid rework.
Best Regards,
Abhishek
Hi Steven and all!
ReplyDeleteRead that idea in thatjeffsmith's blog some years ago and always thought that it was good idea (and is the way svn source control works in SQL Developer) but it does not completely avoid the lost updates it just gives you more "security" of having your work saved on your side.
We are currently using SVN and using BEFORE DDL triggers to check if the package was locked by the developer before he can make any change.
Thank you Marc for the idea, I will check that property to see if I can gain anything in our current flow.
PS: We did not use gitora when this all started for two reasons: first, Yalim hadn't made it publicly available at the time (although he had already announced it to be available shortly); second, our sysadmins were reluctant to provide us with a Git repository.
PS 2: I believe the git model fits greatly with 12c. I'm looking forward to do this upgrade so I can - more easily - give every developer on the team it's own database.
Hi Steven,
ReplyDeleteI think that the approach you are defining in this post is one of the two alternatives to do safe DB development. Another one being having a local database for every developer and merging changes to a shared database environment as a part of build process initiated by pushing changes to the git (or other VCS) master/build branch.
Your approach requires that every time one wants to compile the code/ check the code for warnings/errors, one needs to check in all the changes in to the repository. This is really inconvenient and unimaginable in the Java/JavaScript/non-DB world. Another downside of this approach is that Database IDEs are not designed to work with files. Take Oracle SQL Developer for example and let's say I have all pl/sql code units in separate files. Is there a way to see all my local project files nicely categorized as they are under the Connections tree (but inside DB)? How can I navigate from local file of package1 to the local file of package2 (not the package2 inside DB) with ctrl+click or shift+f4? Not to mention that I cannot even use completion insight for the code that is not yet in the database (which can easily be done for java code which is not yet compiled/built for deployment to the application server). In other words, I can only work with one piece of database code unit at a time then check in, then compile to the DB. Or otherwise I am losing the things that IDEs provide to make the development easier (like completion insight, compilation to check for errors, etc.). In my opinion, pl/sql or database development requires one to compile your code quite often and, thus, would require to check-in almost every little change to the repository. Well, checking-in often is a good thing but for me this would be too much and would very much slow down the development although I usually check-in every
bigger piece of work done but for the reasons of having rich history/a way to go back.
I don't see any reason why this approach should be taken instead of having separate local databases for each developer. Especially when there are tools like Vagrant, Chef/Puppet which can fire a new database for development in minutes. And there are tools that can diff the single-point-of-truth database with the dev database to make a "deployment" script (Oracle SQL Developer does that nicely, also RedGate's tools and others).
Kind regards,
@JuliusZaldokas
Thanks for your detailed feedback, Julius. I agree completely with the idea of each developer having their sandbox to play in - but you then do need to factor in merging changes from independent "play" in the sandbox. I don't know about you but I am wary of relying solely on an automated merge process with changes across 2,3,5 developers - at least not without lots of code review to examine the end result!
DeleteMy suggestion about using files was intended mainly to warn developers against the incredibly simplistic and naive model of working directly out of the database (unless you have something like Gitora in place and it all works smashingly well).
You are absolutely correct that SQL Developer and other Oracle Database IDEs rely on a database connection for all sorts of analysis and functionality. I don't think it makes any sense for the IDES to try to reproduce all that the database gives you just to avoid having to have a connection and compile in your code.
Bottom line: even if you have separate local databases for each developer, you should still save your code to files!
Yes, I agree that manual code review should be done with every merge to the shared dev/test environment and I absolutely agree that pl/sql code units should be saved to files and version controlled even though they aren't really suitable (in my opinion) for the development purposes.
DeleteI also agree that this is not a drawback of any DB IDE which should be solved - we need to accept that DB IDE won't do things that Java[Script] IDEs give us and I think we shouldn't say that DB development should be done the client-side development way because in many ways its very different in its nature and couldn't possibly be done the same way.
Anyway, safe and efficient teamwork for DB development and source control for DB (not only PLSQL but table/view/... DDLs) and then branching/merging/etc. are very complex things to implement and I haven't seen/heard/read of an ideal solution. There are many and they all have some cons/pros, in my opinion. Every time a post on this topic comes up in the blogosphere there is plenty of discussion which I hope will lead to someone coming up with a perfect solution some day :)
Kind regards,
@JuliusZaldokas
Hello All,
ReplyDeleteYes, the dream of each developer is to have his/her own database :):)
How true ...
( I did have mine, in fact :)
since I almost always worked ALONE on all my applications ... )
I even suggested this once in the past, at my work,
but of course it was not considered as a feasible solution
by the administrators ...
What I mostly wanted was to have a kind of "ghost" available,
by which I could create my own database (clone) at any time.
In 11gR2 and higher, one possible, though partial solution
could be to allow each developer to create and use a separate EDITION,
created as a CHILD of the official (parent) edition,
and then developing and checking ALL the editionable objects there,
before their final deployments into the official parent edition.
This will be quite close to having a private database :)
Best Regards,
Iudith
GIT + pkh,pkb,pkb,pks files (headers,body, proc,fun, view, triggers, scripts...) sooooo cool.
ReplyDeleteI've used all sorts of source repository tools (CVS, PVCS, SVN, VSS, TFN, and now more recently...Git Bitbucket). My problem with Git is that there is no concept of "locking" but that's for another discussion.
ReplyDeleteA company that uses the database as a repository and has NO source control is a company I do NOT want to work for.
Use a GUI tool, or even Notepad, or vi or emacs for Unix, save and compile, and repeat before going to QA and PRODUCTION.
Hi Steven,
ReplyDelete1) Coding with any language without using an SCM is just unbelievable especially at this era where the best SCMs are free!
2) Flooding a developer, any developer, with procedures is just counterproductive.
3) The solution is let the developers do what they do better, coding, and use software to implement procedure. And here come the importance of solutions like gitora.
I have been away from developing with PL/SQL since very long time. I have embarrassed other programming languages and ecosystems where the best practices to produce better software are well established and well supported with great software solutions most of them are free. I'm just surprised that PL/SQL developers are still short of support from Oracle and from their communities!
I hope that solutions like gitora emerge. I salute the gitora provider for his act to offer a free limited-access to his solution. I hope that gitora will turn someday to a fully free solution. I hope also that other solutions like gitora see the light.
Cheers
Younes