Oracle Loader or External Table Concept

Oracle Loader (OR) External Table Concept

Requirement:-

A FTP location having one file and we need to upload this files in system or any staging Table.
based on that we can write some validation/program/interface anything.

Restrictions:-
1. Only First File contain the heading or columns name. because we skip first row from first file.

Solution:-
1. Create a DB Directory
2. Give Full Grant to this directory
3. Create a External TABLE with proper Syntax
3. Move that control file and excel files to particular $TOP/ .../Bin  Folder or directories
4. Run the SELECT Command on table and check all data comes or not.

See my video on my you tube Channel "ORACLE  TECHNICAL SHOOTER"


1. Create Directory Syntax:-

CREATE OR REPLACE DIRECTORY XYZ AS '/var/tmp';

2. Grant to Directory Syntax:-

GRANT READ,WRITE ON DIRECTORY XYZ TO PUBLIC;
3. We can check whether our directory is created or not:-

select * from all_objects
where object_type = 'DIRECTORY'
and OBJECT_NAME = 'XYZ'

select * from Dba_Directories

where DIRECTORY_NAME = 'XYZ'

4. Create External Table Syntax:-

CREATE TABLE XXSD_SQL_LOADER_M_TL
(
  EMP_NAME       VARCHAR2(100 BYTE),
  EMP_DEPT         VARCHAR2(100 BYTE),
  EMP_DATE         DATE
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY XYZ
     ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
        SKIP=1
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL )
     LOCATION ('XXSD_SQL_LOADER1.csv')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL

NOMONITORING

5. Move files to FTP Location :- /u01 /STG_APP/apps/apps_st/appl/xxsd/12.0.0/bin

6. Check in Table whether data comes or not:-

Friend My Youtube channel                   @              Oracle Technical Shooter