How Does Oracle obtain DML (insert )?

From: imports data from the Oracle table into an insert statement.

Sometimes we need to import data from Oracle to other databases. Generating the insert into table name... is a simple and straightforward method. From www.arikaplan.com/oracle.htmlin January, we can see that the Stored Procedure genins_output of an insert into table name... statement can be generated. According to the Chinese habits of time format YYYY-MM-DD hh24: MI: SS modified, and a new storage process genins_file. SQL. It can export data in less than 16383 record tables into files under the OS (insert into table name. Before calling it, DBA should check whether the database initialization parameter utl_file_dir has been correctly set: SQL> show parameters utl_file_dir; to see the current settings of this parameter. If there is no value, you must modify the database's initsid. ora file and direct utl_file_dir to a path where you want to use PL/SQL file I/O. Restart the database. This parameter takes effect. Call it to generate data in the table (insert into table name ....) operating System File process genins_file method: SQL> exec genins_file ('emp', '/Oracle/logs', 'insert _ EMP. SQL '); | table name, variable | file name under the generated OS, variable | utl_file_dir path name, unchanged (I set/Oracle/logs) you can see the insert_emp. SQL file in the OS directory/Oracle/logs. Note: A maximum of 32767 rows of files can be generated (insert into table name...) in OS. Because one insert statement is divided into two rows, a maximum of 16383 records can be processed. Appendix: genins_file. SQL

Code:
Create or replace procedure genins_file (p_table in varchar2, rows 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 handle; -- 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: s '') | ''', 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: = cursor; dbms_ SQL .parse (l_cursor, l_query, rows); Evaluate (l_cursor, 1, l_value_list, 32767); ignore: = equals (l_cursor); -- if not exist (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 | ');'; values (cmn_file_handle, handle); values (cmn_file_handle, handle); else exit; end if; end loop; if not values (cmn_file_handle) Then utl_file.fclose (cmn_file_handle); end if; end;/