Convert .sql to .tab

Hello Everyone,

I’m trying to export the DDL for each schema from TOAD to Subversion, by object type.  For example, I’m exporting all the table creation scripts for a particular schema.  However, the default extension for the scripts created is “.sql”.  Our guidelines suggest “.tab”.  I need to override the default to create scripts with the “.tab” extension. How can this be done?

Thanks for the help.
LVL 1
capturetheflagAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Not a Toad user so I'm not sure if there is a way to over ride the default extension.

Maybe a simple OS rename after you are done?

There is also a DBMS_METADATA.GET_DDL call you can make.  Then you can use sqlplus and spool the results to whatever file you desire.

spool mytab.tab
select dbms_metadata.get_ddl('SCOTT','SOME_TABLE') from dual;
spool off
0
 
MikeOM_DBACommented:
...Our guidelines suggest “.tab”.
So you invent guidelines completely different from the standards?
Why then complain if it makes it difficult for you to follow your own guidelines?
If you live by your own standards, you die by your own standards.
:p
0
 
capturetheflagConnect With a Mentor Author Commented:
I’ll show you the easy but not multiple file approach …

 

spool myfile.sql

select dbms_metadata.get_ddl('TABLE',TABLE_NAME, 'HR') from all_tables where owner='HR';

spool off

 

This way though you have to split them out one at a time by going through the file.

 

To split them out, you have to create what I call a “gen” script … save this file as tables.gen

What it will do is create a script that pulls all the tables (or other objects) and put them in the corresponding “.tab” file of the same name.

This is accomplished by the line   dbms_output.put_line('spool '||t.table_name||'.tab');

 

set serveroutput on

set echo off

set heading off

set feedback off

set verify off

set pagesize 0

set linesize 132

set long 90000

 

spool tables.sql

declare

l_sql varchar2(2000);

BEGIN

   dbms_output.put_line('set echo off');

   dbms_output.put_line('set heading off');

   dbms_output.put_line('set feedback off');

   dbms_output.put_line('set verify off');

   dbms_output.put_line('set pagesize 0');

   dbms_output.put_line('set linesize 132');

   dbms_output.put_line('set long 90000');

   dbms_output.put_line('exec DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''SQLTERMINATOR'', true)');

   dbms_output.put_line('exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,''STORAGE'',false)');

   dbms_output.put_line('exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,''SEGMENT_ATTRIBUTES'',false)');

   dbms_output.put_line('exec DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''PRETTY'', true)');

 

   for t in (select * from all_tables where owner='HR') loop

   dbms_output.put_line('spool '||t.table_name||'.tab');

   l_sql := 'select dbms_metadata.get_ddl(''TABLE'','''||t.TABLE_NAME||''', ''HR'') from dual;';

   dbms_output.put_line(l_sql);

   dbms_output.put_line('spool off');

   end loop;

END;

/

spool off

 

So once you have this, run it in SQL*Plus … it will generate a tables.sql file that looks like this …

 

set echo off

set heading off

set feedback off

set verify off

set pagesize 0

set linesize 132

set long 90000

exec DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', true)

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false)

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false)

exec DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', true)

spool REGIONS.tab

select dbms_metadata.get_ddl('TABLE','REGIONS', 'HR') from dual;

spool off

spool LOCATIONS.tab

select dbms_metadata.get_ddl('TABLE','LOCATIONS', 'HR') from dual;

spool off

spool DEPARTMENTS.tab

select dbms_metadata.get_ddl('TABLE','DEPARTMENTS', 'HR') from dual;

spool off

spool JOBS.tab

select dbms_metadata.get_ddl('TABLE','JOBS', 'HR') from dual;

spool off

spool EMPLOYEES.tab

select dbms_metadata.get_ddl('TABLE','EMPLOYEES', 'HR') from dual;

spool off

spool JOB_HISTORY.tab

select dbms_metadata.get_ddl('TABLE','JOB_HISTORY', 'HR') from dual;

spool off

spool COUNTRIES.tab

select dbms_metadata.get_ddl('TABLE','COUNTRIES', 'HR') from dual;

spool off

 

So in the “gen”erated script, you set some key DBMS_METADATA parameters so that your results look prettier …

You just run this script in SQL*Plus by

 

SQL> @tables.sql

 

Now you have a bunch of tab files for each table …
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
capturetheflagAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for capturetheflag's comment #a38913472

for the following reason:

Good answer!
0
 
slightwv (䄆 Netminder) Commented:
I'm afraid I'll have to object to you accepting your own answer as the only solution.  Did it not use information from a previous post?  The logic you posted looks pretty much like what I suggested.

I did not see the requirement of every table being in it's own script file in the original question.
0
 
MikeOM_DBACommented:
I completely agree with slightwv,

capturetheflag's solution not only seems to be a copy/paste from somewhere else, but is based on slightwv's suggestion.
0
 
capturetheflagAuthor Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.