ant-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Lindwall <JLindw...@Xifin.com>
Subject RE: Creating database scripts via ant
Date Wed, 10 Mar 2004 18:44:49 GMT
Thanks for the reply but your suggestion doesn't solve my problem (unless I'm doing something
wrong?).

I'm using TOAD 7.3.0.0.  I enabled the option "Tools/Spool SQL/To File..." and then executed
"Database/Export/Export Table Scripts".  In the SQL spool file I can indeed see the SQL being
issued by TOAD to perform this function (which is a pretty cool trick to remember for the
future).  But this captured SQL alone does not give me what I want.  

The SQL that TOAD is issuing is allowing it to collect the info it needs to then generate
"CREATE TABLE" DDL and "CREATE CONSTRAINT" DDL, et al.  This is the raw material for the generation
of the DDL creation.  This DDL generation is the functionality I want to locate -- "point
the tool at a database and out comes a file filled with DDL to create that database".  TOAD
contains that code internally AFAIK so your suggestion doesn't solve my problem.

Surely there's some open source tool that performs this task.

Thanks anyway!


FYI: Here's an example of the spooled SQL:

3/9/2004 4:18:53 PM

SELECT /*+ CHOOSE */ table_name, tablespace_name, pct_free, 
 pct_used, 
 initial_extent,
 next_extent,
 pct_increase,
 degree, instances, decode(ltrim(cache),'Y', 'CACHE', 'NOCACHE') cache
, INI_TRANS, MAX_TRANS, MIN_EXTENTS 
, MAX_EXTENTS 
, FREELISTS, FREELIST_GROUPS 
, CLUSTER_NAME, IOT_NAME, PARTITIONED, IOT_TYPE, LOGGING 
, TEMPORARY, DURATION 
, table_type, table_type_owner 
 FROM user_all_tables where 
 table_name=:tabname

:TABNAME = ACLS
3/9/2004 4:18:53 PM

Select /*+ CHOOSE */ column_name as Name, nullable,
 data_type as Type,
 Decode( data_type, 'NUMBER', data_precision + data_scale, data_length ) Length,
 data_precision Precision, data_scale Scale, data_length dlength, 
 data_default from
 user_tab_columns
 where table_name = :TABNAME
 order by column_id

:TABNAME = ACLS
3/9/2004 4:18:53 PM

 select /*+ CHOOSE */  a1.constraint_name, 
 c1.column_name, c1.position
 from 
 user_cons_columns C1,  user_constraints A1
 where C1.table_name = A1.Table_name
 And  C1.constraint_name = A1.Constraint_Name
 And  C1.owner = A1.owner 
 and A1.constraint_type = 'P'
 and A1.table_name =:TabName
 ORDER BY 3

:TABNAME = ACLS
-- etc etc

-----Original Message-----
From: Matt Reason [mailto:matt.reason@movielink.com]
Sent: Tuesday, March 09, 2004 3:25 PM
To: Ant Users List
Subject: RE: Creating database scripts via ant




Toad has a setting (I forget where - and it takes a day to find) that
will show you all of the queries it is running to do it's dirty work.
Once you enable it, you will see exactly what query it runs on the data
dictionary to get the definition of your database.  Grab that query and
save it to a file along with your custom statements.   Then use ant's
sql task or exec to kick it off.

-----Original Message-----
From: John Lindwall [mailto:JLindwall@Xifin.com] 
Sent: Tuesday, March 09, 2004 3:23 PM
To: 'user@ant.apache.org'
Subject: Creating database scripts via ant


Our company maintains it's database definition via a  "master database"
which is copied for production and test uses.  I prefer  to have SQL
(DDL) scripts that create and populate my database for development  and
testing.  What I do now is use a GUI tool called TOAD to create SQL
files (from the master database) that create the database structure
(table  structure, functions, sequences, etc).
 
Whenever the master  database changes I need to manually run the GUI
tool to regenerate the  scripts.  Then I customize them a little to
clean up some 
items.  I'd  rather run an ant target that generates these files for me
so I can automate the  task.
 
Any  pointers?
 
Thanks!


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message