ORA-00911: invalid character - When running SQL dump

user_445820 0
Viewed 572

I have a bunch of SQL which I need to execute, but Oracle is complaining with this error:

Error Message : ORA-00911: invalid character
Position      : 27
Statement     : (dump below)

I'm not sure what's causing this. I have tried removing the semicolon at the end as some answers suggested, but that's not doing the trick. I'm attempting to run this SQL dump on Laravel 5.2 with the DB::unprepared() method, like so:

DB::connection('oracle')->unprepared(File::get(base_path('/path/to/oracle_dump.sql')));

This is the SQL I'm trying to run:

DROP TABLE "MYUSERNAME"."POSTS";
DROP SEQUENCE "MYUSERNAME"."POST_SEQ";
CREATE TABLE POSTS
(
  ID NUMBER(*, 0) NOT NULL, TITLE VARCHAR2(1000 BYTE), CONSTRAINT SYS_C004109 PRIMARY KEY(ID)
  USING INDEX
  (
      CREATE UNIQUE INDEX SYS_C004109 ON POSTS (ID ASC)
      LOGGING
      TABLESPACE TEST_TABLSPACE
      PCTFREE 10
      INITRANS 2
      STORAGE
      (
        INITIAL 65536
        NEXT 1048576
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        BUFFER_POOL DEFAULT
      )
      NOPARALLEL
  )
  ENABLE
)
LOGGING
TABLESPACE TEST_TABLSPACE
PCTFREE 10
INITRANS 1
STORAGE
(
  INITIAL 65536
  NEXT 1048576
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE SEQUENCE POST_SEQ INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20
CREATE TRIGGER POST_BIR
BEFORE INSERT ON posts
FOR EACH ROW

BEGIN
SELECT post_seq.NEXTVAL
INTO :new.id
FROM dual;
END;

I have used SQLDeveloper on Mac to get this dump.

oracle sql
2 Answers

You should use ";" after each statement in the file "oracle_dump.sql".

DROP TABLE "MYUSERNAME"."POSTS";
DROP SEQUENCE "MYUSERNAME"."POST_SEQ";
CREATE TABLE POSTS
(
  ID NUMBER(*, 0) NOT NULL, TITLE VARCHAR2(1000 BYTE), CONSTRAINT SYS_C004109 PRIMARY KEY(ID)
  USING INDEX
  (
      CREATE UNIQUE INDEX SYS_C004109 ON POSTS (ID ASC)
      LOGGING
      TABLESPACE TEST_TABLSPACE
      PCTFREE 10
      INITRANS 2
      STORAGE
      (
        INITIAL 65536
        NEXT 1048576
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        BUFFER_POOL DEFAULT
      )
      NOPARALLEL
  )
  ENABLE
)
LOGGING
TABLESPACE TEST_TABLSPACE
PCTFREE 10
INITRANS 1
STORAGE
(
  INITIAL 65536
  NEXT 1048576
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE SEQUENCE POST_SEQ INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE TRIGGER POST_BIR
BEFORE INSERT ON posts
FOR EACH ROW

BEGIN
SELECT post_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
user_88848160

Try executing each statement in a separate file, and do not close the statement with ";" once you have each in its file

You have 5 statement in the file "oracle_dump.sql". Have a separate file for each if you want to run them using a command like:

DB::connection('oracle')->unprepared(File::get(base_path('/path/to/<<file>>')));
user_85614200
Related