CODEWITHSUNDEEP
node.jsoracle-databasecsv
forJ
forJ

Reputation: 4617

Bulk Inserting CSV file to Oracle DB using sqlloader nodeJS

I am trying to load csv file using nodeJS on oracle database.

From documentation here https://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_concepts.htm it seems to be possible to load the entire csv file and pass on to Oracle by implementing the below.

load data
infile 'example.dat'  "str '|\n'"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
 col2 char(7))

where data looks something like

hello,world,|
james,bond,|

However when I implement it using oracledb library on nodeJS, it throws ORA-00900 invalid SQL statement error

The below is how I implement the query.

oracledb.getConnection(
    {
        user: userId,
        password: password,
        connectString: `(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ${host})(PORT = ${port}))(CONNECT_DATA = (SID = ${sid})))`
    },
    function (err, connection) {
        if (err) {
            errorHandler(err);
            return;
        }
        connection.execute(
            query,
            function (err, result) {
                if (err) {
                    errorHandler(err);
                    oracleFunctions.connection.doRelease(connection, errorHandler);
                    return;
                }

                successHandler(result);
                oracleFunctions.connection.doRelease(connection, errorHandler);
            });
    });

my query is as below

LOAD DATA
infile 'path\to\the\file.csv' "str '\n'"
INTO TABLE e1_000090000_ADS_R_PPLaps
FIELDS TERMINATED BY ',' optionally enclosed by '"'

What am I doing wrong?

Upvotes: 0

Views: 1175

Answers (1)

gvenzl
gvenzl

Reputation: 1891

You are mixing the SQL*Loader utility, a separate binary provided by Oracle for data loading, with actual valid SQL syntax. You cannot just pass on SQL*Loader parameters as SQL statements, they are not! If you want to load a file via Node.js you will have to read the file, parse the contents and translate that to actual INSERT INTO statement, which is exactly what SQL*Loader does for you.

Upvotes: 1

Related Questions