ORA-00933: SQL command not properly ended when running create sequence commands

Question

I'm trying to execute some sql using the following C# code:

IDbCommand objOracleCommand = CreateCommand(commandPart, connection, transaction);
objOracleCommand.ExecuteNonQuery();

I'm getting back a "ORA-00933: SQL command not properly ended" error.

commandPart is a string that looks like:

CREATE SEQUENCE seq_1
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCACHE
NOCYCLE
NOORDER

CREATE SEQUENCE seq_2 START WITH 1 INCREMENT BY 1

The commandPart is actually being read in from a text file. The original SQL has semi-colons like so:

CREATE SEQUENCE seq_1
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCACHE
NOCYCLE
NOORDER;

CREATE SEQUENCE seq_2 START WITH 1 INCREMENT BY 1;

This original snippet (with semi-colons) will run just fine in SQL Developer. If I run the original snippet via C# I get "ora-00911: invalid character". Any ideas?

Thanks, Eric

Try putting a semi-colon after NOORDER. If that doesn't fix it, try separating the two statements and execute them one at a time. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Oct 30, 2012 at 21:02

Codo · Accepted Answer · 2012-10-30 21:02:05Z

2

Your command part consists of two commands. Oracle is confused because it only expects one command.

You'll need to execute (ExecuteNonQuery) the two commands seperately.

answered Oct 30, 2012 at 21:02

Codo

79.7k18 gold badges176 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Over a year ago

Hi, I put a ; at the end I get a ORA-00933: SQL command not properly ended. I put a ; in between the two queries and I get a ora-00911: invalid character error. I'm parsing a 300,000+ line sql file that's creating a blank database with my company's metadata. If I split it at the semi colons, It works until I get to function creation statements that have lots of statements, separated by semicolons. If I keep going down this route it seems I'll essentially wind up writing my own SQL parser. I have a hard time believe that Oracle can't handle multiple sql statements in a sql command. -Eric

Codo Over a year ago

For your purpose, it's better to use SQLplus. It'll correctly split and execute the commands including blocks of PL/SQL, stored procedures etc. If you do the splitting yourself, the rule is to split at semicolons except when you encounter DECLARE or BEGIN; then you'll have to split at the next slash (/) on a separate line. (Oracle has three languages: SQL, PL/SQL and SQLplus. The slash is part of SQL*plus only.)

Collectives™ on Stack Overflow

ORA-00933: SQL command not properly ended when running create sequence commands

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related