ORACLE FILE TO DB

Oracle does not supply any data unload utilities. Here are some workarounds:

Using SQL*Plus

You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (comma separated values) file that can be imported into MS-Excel:

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
  from tab1
 where col2 = 'XYZ';
spool off

You can also use the "set colsep" command if you don't want to put the commas in by hand. This saves a lot of typing. Example:

set colsep ','
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
  from tab1
 where col2 = 'XYZ';
spool off

Using PL/SQL

PL/SQL's UTL_FILE package can also be used to unload data. Example:

declare
  fp utl_file.file_type;
begin
  fp := utl_file.fopen('c:\oradata','tab1.txt','w');
  utl_file.putf(fp, '%s, %sn', 'TextField', 55);
  utl_file.fclose(fp);
end;