| Schema Selective Copy [message #228949] |
Wed, 04 April 2007 11:03  |
Balgarvie
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
B
|
|
|
|
|
|
| Re: Schema Selective Copy [message #229158 is a reply to message #228952] |
Thu, 05 April 2007 10:33  |
Balgarvie
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:
SELECT 'INSERT INTO '||chr(38)||'IN_SCHEMA..BANK_ACCOUNTS(
ACQUIRER
, ACTIVE_FLAG
, ADDRESS1, etc
)
VALUES ( '''||ACQUIRER||'''
, '''||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.
Thanks
B
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
