Multiple File upload Thur Oracle Loader Or External Table Concept

Multiple File upload thru Oracle Loader (OR) External Table Concept

Requirement:-

A FTP location having multiple file and we need to upload all 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. create another table same or more than external table columns based on requirements :-

create table  XXSD_SQL_LOADER_M2_TL 
(
  EMP_NAME       VARCHAR2(100 BYTE),
  EMP_DEPT       VARCHAR2(100 BYTE),
  EMP_DATE       DATE

)

6. Create one procedure

CREATE OR REPLACE PROCEDURE XXSD_SQL_LOADER_M2_PROC AS

BEGIN

  

  FOR i IN 1 .. 10 LOOP

    EXECUTE IMMEDIATE 'ALTER TABLE XXSD_SQL_LOADER_M_TL LOCATION (''XXSD_SQL_LOADER'||i||'.csv'')';

INSERT INTO XXSD_SQL_LOADER_M2_TL     

SELECT * FROM XXSD_SQL_LOADER_M_TL;

    commit;

  END LOOP;

  exception when others then null;

END;

7. Execute that procedure:-

begin

XXSD_SQL_LOADER_M2_PROC;

END;

8. 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