Can't excute SQL instructions from file to Oracle Express


I have installed Oracle Express and successfully managed to comunicate it with a Xojo desktop app.

I can create tables and all with simple SQL commands written in the body of the app, like this:

Dim master As New OracleDatabase

master.DatabaseName = “localhost:1521/XE”
master.UserName = UserName
master.Password = Password
master.Debug = 1

If master.Connect Then
master.SQLExecute(“CREATE TABLE tbl_Ajuste (ID INTEGER NOT NULL, Data Date,”_
+" Fornecedor INTEGER, Estoque INTEGER, Razao VARCHAR2(1000), contabilizado CHAR CHECK(contabilizado in (0,1)), “_
+” usuario VARCHAR2(100), CONSTRAINT tbl_Ajuste_PK PRIMARY KEY (ID) ENABLE)")

End If[/code]

When I try to read SQL commands from a TextInputStream and execute them, I get errors.

This is the code I’m using to read and process the SQL instructions stored in a file:

Dim sqlfile As FolderItem
Dim s_linha As String

#if DebugBuild then
	sqlfile = GetFolderItem("").Parent.Child("Temp\\dados_geo_oracle.sql")
	sqlfile = GetFolderItem("").Child("Temp\\dados_geo_oracle.sql")

if sqlfile.Exists then
	tis = TextInputStream.Open(sqlfile)
	tis.Encoding = Encodings.UTF8
	While Not tis.EOF
		s_linha = tis.ReadLine(Encodings.UTF8)
end if

When the very first SQL line is processed, Oracle returns:

ErrorCode = 2000
ErrorMessage = ORA-02185 - a token other than WORK follows COMMIT

After all the other lines the messages are:

ErrorCode = 2000
ErrorMessage = ORA-00911 - invalid character

I was careful enough to remove all portuguese accentuation marks from my test. The SQL file looks like this:

INSERT INTO tbl_Paises (ID, Nome, Sigla) SELECT Seq_Paises.NextVal, 'Brasil', 'BR' FROM dual WHERE NOT EXISTS (SELECT Nome FROM tbl_Paises WHERE Nome = 'Brasil');
INSERT INTO tbl_Estados (ID, Nome, Sigla) SELECT Seq_Estados.NextVal, 'Acre','AC' FROM dual WHERE NOT EXISTS (SELECT Nome FROM tbl_Estados WHERE Nome = 'Acre');
INSERT INTO tbl_Estados (ID, Nome, Sigla) SELECT Seq_Estados.NextVal, 'Alagoas','AL' FROM dual WHERE NOT EXISTS (SELECT Nome FROM tbl_Estados WHERE Nome = 'Alagoas');

Any ideas what is going wrong?

I could get similar code to work with SQLite, Postgres, MySQL and SQL Server. Oracle is proving to be tougher than the others.

All help is appreciated.

I had sql statements to Oracle 12c failing when I used semicolons at the end of it. After removing semicolons, the statement worked as expected.

As I am fiddling with Oracle 12c myself right now, I looked into this again.

See the discussion about when to use ; and / here:

And someone posted this explanation:

I found this hint:

If one wishes to execute multiple sql statements (Oracle) from a single execute statement, then one needs to wrap the SQL statements in a PL/SQL begin … end block: