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
Solved

Convert .sql to .tab

Posted on 2013-02-06
7
696 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

856 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