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?

Have you tried to run the extended SQL outside the fluent-migrator? It will help you to distinguish if the problem is in fluent-migratior or in pure SQL. — mipe34
– mipe34, Commented Feb 3, 2013 at 12:38
yes of course. it executes successfully in oracle. But I think the problem is that need to perform a single sql command (if it executes from c#),not all at once. but I think that is what some solution that run all at once. — AndreyMaybe
– AndreyMaybe, Commented Feb 3, 2013 at 12:54
Since I suppose you have checked the potential source of the error (techonthenet.com/oracle/errors/ora00911.php). I would suggest to split the SQL into two files. It often help me to pass through fluent migrator. — mipe34
– mipe34, Commented Feb 3, 2013 at 13:00
yes it ok, but in really sql script i have more than 10 commands, so if i split it from files, i will have 10 files, maybe it not good way — AndreyMaybe
– AndreyMaybe, Commented Feb 3, 2013 at 13:12
maybe ideia in that parse him, and run in foreach. but it not stable case. do you think that impossible run the entire sql script ? — AndreyMaybe
– AndreyMaybe, Commented Feb 3, 2013 at 13:16

Daniel Lee · Accepted Answer · 2013-02-03 18:05:33Z

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

AndreyMaybe · Accepted Answer · 2013-02-03 18:31:49Z

1

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;

answered Feb 3, 2013 at 18:31

AndreyMaybe

3091 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

run sql scripts with fluentmigrator

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related