Bulk Inserting CSV file to Oracle DB using sqlloader nodeJS

Question

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?

gvenzl · Accepted Answer · 2017-11-13 05:44:35Z

1

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.

answered Nov 13, 2017 at 5:44

gvenzl

1,91014 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

forJ Over a year ago

Is it really 'exactly' what SQL loader does? Because I hear that it is much faster than just inserts.

gvenzl Over a year ago

SQL*Loader uses a combination of BULK INSERTS and INSERT APPEND to load data, which are indeed much faster than conventional inserts. Both functionalities are part of the database kernel and generally accessible to the user. However, as to Node.js it seems that the driver does not yet support this: github.com/oracle/node-oracledb/issues/614

Amit Kumar Over a year ago

@gvenzl, do you have any example to use sql loader with oracledb node js.

gvenzl Over a year ago

As said @AmitKumar, Sql*Loader is a binary in itself and not a SQL syntax. It therefore can't be used inside Node.js code other than calling the binary. For examples please have a look at the Sql*Loader documentation.

Collectives™ on Stack Overflow

Bulk Inserting CSV file to Oracle DB using sqlloader nodeJS

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related