Home » SQL & PL/SQL » SQL & PL/SQL » Schema Selective Copy
Schema Selective Copy [message #228949] Wed, 04 April 2007 11:03 Go to next message
Messages: 4
Registered: September 2006
Junior Member
I have been asked to write a script which will generate Insert statements for selected tables only within a specific version of a database. The idea is that those statements can be saved to a file and that file then taken to a second version of that schema and used to update it.

I decided to tackle it in two stages:
1. Generate a Select statement for each table I want to copy.
I'm saving that in a staging table which consists of one Clob column like so...
Select 'Insert into ' || chr(38) || 'In_Schema..Tariffs (Tariff_ID, Tax_Code) Values ('||nvl(TO_CHAR(TARIFF_ID),'NULL')||', , '''||TAX_CODE||''')'||CHR(13)||'/'
The reason I'm storing them as Clobs is because there are some very large table definitions in there.
This works fine up to this point. If I run those statements manually they give me the results I expect.

2. From this point on I'm in unknown territory. I think I somehow need run each of those select statements to generate the resulting Insert statements. I had it mind to do this via a cursor, saving the results in a second staging table. I can then do a save as against that table and that should give me the file I want.

The problem is that running the select statements can result in widely variable numbers of records being returned, and I'm not sure if I'm going about it the right way or if theres an easier way of achieving this. I don't have admin rights so theres a limit to what I can do.

Any suggestions would be gratefully recieved.

Thank you


Re: Schema Selective Copy [message #228952 is a reply to message #228949] Wed, 04 April 2007 11:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
From SQLplus, you can spool the generate statements directly to file. Also - you might find chr(10) better than chr(13) in your current approach.

set linesize 1000
set trimspool on
set feedback off
set long 1000
set pagesize 0
set heading off
set scan off
spool my_inserts.sql
PROMPT prompt Inserting#1 (Tarrifs)...
select 'Insert into &In_Schema..Tariffs (Tariff_ID, Tax_Code) Values ('||nvl(TO_CHAR(TARIFF_ID),'NULL')||', , '''||TAX_CODE||''');' from ...;

PROMPT prompt Inserting#2 ...


Re: Schema Selective Copy [message #229158 is a reply to message #228952] Thu, 05 April 2007 10:33 Go to previous message
Messages: 4
Registered: September 2006
Junior Member
OK, let me put it another way...

I have a single column table consisting of one record for each table in the schema, like so:
, ADDRESS1, etc
, '''||ACTIVE_FLAG||'''
, '''||ADDRESS1||'''
etc.)'||CHR(13)||'/'||CHR(13)||'' FROM BANK_ACCOUNTS

Copying and pasting these entries into toad then running them works fine, I get Insert statements for every record in the table.

I want to do away with the manual copy/paste exercise and run these statements "in-situ", saving the results into a second table. How can this be done ? I've tried nested cursors with no joy, is some sort of collection the best solution ?
I don't have access to the server file system so using the UTL File package isn't an option.

Previous Topic: droping all tables in a schema
Next Topic: Presenting rows as columns
Goto Forum:

Current Time: Sun Jun 25 04:03:35 CDT 2017

Total time taken to generate the page: 0.08111 seconds