NodeJs + OracleDB + load sql file

Question

The problem is simple, I cant load an sql file using the oracledb connector. It seems it doesn´t supoport more than one sentence.

Any idea how to load an sql file?

var oracledb = require('oracledb');
var fs = require('fs');

fs.readFile("test.sql", function(err, data) {
    if (err) {
        throw err;
    }

    connect(data.toString());
});

function connect(sql) {
    oracledb.getConnection({
            user: "****",
            password: "***",
            connectString: "****"
        },
        function(err, connection) {
            if (err) {
                console.error(err.message);
                return;
            }
            connection.execute(
                sql, [],
                function(err, result) {
                    if (err) {
                        console.error(err.message);
                        doRelease(connection);
                        return;
                    }
                    console.log(result.metaData);
                    console.log(result.rows);
                    doRelease(connection);
                });
        });
}

function doRelease(connection) {
    connection.release(
        function(err) {
            if (err) {
                console.error(err.message);
            }
        });
}

It throws an error:

ORA-00911: invalid character

The sql is here:

select * from DEFECTO;
select * from ESQUEMA;

todotresde · Accepted Answer · 2015-07-02 20:24:36Z

1

Well, I found a workaround for multiple sql statements, and is just using exec:

exec('echo exit | sqlplus -S ****/***@//****:1521/**** @sqlfile.sql', ["bash"],
                function(error, stdout, stderr) {
                    callback();
                }
            );

answered Jul 2, 2015 at 20:24

todotresde

1,8002 gold badges20 silver badges25 bronze badges

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

Comments

Adam Fendley · Accepted Answer · 2016-05-18 17:41:20Z

1

Battled with this for a long time before realizing it's the semicolon that the connection.execute() method doesn't like. So long as your SQL statement doesn't conclude with one, reading from a file works.

answered May 18, 2016 at 17:41

Adam Fendley

665 bronze badges

Comments

Christopher Jones · Accepted Answer · 2015-07-23 12:30:21Z

0

As you discovered, node-oracledb's connection.execute() method only executes one statement. This is the same as other languages. Instead of putting your SQL statements in a SQL*Plus file, try putting them in a .js file as an array of strings. Then process each element of the array. This will give you better control over transactions and error handling.

answered Jul 23, 2015 at 12:30

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

2 Comments

todotresde Over a year ago

Well ... imagine a database dump .... it will not be something practical to convert it to a js file

Christopher Jones Over a year ago

For Oracle->Oracle dumps, use a better format than SQL .e.g export with Data Pump. For 3rd party imports into Oracle, the structure of any data might be consistent so you can parse it using JS and use node-oracledb to insert the data. Bottom line: use what works.

Collectives™ on Stack Overflow

NodeJs + OracleDB + load sql file

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related