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.
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.