ORA-00933: SQL command not properly ended in Groovy Script

Question

Wrote this script:

static  void schema()
{
    Sql.newInstance(*DB_CONFIG, DB_DRIVER).execute('''
        drop table if exists post;
        drop table if exists author;
        create table author(
            id integer primary key,
            name varchar(500)
            );
        create table post(
            id integer primary key,
            title varchar(500),
            text longvarchar,
            author integer not nul,
            foreign key(author) references author(id)
            );
        ''')
}

and after start, I see this:

"WARNING: Failed to execute: because: ORA-00933: SQL command not properly ended"

I'm using Oracle 11g 2 database and oracle jdbc driver.

`drop table if exists` is not a valid `Oracle` syntax. – Lalit Kumar B Feb 16 '15 at 07:33 — Lalit Kumar B, Feb 16 '15 at 07:33

score 2 · Accepted Answer · answered Feb 16 '15 at 08:20

2

drop table if exists post; drop table if exists author;

It is not a valid Oracle syntax. You could do it in the following way -

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE post';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE author';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

There is another syntax error -

author integer not nul,

Correct it to NOT NULL.

answered Feb 16 '15 at 08:20

Lalit Kumar B

47,486
13
97
124

You're welcome. Please mark it as answered if it has helped you. – Lalit Kumar B Feb 16 '15 at 09:09
Lalit, can you help me again? I write your answer in script, and after runnig see this: `ORA-00928: missing SELECT keyword` – A.E.K Feb 16 '15 at 09:33
The `BEGIN-END` is a PL/SQL block, and the CREATE statements are pure SQL. Make sure you take of the execution of PL/SQL and SQL properly. – Lalit Kumar B Feb 16 '15 at 10:00
I just started to work with databases. Could you tell me more what you mean? – A.E.K Feb 16 '15 at 10:03
Did you fix the other error `NOT NULL`? You had a typo as `NUL`. – Lalit Kumar B Feb 16 '15 at 10:04
I don't know how to call an Oracle PL/SQL block in Groovy script. Can you please search for the same and try. – Lalit Kumar B Feb 16 '15 at 10:50

ORA-00933: SQL command not properly ended in Groovy Script

1 Answers1