Node-oracledb: missing or invalid option creating table

Created on 30 Dec 2016 · 5Comments · Source: oracle/node-oracledb

Hi,

I am working on the integration of node-oracledb into sequelize and I'm having an issue :

I can't execute a create table statement without an error. At each time, I got ORA-00922: missing or invalid option

The sql i try to pass is the following, it's ok with sqlplus and sqldeveloper :

CREATE TABLE users (id NUMBER(*,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, createdAt TIMESTAMP NOT NULL, updatedAt TIMESTAMP NOT NULL,CONSTRAINT PKusersid PRIMARY KEY (id));

The call to execute() returns :

Error: ORA-00922: missing or invalid option

    at Error (native)

I am working with nodejs 6.9, Oracle 12c and there is no configuration problem as I am able run my application (which doesn't create tables).
Oracledb is in version 1.12.2.

question

Source

ggrimbert

Most helpful comment

@ggrimbert You just need to remove the semicolon from the end of the statement.

dmcghan on 30 Dec 2016

👍7 😄1

All 5 comments

@ggrimbert You just need to remove the semicolon from the end of the statement.

dmcghan on 30 Dec 2016

👍7 😄1

This works, thanks !

ggrimbert on 3 Jan 2017

I am facing the similar problem while executing the PL/SQL query via the python2.7 using cx_Oracle module . It works in sql developer . But fails with same error ORA-00922 . Could someone please help me on this

SET SERVEROUTPUT ON SIZE 100000
DECLARE
match_count INTEGER;
v_data_type VARCHAR2(255) :='VARCHAR2';
v_search_string VARCHAR2(4000) :='cc569359-b599-4af6-8fdb-a0ad4ea83f51';
BEGIN
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where data_type = v_data_type and table_name like 'TAPI%') LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;