run sql scripts with fluentmigrator

Question

I have SQL script:

CREATE TABLE TESTTABLE1(
   ID_TESTTABLE1          NUMBER (18) NOT NULL,
   TESTTABLE_VALUE1       NUMBER (18),
   TESTTABLE_KEY1       NUMBER (18))

and use Fluentmigrator:

[Migration(201302021800)]
public class Migration_201302021800 : AutoReversingMigration
{
    public override void Up()
    {
        var url = @"Update_1.0.0.5.sql";
        Execute.Script(url);
    }
}

It executes successfully, and if I add some SQL:

CREATE TABLE TESTTABLE1
(
  ID_TESTTABLE1          NUMBER (18) NOT NULL,
  TESTTABLE_VALUE1       NUMBER (18),
  TESTTABLE_KEY1       NUMBER (18)
);
CREATE TABLE TESTTABLE
(
  ID_TESTTABLE          NUMBER (18) NOT NULL,
  TESTTABLE_VALUE      NUMBER (18),
  TESTTABLE_KEY      NUMBER (18)
);

execute in Fluentmigrator fails, with Oracle exeption {"ORA-00911: invalid character"}.

My database is Oracle db.

What's the problem?

Answer 1

To batch statements together for Oracle you need to have it enclosed in a BEGIN...END block. In your last example that you linked to in the comments you are missing a semicolon right after the second statement and before the END keyword.

BEGIN 
CREATE TABLE TESTTABLE1 
    ( 
      ID_TESTTABLE1          NUMBER (18) NOT NULL, 
      TESTTABLE_VALUE1       NUMBER (18), 
      TESTTABLE_KEY1       NUMBER (18) 
    ); 
CREATE TABLE TESTTABLE 
    ( 
      ID_TESTTABLE          NUMBER (18) NOT NULL, 
      TESTTABLE_VALUE      NUMBER (18), 
      TESTTABLE_KEY      NUMBER (18) 
    );
END;

Although FluentMigrator could provide better support in this case. For example, when FluentMigrator processes multi-statement scripts from Sql Server then it splits up the script and executes each statement (https://github.com/schambers/fluentmigrator/blob/master/src/FluentMigrator.Runner/Processors/SqlServer/SqlServerProcessor.cs#L197-236). So I would recommend logging an issue at https://github.com/schambers/fluentmigrator/issues

Answer 2

Now i use not only BEGIN and END statement, so also EXECUTE IMMEDIATE for CREATE ALTER and DROP. For INSERT i use only BEGIN and END.

BEGIN 
EXECUTE IMMEDIATE'CREATE TABLE TESTTABLE1 
( 
  ID_TESTTABLE1          NUMBER (18) NOT NULL, 
  TESTTABLE_VALUE1       NUMBER (18), 
  TESTTABLE_KEY1       NUMBER (18) 
)'; 
EXECUTE IMMEDIATE 'CREATE TABLE TESTTABLE 
( 
  ID_TESTTABLE          NUMBER (18) NOT NULL, 
  TESTTABLE_VALUE      NUMBER (18), 
  TESTTABLE_KEY      NUMBER (18) 
)';
END;