Error ora 00900 invalid sql statement

1. What is your Node.js version: use console.log(process.version)? Is it 64-bit or 32-bit: use console.log(process.arch)?

node:10.15.1

1. What is your node-oracledb version: use console.log(oracledb.versionString)?

^3.1.0

1. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?

2. What error(s) you are seeing?

Unhandled rejection Error: ORA-00900: invalid SQL statement

1. What OS (and version) is Node.js executing on: use console.log(process.platform)?

debian 9

1. What is your Oracle client (e.g. Instant Client) version: use console.log(oracledb.oracleClientVersionString)? Is it 64-bit or 32-bit? How was it installed? Where is it installed?

instantclient-basic-linux.x64-18.3.0.0.0

1. What is your Oracle Database version: use console.log(connection.oracleServerVersionString)?

12.1.0.2.0

1. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to? On macOS, what is in ~/lib?

2. What Oracle environment variables did you set? How exactly did you set them?

3. Do you have a small, single Node.js script that immediately runs to show us the problem?

Doesn’t work

  const db = knex({
    client: 'oracledb',
    debug: true,
    connection: {
      user,
      password,
      connectString: "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + host + ")(PORT=" + port + "))(CONNECT_DATA=(SID = " + sid + ")))"
    },
    pool: { 
      afterCreate: (conn:any, done:Function) => { 
        conn.execute('execute VECTORS.version_util.set_current_version(:tableName)',{
          tableName: 'VECTORS.Test'
        },(err:Error) => {
          done(err,conn);
        });
      } 
    },
    // @ts-ignore
    fetchAsString: ['clob']
  });

  db('STATES').first()
  .then((data:any) => (console.log(data)));

Works

  const db = knex({
    client: 'oracledb',
    debug: true,
    connection: {
      user,
      password,
      connectString: "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + host + ")(PORT=" + port + "))(CONNECT_DATA=(SID = " + sid + ")))"
    },
    pool: { 
      afterCreate: (conn:any, done:Function) => { 
        conn.execute('SELECT * FROM COLUMN_REGISTRY WHERE ROWNUM = 1',(err:Error) => {
          done(err,conn);
        });
      } 
    },
    // @ts-ignore
    fetchAsString: ['clob']
  });

  db('STATES').first()
  .then((data:any) => (console.log(data)));

Also Works

From within Oracle SQL developer

execute vectors.version_util.set_current_version('VECTORS.Test');
SELECT * FROM STATES WHERE rownum = 1;

This is the versioning system I am using:

http://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/read-oracle-versioned-data-using-sql.htm

================================
General
================================
A. When calling to a remote PL/SQL procedure from sqlplus — all works fine,
But when calling to the same remote PL/SQL procedure from PL/SQL code, an ORA-00900: invalid SQL statement error is thrown.


B. When calling a PL/SQL directly, all works fine, but when calling same procedure vi db link, it fails with ORA-02064 Distributed operation not supported


Why is that?


================================
ORA-00900 Example
================================

In this example, a DB_LINK named KUKU is created to the remote database.

CREATE DATABASE LINK KUKU CONNECT TO REMOTE_USER IDENTIFIED BY REMOTE_PASSWORD USING ‘REMOTE_HOST_NAME’;

In the remote database, a getDate Procedure in Package PKG_TEST should be activated.


In sqlplus
From sqlplus, any of these versions work:
EXEC PKG_TEST.getDate@KUKU;
EXEC REMOTE_USER.PKG_TEST.getDate@KUKU;


When creating a Synonym, it can be also used, to simplify code.


CREATE SYNONYM REMOTE_PKG FOR REMOTE_USER.PKG_TEST@KUKU


SELECT * FROM USER_SYNONYMS WHERE synonym_name = ‘KUKU’


SYNONYM_NAME        TABLE_OWNER      TABLE_NAME      DB_LINK
——————- —————- ————— —————
REMOTE_PKG          REMOTE_USER      PKG_TEST        KUKU

So in sqlplus, it would be:
EXEC REMOTE_PKG.getDate;


In PL/SQL
When activating the same code in PL/SQL it MUST be inside a BEGIN END block.
Any other syntax, such as EXECUTE IMMEDIATE <sql_string> would fail with «ORA-00900: invalid SQL statement».


This is the correct code:


BEGIN
  PKG_TEST.getDate;
  EXCEPTION
    WHEN OTHERS THEN
      WRITE_LOG(v_module_name,’Error Running: ‘||v_sql_str||’ ‘||SQLERRM);
      RAISE;
END;

Per Oracle Metalink CALLING REMOTE PACKAGE RECEIVES ORA-2064 (Doc ID 1026597.6):

So, a work around this problem would be to change the remote Procedure to be Autonomous Transaction.