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.

Answer 1

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.