Skip to content

SQL-Loader – External Tables

August 15, 2012

Load data into Table from external files , using two methods:

 1)  SQL Loader

2)  External tables

 (I)                 SQL Loader.

 Source Table Name : SYS.DBA_USERS

Target Table Name : SYS.MY_DBA_USERS

 

1)    We need to have the Data file with comma separated fields. I will unload data from DBA_USERS table.

 

cat csv.sql

 

REM Comma separated data
REM
REM Run as below
REM SQL> @CSV.sql <Owner> <Tablename>
REM SQL> @CSV.sql TEST EMP
REM Created By : Malesh Gummula
 
set echo off
set verify off
set pages 0
set feedback off
set trimspool on
set line 200
spool run_sql.sql
prompt set echo off
prompt set pages 0
prompt set feedback off
prompt set trimspool on
prompt set line 32767
prompt spool oradata.txt
 
 
SELECT CASE
WHEN rownum = (SELECT COUNT(1) + 1
FROM dba_tab_columns
WHERE owner = upper(‘&&1’)
AND table_name = upper(‘&&2’)) THEN
substr(output,
1,
instr(output,
‘||’,
1) – 1)
ELSE
output
END AS query
FROM (SELECT rownum,
a.*
FROM (SELECT ‘Select ‘ AS output
FROM dual
UNION ALL
SELECT column_name || ‘||”,”||’
FROM (SELECT column_name
FROM dba_tab_columns
WHERE owner = upper(‘&1’)
AND table_name = upper(‘&2’)
ORDER BY column_id)
UNION ALL
SELECT ‘from &1..&2; ‘
FROM dual) a) b;
prompt spool off
 
spool off
 
REM Now run the query generated
 
@run_sql.sql
REM #####################End of Creating DAT File ##############

 2)    Run the Above SQL to Generate data file named “oradata.txt”

 SQL> @csv.sql SYS DBA_USERS
 
maldevsrv02:/opt/oracle: cat oradata.txt
SYS,0,,OPEN,,02-SEP-12,SYSTEM,TEMP,06-MAR-12,DEFAULT,SYS_GROUP,,10G 11G ,N,PASSWORD
SYSTEM,5,,OPEN,,24-SEP-12,SYSTEM,TEMP,06-MAR-12,DEFAULT,SYS_GROUP,,10G 11G ,N,PASSWORD
E517137,84,,OPEN,,16-SEP-12,USERS,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
TEST,85,,OPEN,,05-DEC-12,USERS,TEMP,20-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
E465800,86,,OPEN,,20-JAN-13,USERS,TEMP,24-JUL-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
OUTLN,9,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSTEM,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
MGMT_VIEW,74,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSTEM,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
FLOWS_FILES,75,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
MDSYS,57,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
WMSYS,32,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
APPQOSSYS,31,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
APEX_030200,78,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
OWBSYS_AUDIT,83,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
DBSNMP,30,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
OWBSYS,79,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
ORDDATA,54,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
ANONYMOUS,46,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,,N,PASSWORD
EXFSYS,42,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
XDB,45,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
ORDSYS,53,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
CTXSYS,43,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
ORDPLUGINS,55,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
SYSMAN,72,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
OLAPSYS,61,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
SI_INFORMTN_SCHEMA,56,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,SYSAUX,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
XS$NULL,2147483638,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,USERS,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
MDDATA,65,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,USERS,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
ORACLE_OCM,21,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,USERS,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
DIP,14,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,USERS,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
APEX_PUBLIC_USER,76,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,USERS,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
SPATIAL_CSW_ADMIN_USR,70,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,USERS,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD
SPATIAL_WFS_ADMIN_USR,67,,EXPIRED & LOCKED,06-MAR-12,06-MAR-12,USERS,TEMP,06-MAR-12,DEFAULT,DEFAULT_CONSUMER_GROUP,,10G 11G ,N,PASSWORD

 

3)    Create SQLLDR control file Dynamically.  To insert data in .MY_ E.g.  SYS.my_DBA_USERS

 

Note : You can run the below script without any parameters in same session of step 1       

cat cntl.sql

 REM #####################Generating SQLLDR control file ##############
 set verify off
set pages 0
set feedback off
set trimspool on
set line 200
set pages 0
set recsep off
 
prompt “Generating SQL Loader File ….”
spool loader.ctl
 
SELECT CASE
               WHEN rownum = (SELECT COUNT(1) + 1
                                          FROM dba_tab_columns
                                       WHERE owner = upper(‘&&1’)
                                           AND table_name = upper(‘&&2’)) THEN
                  substr(output,
                           1,
                           instr(output,
                                     ‘,’  ,
                                     1) – 1)
               ELSE
                  output
         END query
  FROM (SELECT ‘LOAD Data’ || chr(10) || chr(9) || ‘infile ”oradata.txt”’ || chr(10) || chr(9) ||
                     ‘into table &&1..my_&&2’ || chr(10) || chr(9) || ‘fields terminated by “,” optionally enclosed by ””” ‘ ||
                     chr(10) || chr(9) || ‘(‘ AS output
              FROM dual
            UNION ALL
            SELECT chr(9) || column_name || ‘,’
              FROM (SELECT column_name
                          FROM dba_tab_columns
                         WHERE owner = upper(‘&1’)
                           AND table_name = upper(‘&2’)
                         ORDER BY column_id)) b
UNION ALL
SELECT chr(9) || ‘)’
  FROM dual;
 
spool off
prompt ” End of Generating SQL Loader File ….”
REM #####################End of Generating SQLLDR control file ##############
 

4)    Run above script to create control file

SQL> @cntl.sql SYS DBA_USERS
 
“Generating SQL Loader File ….”
LOAD Data
        infile ‘oradata.txt’
        into table SYS.my_DBA_USERS
        fields terminated by “,” optionally enclosed by ‘”‘
        (
        USERNAME,
        USER_ID,
        PASSWORD,
        ACCOUNT_STATUS,
        LOCK_DATE,
        EXPIRY_DATE,
        DEFAULT_TABLESPACE,
        TEMPORARY_TABLESPACE,
        CREATED,
        PROFILE,
        INITIAL_RSRC_CONSUMER_GROUP,
        EXTERNAL_NAME,
        PASSWORD_VERSIONS,
        EDITIONS_ENABLED,
        AUTHENTICATION_TYPE
        )
” End of Generating SQL Loader File ….”

 

5)    Now we have data file and control file for SQLLDR input , so we can run the sql loader now.

Note : this will create table where data needs to be loaded in same schema of source table, before creating Target table it will drop Target table.

Source Table Name : SYS.DBA_USERS

Target Table Name : SYS.MY_DBA_USERS

  cat load.sql

set echo on
set line 200
set pages 200
set verify on
set feedback on
 
drop table &&1..my_&&2;
create table &&1..my_&&2 as select * from &&1..&&2 where 1=2;
 
set echo off
 
prompt ” Executing SQL Loader to load Data ….”
 
REM for Unix => host sqlldr \’/ AS SYSDBA\’ control=loader.ctl
 
host sqlldr \’/ AS SYSDBA\’ control=loader.ctl
 
REM host sqlldr ‘/ AS SYSDBA’ control=loader.ctl
 
prompt ” End of Executing SQL Loader to load Data ….”
 
select * from &&1..my_&&2;
 

6)    Now run the script load.sql to load data from sqlplus

 SQL> @load.sql SYS DBA_USERS
 Output:
SQL> set line 200
SQL> set pages 200
SQL> set verify on
SQL> set feedback on
SQL>
SQL> drop table &&1..my_&&2;
old   1: drop table &&1..my_&&2
new   1: drop table SYS.my_DBA_USERS
 
Table dropped.
 
SQL> create table &&1..my_&&2 as select * from &&1..&&2 where 1=2;
old   1: create table &&1..my_&&2 as select * from &&1..&&2 where 1=2
new   1: create table SYS.my_DBA_USERS as select * from SYS.DBA_USERS where 1=2
 
Table created.
 
SQL>
SQL> set echo off
” Executing SQL Loader to load Data ….”
 
SQL*Loader: Release 11.2.0.3.0 – Production on Tue Aug 14 07:45:08 2012
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Commit point reached – logical record count 32
 
” End of Executing SQL Loader to load Data ….”
 
old   1: select * from &&1..my_&&2
new   1: select * from SYS.my_DBA_USERS

 

(II)              External Tables

 

1)    We will load the data in table TEST.EMP

 
CREATE TABLE test.emp
      (
            eno NUMBER(4), ename varchar2(20), dno NUMBER(4)
      );
 
SQL> desc TEST.emp
 Name                                      Null?    Type
 —————————————– ——– —————————-
 ENO                                       NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(20)
 DNO                                                NUMBER(4)
 
SQL> select * from test.emp;
 
no rows selected
 

2)    Create Directory Object in database and copy the data file to the directory location.

 

SQL> CREATE  OR REPLACE DIRECTORY ext_tab_dir AS  ‘/u02/exports’;
 
Directory created.
 
cp oradata.txt /u02/exports
 
 
maldevsrv02:/opt/oracle: ls -ltr /u02/exports/oradata.txt
-rw-r–r–   1 oracle   dba           16 Aug 14 09:41 /u02/exports/oradata.txt
 
maldevsrv02:/opt/oracle: cat /u02/exports/oradata.txt
1,sam,1
2,tom,2

 3)    Create External table now

 

SQL> drop table emp_ext;
 
Table dropped.
 
SQL> !ls -ltr /u02/exports
total 2519840
-rw-r–r–   1 oracle   dba           16 Aug 14 09:41 oradata.txt
 
SQL> CREATE TABLE emp_ext
      (
            eno NUMBER(4), ename varchar2(20), dno NUMBER(4)
      )
            ORGANIZATION EXTERNAL
            (
                  TYPE ORACLE_LOADER
                  DEFAULT DIRECTORY ext_tab_dir
                  ACCESS PARAMETERS
                  (
                        RECORDS DELIMITED BY NEWLINE
                        BADFILE ‘bad_%a_%p.bad’
                        LOGFILE ‘log_%a_%p.log’
                        FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘  LRTRIM 
                        MISSING FIELD VALUES ARE NULL 
                        REJECT ROWS WITH ALL NULL FIELDS
                        (
                              eno INTEGER EXTERNAL(4),
                              ename CHAR(20),
                              dno INTEGER EXTERNAL(4)
                        )
                  )
            LOCATION (‘oradata.txt’)
)
PARALLEL 5
REJECT LIMIT 0; 
 
Table created.
 

Note : Do not provide the full path for data file ‘oradata.txt’, as path will be picked from “DEFAULT DIRECTORY ext_tab_dir” clause, ie directory which we created in step 2.

 

SQL> select * from emp_ext;
 
       ENO ENAME                       DNO
———- ——————– ———-
         1 sam                           1
         2 tom                           2
  

4)    Check Log files created in Directory location

 
SQL> !ls -ltr /u02/exports
total 2519872
-rw-r–r–   1 oracle   dba           16 Aug 14 09:41 oradata.txt
-rw-r–r–   1 oracle   dba          676 Aug 14 09:48 log_000_17496.log
-rw-r–r–   1 oracle   dba          676 Aug 14 09:48 log_000_20044.log
 
maldevsrv02:/u02/exports: cat log_000_17496.log
 
 LOG file opened at 08/14/12 09:48:24
 
Field Definitions for table EMP_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields
 
  Fields in Data Source:
 
    ENO                             Integer external (4)
      Terminated by “,”
      Enclosed by “”” and “””
      Trim whitespace from left and right
    ENAME                           CHAR (20)
      Terminated by “,”
      Enclosed by “”” and “””
      Trim whitespace from left and right
    DNO                             Integer external (4)
      Terminated by “,”
      Enclosed by “”” and “””
      Trim whitespace from left and right
 
maldevsrv02:/u02/exports: cat log_000_20044.log
 
 LOG file opened at 08/14/12 09:48:25
 
Field Definitions for table EMP_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields
 
  Fields in Data Source:
 
    ENO                             Integer external (4)
      Terminated by “,”
      Enclosed by “”” and “””
      Trim whitespace from left and right
    ENAME                           CHAR (20)
      Terminated by “,”
      Enclosed by “”” and “””
      Trim whitespace from left and right
    DNO                             Integer external (4)
      Terminated by “,”
      Enclosed by “”” and “””
      Trim whitespace from left and right
 

5)    Now you can transfer the data from external table to regular table

 

SQL> drop table test.emp;
 
Table dropped.
 
SQL> create table test.emp as select * from emp_ext;
 
Table created.
 
SQL> select * from test.emp;
 
       ENO ENAME                       DNO
———- ——————– ———-
         1 sam                           1
         2 tom                           2
 

6)    END

You can Download the same in PDF format , click below link

 SQL Loader

Advertisements

From → Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: