| Basic tools for Oracle version control: wanted! [message #521075] |
Fri, 26 August 2011 04:03  |
 |
Jollo
Messages: 5
Registered: August 2011
Location: Italy
|
Junior Member |
|
|
Hi all,
I'm looking for 3 simple things... well, I thought of them as simple, but I'm realizing they're not to be taken for granted!
1) a reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.
2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).
3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.
I must be one out of a couple million people asking for the same things over and over again: I've seen the questions all over the Internet but I could find no straight answer. Please help!
Thanks and take care.
|
|
|
|
| Re: Basic tools for Oracle version control: wanted! [message #521079 is a reply to message #521075] |
Fri, 26 August 2011 04:18  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Don't know anything that does all of that. Lot's of DB tools will give you the scripts but I know of none that'll put them in an appropriate tree structure.
2) PL/SQL Developer does schema diffs. I wouldn't be surprised if TOAD and SQL Developer do as well. I doubt you're going to find an open source one though.
|
|
|
|
|
|
| Re: Basic tools for Oracle version control: wanted! [message #521086 is a reply to message #521083] |
Fri, 26 August 2011 04:38 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I find it mind boggling that people insist on creating DB's and then trying to reverse engineer it to get the scripts.
Create scripts for objects, test them in a db by all means, then check them into source code and use them to generate test/prod DBs.
PL/SQL Dev doesn't have a command line interface as far as I'm aware.
|
|
|
|
| Re: Basic tools for Oracle version control: wanted! [message #521095 is a reply to message #521086] |
Fri, 26 August 2011 05:26 |
|
Jollo
Messages: 5
Registered: August 2011
Location: Italy
|
Junior Member |
|
|
In an ideal world, I would completely agree... but in the real world, there are only 2 sorts of IT professionals: those who already experienced being assigned to a running project with shaky (if any) version control for the database components, and those that will have such dubious pleasure in the future. You wouldn't believe how many otherwise knowlegable DBAs at large still swear that the one and only repository for schema information should be the prod instance =(
Anyway, if you ascertain that the scripts in your VCS are NOT in sync with the prod DB, you don't have many choices: either you modify the scrits manually, run them to build a dummy schema and repeat until your diff tool (which one?) says you're done, or you reverse engineer. Granted, it's something you only need to do once (or never, if you apply solid version control from the beginning), but if you need to you have to get it right at first try. Hence the quest for a reliable tool.
Anyway, thanks for your input: still, I'll be waiting to see if anyone else turns up some less depressing hints... Cheers!
|
|
|
|
| Re: Basic tools for Oracle version control: wanted! [message #521104 is a reply to message #521095] |
Fri, 26 August 2011 06:05 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
SQLTools has such an option, and creates a pretty good folder layout with the objects.
It's a little out of date, but it still works with Oracle 11.2.
One drawback that it has is that it doesn't work with Java stored procedures.
And on the VC:
Yes, I have all my code in a version control. But I'm not the only one changing stuff. So every time before I merge changes from VC into prod, I first export the production schema into a folder structure, and check it against VC. (The folder structure in VC matches the folder structure that SQL Tool creates, because that's basically where the code came from years ago) ;-P
-
Attachment: extract.png
(Size: 8.24KB, Downloaded 3564 times)
[Updated on: Fri, 26 August 2011 06:09] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Basic tools for Oracle version control: wanted! [message #530992 is a reply to message #521075] |
Fri, 11 November 2011 12:14 |
 |
hansbarnard
Messages: 1
Registered: November 2011
Location: United Kingdom
|
Junior Member |
|
|
Hi Jollo
Check out the tool Lure from the company Earthly Software Ltd. (I am not able to post a direct link to the website but just search for the website.) The demos on the website will give you a quick introduction of how this tool works.
This tool addresses 1, and 2 (except it compares the source code in the extracted DDL files with the target database)
Regarding 3, it is not open source but does have a command line interface.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
