How to import data exp into SQL Loader

Homepage → exp/IMP →>

How to import data exp into SQL Loader

Excerpt: Unknown Exporting table data to SQL * loader files Exporting table data to SQL * loader files The following describes how to import data from a specified table to an SQL * loaderl statement using a written script. There are three scripts: Exporttable. SQL Getloadercontrol. SQL Getloaderdata. SQL (can be joined) Use these scripts to save the specified table data in ASCII format as a CSV file (which can be read in MS Excel ). A control file is also generated for SQL * loader to import CSV files. First use a text editor to save the following three scripts into three. SQL files, and then SQL> @ exporttable Below are the three files -- File 1: exporttable. SQL -- Performer: User -- Purpose: export table data to sqlloader files Accept tablename prompt 'table to export :' Set Concat ~ Prompt data file-& tablename ~. CSV Prompt control file-& tablename ~. CTL Spool & tablename ~. SQL Start getloaderdata & tablename Spool &tablename.csv Start & tablename Spool & tablename ~. CTL Start getloadercontrol & tablename Spool off Host del & tablename ~. SQL Rem host RM & tablename ~. SQL Set termout on Bytes -------------------------------------------------------------------------------------------------------------- -- File 2: getloadercontrol. SQL -- Executor: exporttable. SQL -- Purpose: Create sqlloader Control File Set pause off Set Newpage none Set heading off Set Concat ~ Set feedback off Set verify off Set linesize 80 Set trimspool on Set trimout off Set termout off Column ord noprint Select 1 ord, 'Load data' from dual Union Select 2 ord, 'infile &tablename.csv 'from dual Union Select 3 ord, 'append' from dual Union Select 4 ord, 'into table & tablename' from dual Union Select 5 ord, 'fields terminated by' | '''; ''' from dual Union Select 6 ord, '(' from dual Union Select 10 * column_id ord, column_name | ''| Decode (data_type, 'Number', 'decimal external ', 'Varchar2', 'Char ', 'Char ', 'Char ', 'Date', 'date') | ',' From user_tab_columns Where table_name = upper ('& tablename ') And column_id not in (select max (column_id) From user_tab_columns Where table_name = upper ('& tablename ')) Union Select 1000 * column_id ord, column_name | ''| Decode (data_type, 'Number', 'decimal external ', 'Varchar2', 'Char ', 'Char ', 'Char ', 'Date', 'date') | ')' From user_tab_columns Where table_name = upper ('& tablename ') And column_id in (select max (column_id) From user_tab_columns Where table_name = upper ('& tablename ')) Order by ord; Bytes -------------------------------------------------------------------------------------------------------------- -- File 3: getloaderdata. SQL -- Executor: exporttable. SQL -- Purpose: Create script extracting data from table Set pause off Set Newpage none Set heading off Set Concat ~ Set feedback off Set verify off Set linesize 1000 Set trimspool on Set trimout on Set termout off Column ord noprint Select 0 ord, 'select', null, null, 'rtrim ('| column_name | ')' From user_tab_columns Where table_name = upper ('& tablename ') And column_id = 1 Union Select column_id ord, '|', '''; ''', '|', 'rtrim ('| column_name | ')' From user_tab_columns Where table_name = upper ('& tablename ') And column_id> 1 Union Select 1000 ord, '|', '''; ''', null, 'From & tablename order by 1 ;' From dual Order by ord;

[Last Update: 10/15/2001 07:54:04]