Monday, July 13, 2009

ORA-00911 when writing a query for SSIS

Coming from the Oracle world, I'm in the habit of putting a semicolon at the end of all of my SQL queries. Not a good idea when you're writing an Oracle query for import/export in SSIS (SQL Server Integration Services).

The problem is that SSIS takes care of that for you, and you'll get an entirely unhelpful "ORA-00911: invalid character" error message.
Just remove that final semicolon, and you should be in OK shape.

If you find yourself needing to do multiple SQL statements in one execute SQL task (for instance), you can use the GO separator instead of semicolons.

Note that GO has to be on its own line:
select 'this is sql statement 1' from dual
go
select 'this is sql statement 2' from dual
go

For the curious, GO is a SQL Server-specific batch keyword that is used to separate different SQL batches. It wouldn't normally be something you'd consider using in an Oracle query, but it works in this case.

5 comments:

  1. 4 years down the track...this is still useful! thank you :) I was trying to setup an Exectue SQL Task, which ran two simple statements. It inserted data into an Oracle table and then returned a Single Row result set - so two queries. The (natural) Commit and Semi Colon in between the two statements seemed to be causing the issue. I am now using two separate Execute SQL Tasks to do the separate queries. Thanks!

    ReplyDelete
  2. 3 years later, and still helpful.
    #theinternet

    ReplyDelete
  3. Short, to the point...and still useful.

    ReplyDelete
  4. Omitting last SemiColon doesent work!

    ReplyDelete

Thanks for leaving a comment!