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 ?
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!!
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;
/
