Solved

Convert .sql to .tab

Posted on 2013-02-06
7
687 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 76

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 76

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
Article by: gr8gonzo
Git can be a complicated version control system for beginners, but it definitely is one of the best ones out there. Since this article assumes that you're starting at square one, it will skip over things that Git -can- do and will focus on the typic…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now