CODEWITHSUNDEEP
sqloracle-databasesql-loader
Ganesh Gani
Ganesh Gani

Reputation: 41

Loading data from CSV file to oracle table using sql loader

i want to load the data from CSV file to oracle table with this below condition.

if the table is empty then only the data should be loaded into table otherwise data should not be loaded.

is this requirement possible with sql loader ? or do i need to use any other ETL tool like informatica ?

Upvotes: 1

Views: 1162

Answers (2)

Popeye
Popeye

Reputation: 35930

You can use the loading option: INSERT for achieving it.

It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.

You can refer: Loading Data into Empty Tables

Cheers!!

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

Regardless of which OS environment you're working on, You may always use ORACLE_LOADER access driver using External tables.

From the Oracle docs Choosing External Tables Versus SQL*Loader

The record parsing of external tables and SQL*Loader is very similar, so normally there is not a major performance difference for the same record format.

All you need to have is a read/write on a directory object. It's good if you can create one(CREATE DIRECTORY YOUR_DIR AS '<directory_path>') or else ask other superusers to give appropriate grants(GRANT READ ON DIRECTORY YOUR_DIR TO yourschema).

The External Table can be created dynamically using EXECUTE IMMEDIATE

DECLARE
     l_exists INT;
BEGIN
SELECT CASE
     WHEN EXISTS ( SELECT 1
                     FROM yourmaintable
                   WHERE ROWNUM = 1
                 ) THEN 1
     ELSE 0 END
into l_exists from dual;

  IF  l_exists : = 0 THEN --If the Table is empty


EXECUTE IMMEDIATE q'{ CREATE TABLE your_ext_table   -- Using the alternate 
                                                    -- quoting mechanism
   (col1         INT,
    col2         VARCHAR2(20)
    col3         DATE)
 ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER    -- Use the ORACLE_LOADER driver to load your file.
    DEFAULT DIRECTORY YOUR_DIR
    ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE
       FIELDS (col1      CHAR(2),
               col2      VARCHAR2(20),
               col3      VARCHAR2(20) date_format DATE mask "mm/dd/yyyy"
              )
      )
    LOCATION ('yourfile.csv')
   )}'
     ;

EXECUTE IMMEDIATE 'INSERT INTO yourmaintable (col1,col2,col3)  --dynamic Insert
                   select * FROM your_ext_table';
END IF;



END;
/

Upvotes: 1

Related Questions