from: 把Oracle表裡的資料導成insert語句
有些時候我們需要把oracle裡的資料匯入其他資料庫裡。產生insert into 表名 .... 是一種很簡單直接的方法。 今年六月份從www.arikaplan.com/oracle.html看到一個可以產生insert into 表名 ....語句的預存程序genins_output。按中文習慣的時間格式YYYY-MM-DD HH24:MI:SS改了改，並新寫了一個預存程序genins_file.sql。 它可以把小於16383條記錄表裡的資料導成(insert into 表名 ....)OS下檔案。 調用它之前，DBA要看看資料庫的初始化參數 UTL_FILE_DIR 是否已經正確地設定: SQL> show parameters utl_file_dir; 可以看到該參數的當前設定。 如果沒有值，必須修改資料庫的initsid.ora檔案，將utl_file_dir 指向一個你想用PL/SQL file I/O 的路徑。重新啟動資料庫。此參數才生效。 調用它，可以把表裡的資料產生(insert into 表名 ....)OS下檔案的過程genins_file方法: SQL>exec genins_file('emp','/oracle/logs','insert_emp.sql'); | | | 表名，可變 | 產生OS下檔案名稱,可變 | utl_file_dir路徑名,不變(我設定的是/oracle/logs) 可以在OS目錄/oracle/logs下看到insert_emp.sql檔案。 注意事項: 產生(insert into 表名 ....)OS下檔案最多32767行。因為我一條insert分成兩行,所以最多處理16383條記錄的表。 附：genins_file.sql
code:
CREATE OR REPLACE PROCEDURE genins_file( p_table IN varchar2, p_output_folder IN VARCHAR2, p_output_file IN VARCHAR2) IS -- l_column_list VARCHAR2(32767); l_value_list VARCHAR2(32767); l_query VARCHAR2(32767); l_cursor NUMBER; ignore NUMBER; l_insertline1 varchar2(32767); l_insertline2 varchar2(32767); cmn_file_handle UTL_FILE.file_type; -- FUNCTION get_cols(p_table VARCHAR2) RETURN VARCHAR2 IS l_cols VARCHAR2(32767); CURSOR l_col_cur(c_table VARCHAR2) IS SELECT column_name FROM user_tab_columns WHERE table_name = upper(c_table) ORDER BY column_id; BEGIN l_cols := null; FOR rec IN l_col_cur(p_table) LOOP l_cols := l_cols || rec.column_name || ','; END LOOP; RETURN substr(l_cols,1,length(l_cols)-1); END; -- FUNCTION get_query(p_table IN VARCHAR2) RETURN VARCHAR2 IS l_query VARCHAR2(32767); CURSOR l_query_cur(c_table VARCHAR2) IS SELECT 'decode('||column_name||',null,''null'','|| decode(data_type,'VARCHAR2','''''''''||'||column_name ||'||''''''''' ,'DATE','''''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'')||''''''''' ,column_name ) || ')' column_query FROM user_tab_columns WHERE table_name = upper(c_table) ORDER BY column_id; BEGIN l_query := 'SELECT '; FOR rec IN l_query_cur(p_table) LOOP l_query := l_query || rec.column_query || '||'',''||'; END LOOP; l_query := substr(l_query,1,length(l_query)-7); RETURN l_query || ' FROM ' || p_table; END; -- BEGIN l_column_list := get_cols(p_table); l_query := get_query(p_table); l_cursor := dbms_sql.open_cursor; DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native); DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 32767); ignore := DBMS_SQL.EXECUTE(l_cursor); -- IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN cmn_file_handle := UTL_FILE.FOPEN (p_output_folder, p_output_file, 'a',32767); END IF; LOOP IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list); l_insertline1:='INSERT INTO '||p_table||' ('||l_column_list||')'; l_insertline2:=' VALUES ('||l_value_list||');'; UTL_FILE.put_line (cmn_file_handle, l_insertline1); UTL_FILE.put_line (cmn_file_handle, l_insertline2); ELSE EXIT; END IF; END LOOP; IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN UTL_FILE.FCLOSE (cmn_file_handle); END IF; END; /
