Quick and dirty Oracle export/import

Language: English
Programming Language: SQL
Published by: jujiro [not registered]
Last Update: 5/15/2006
Views: 1398

Description

The code creates a script of insert statements for the existing data within a table. The generated script can then be run to re-populate the table.

Code

Display line numbers

1 -- By: Ashish Kumar 2 -- Date Created: 10/01/2001 3 -- EMail: kumara@jagat.com 4 -- Code Version: 1.0.1 5 6 -- Objective: 7 -- You can use the following code to extract the existing data from tables in the form 8 -- of insert statements. The generated script can be run at a later time to re-create your data. 9 -- This code is no match for EXPORT and IMPORT utilities. 10 -- Use it for *quick and dirty* situations. 11 -- The code handles only date, char, varchar2, and numeric data types. 12 13 -- Change History: 14 15 -- The example used in the code uses scott schema. 16 17 -- AUTHOR MAKES NO WARRANTIES FOR THIS CODE. 18 19 -- Step 1: Create this procedure: 20 create or replace Function ExtractData(v_table_name varchar2) return varchar2 As 21 b_found boolean:=false; 22 v_tempa varchar2(8000); 23 v_tempb varchar2(8000); 24 v_tempc varchar2(255); 25 begin 26 for tab_rec in (select table_name from user_tables where table_name=upper(v_table_name)) 27 loop 28 b_found:=true; 29 v_tempa:='select ''insert into '||tab_rec.table_name||' ('; 30 for col_rec in (select * from user_tab_columns 31 where 32 table_name=tab_rec.table_name 33 order by 34 column_id) 35 loop 36 if col_rec.column_id=1 then 37 v_tempa:=v_tempa||'''||chr(10)||'''; 38 else 39 v_tempa:=v_tempa||',''||chr(10)||'''; 40 v_tempb:=v_tempb||',''||chr(10)||'''; 41 end if; 42 v_tempa:=v_tempa||col_rec.column_name; 43 if instr(col_rec.data_type,'CHAR') > 0 then 44 v_tempc:='''''''''||'||col_rec.column_name||'||'''''''''; 45 elsif instr(col_rec.data_type,'DATE') > 0 then 46 v_tempc:='''to_date(''''''||to_char('||col_rec.column_name||',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')'''; 47 else 48 v_tempc:=col_rec.column_name; 49 end if; 50 v_tempb:=v_tempb||'''||decode('||col_rec.column_name||',Null,''Null'','||v_tempc||')||'''; 51 end loop; 52 v_tempa:=v_tempa||') values ('||v_tempb||');'' from '||tab_rec.table_name||';'; 53 end loop; 54 if Not b_found then 55 v_tempa:='-- Table '||v_table_name||' not found'; 56 else 57 v_tempa:=v_tempa||chr(10)||'select ''-- commit;'' from dual;'; 58 end if; 59 return v_tempa; 60 end; 61 / 62 show errors 63 64 -- STEP 2: Run the following code to extract the data. 65 set head off 66 set pages 0 67 set trims on 68 set lines 2000 69 set feed off 70 set echo off 71 var retline varchar2(4000) 72 spool c:\t1.sql 73 select 'set echo off' from dual; 74 select 'spool c:\recreatedata.sql' from dual; 75 select 'select ''-- This data was extracted on ''||to_char(sysdate,''mm/dd/yyyy hh24:mi'') from dual;' from dual; 76 77 -- Repeat the following two lines as many times as tables you want to extract 78 exec :retline:=ExtractData('dept'); 79 print :retline; 80 81 exec :retline:=ExtractData('emp'); 82 print :retline; 83 84 select 'spool off' from dual; 85 spool off 86 @c:\t1 87 88 -- STEP3: Run the spooled output c:\recreatedata.sql to recreate data. 89

Quick and dirty Oracle export/import

Description

Code

No comments avaiable

Add a comment