Home » SQL & PL/SQL » Client Tools » Basic tools for Oracle version control: wanted!
Basic tools for Oracle version control: wanted! [message #521075] Fri, 26 August 2011 04:03 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13014
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 #521083 is a reply to message #521079] Fri, 26 August 2011 04:29 Go to previous messageGo to next message
Jollo
Messages: 5
Registered: August 2011
Location: Italy
Junior Member
Thanks for the reply: I had an horrible suspicion I would get that kind of answers... I find mind-boggling that such basic tasks haven't been automated yet. In fact, I am sure they have been automated hundreds of times over and over again ... by home-grown tools!

I am not insisting on open source: more than willing to pay for robust tools... but I need a command line interface! Does PL/SQL Developer do script generation via command line?

Re: Basic tools for Oracle version control: wanted! [message #521086 is a reply to message #521083] Fri, 26 August 2011 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 13014
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3205
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.

/forum/fa/9296/0/

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 675 times)

[Updated on: Fri, 26 August 2011 06:09]

Report message to a moderator

Re: Basic tools for Oracle version control: wanted! [message #521236 is a reply to message #521104] Sat, 27 August 2011 04:03 Go to previous messageGo to next message
Jollo
Messages: 5
Registered: August 2011
Location: Italy
Junior Member
Thanks ThomasG for the info. Summary up to date:

SQLTools looks to be an acceptable surrogate for #1, even if you have to do top-level scripting (the ordering) yourself. Not a problem, though: reverse engineering is something you ought to do at most once (ideally, never).

SQLDeveloper and TOAD do diff scripts, but I'm still wondering a) if you can inwoke diff script generation form the command line (with options and all), and b) how confident can I be in those scripts? Is anyone actually using these tools for deploy script generation?

Thanks again for the support, please keep the info coming...


Re: Basic tools for Oracle version control: wanted! [message #521353 is a reply to message #521236] Mon, 29 August 2011 02:46 Go to previous messageGo to next message
Jollo
Messages: 5
Registered: August 2011
Location: Italy
Junior Member
Looks like Devart's "Schema Compare for Oracle" about meets the requirements... Does anyone have any real-life experience with it?

[Updated on: Mon, 29 August 2011 02:47]

Report message to a moderator

Re: Basic tools for Oracle version control: wanted! [message #521450 is a reply to message #521353] Tue, 30 August 2011 01:43 Go to previous messageGo to next message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
bit late now, but SQL developer has built in SVN plug ins, for when you fix this part!
Re: Basic tools for Oracle version control: wanted! [message #530992 is a reply to message #521075] Fri, 11 November 2011 12:14 Go to previous message
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.
Previous Topic: SQL*Plus error
Next Topic: supressing output
Goto Forum:
  


Current Time: Thu Feb 22 03:34:27 CST 2018

Total time taken to generate the page: 0.03596 seconds