Solved

Convert .sql to .tab

Posted on 2013-02-06
7
702 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 77

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

726 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