5

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?

FelipeAls
  • 21,711
  • 8
  • 54
  • 74
AndreyMaybe
  • 309
  • 1
  • 2
  • 8
  • 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 Feb 03 '13 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 Feb 03 '13 at 12:54
  • Since I suppose you have checked the potential source of the error (http://www.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 Feb 03 '13 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 Feb 03 '13 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 Feb 03 '13 at 13:16
  • I'm still suspecting that there is some COPY/PASTE character converting error. But.. ;-) . I have in my migration also one big script creating the whole initial DB and it worked well. So it should work. My suggestion was more like a workaround (sometimes it helps me to keep hair on my head). However, is there any reason why don't you use fluent migrator API to create your tables? – mipe34 Feb 03 '13 at 13:27
  • Can you include the whole stack trace? – Daniel Lee Feb 03 '13 at 15:57
  • maybe your db is ms sql.. ;-) but in case i use oracle ) so, yes, it good idea, but i have some script for include default files and ect. so if i will write use fluent migrator API, it would be very cumbersome. – AndreyMaybe Feb 03 '13 at 16:01
  • Looks like the same problem as yours: https://groups.google.com/d/topic/fluentmigrator-google-group/0hagCHrarzk/discussion It's saying that you should use BEGIN and END; around your blocks. – Daniel Lee Feb 03 '13 at 16:05
  • ok, i copy stack trace :http://bit.ly/X6GZc5 – AndreyMaybe Feb 03 '13 at 16:07
  • Daniel Lee thanks, it's work for "Insert" with BEGIN and END, but i can't use it with "CREATE" "DROP" or "ALETER", for example http://bit.ly/X6Ijf6 doesn't work. – AndreyMaybe Feb 03 '13 at 16:14
  • Am I missing something? How can you create the same table twice (TESTTABLE)? That wouldn't work on any other database. Does it work on Oracle? – Daniel Lee Feb 03 '13 at 16:28
  • See here as well: http://stackoverflow.com/questions/685850/run-multiple-commands-in-one-executescalar-in-oracle – Daniel Lee Feb 03 '13 at 16:29
  • Not two begin end's. BEGIN statement1; statement2; END; – Daniel Lee Feb 03 '13 at 16:31
  • i'm sory i put wrong file, this is worked http://bit.ly/X6Ijf6 – AndreyMaybe Feb 03 '13 at 16:35
  • Daniel Lee, so it work's only with this statement (not similar that in http://bit.ly/X6Pu70) http://bit.ly/X6Ijf6, but put create table in string is not good – AndreyMaybe Feb 03 '13 at 16:57
  • thanks you all so it's work only with EXECUTE IMMEDIATE, any other ideas? – AndreyMaybe Feb 03 '13 at 17:06
  • Sorry, it looks like an Oracle/ADO.Net problem. FluentMigrator doesn't do much with scripts except execute them. I suspect that if you tried to execute this with ADO.Net directly you would get the same error. – Daniel Lee Feb 03 '13 at 17:12
  • Yes you are right, I tried .. in any case, thank you. could you please just write the answer to that other developers could see that's :) thanks in advance. – AndreyMaybe Feb 03 '13 at 17:19
  • Looking at the how FluentMigrator deals with this for other databases, it splits up multi-statement scripts but it doesn't do this for Oracle. So if you can't fix this you should log an issue at https://github.com/schambers/fluentmigrator/issues and hopefully someone (not me) who knows something about Oracle can fix it. – Daniel Lee Feb 03 '13 at 17:43
  • Daniel Lee sorry, but could you please just write this sql http://bit.ly/11E1U7A (it works only),because your response contains not working sql. thanks in advance. – AndreyMaybe Feb 03 '13 at 18:03
  • Daniel Lee yes of course with pleasure :) and is ready to help immediately – AndreyMaybe Feb 03 '13 at 18:06
  • Curiosity. Can you tell me why you extends a "AutoReversingMigration" instead of "Migration" in this migration? – Dherik Sep 23 '14 at 16:00

2 Answers2

5

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

Daniel Lee
  • 7,709
  • 2
  • 48
  • 57
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;
AndreyMaybe
  • 309
  • 1
  • 2
  • 8