Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert .sql to .tab

Posted on 2013-02-06
7
Medium Priority
?
716 Views
Last Modified: 2013-02-27
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.
0
Comment
Question by:capturetheflag
  • 3
  • 2
  • 2
7 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 800 total points
ID: 38859390
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 38903417
...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
 
LVL 1

Assisted Solution

by:capturetheflag
capturetheflag earned 0 total points
ID: 38913472
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:capturetheflag
ID: 38915218
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38915219
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 38918244
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
 
LVL 1

Author Closing Comment

by:capturetheflag
ID: 38933234
thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question