ORA-00933: SQL command not properly ended
-
- Posts: 57
- Joined: Tue 11 Mar 2014 07:49
ORA-00933: SQL command not properly ended
Calling ObjectContext.ExecuteStoreCommand() using dotConnect for Oracle (v. 8.3.115) with any statement ending with semicolon (;) causes ORA-00933: SQL command not properly ended. Please fix it ASAP.
This issue may also be related: http://forums.devart.com/viewtopic.php?f=1&t=29232
This issue may also be related: http://forums.devart.com/viewtopic.php?f=1&t=29232
Last edited by Dennis Wanke on Sat 29 Mar 2014 09:35, edited 1 time in total.
Re: Showstopper: ORA-00933: SQL command not properly ended
This is a designed behaviour. ObjectContext.ExecuteStoreCommand() uses the OracleCommand object which is not intended for using several SQL statements in the same command text. If you want to execute several SQL statemenets one by one, please use OracleScript: http://www.devart.com/dotconnect/oracle ... cript.html.
JIC: You can get the ADO.NET connection (OracleConnection) of your EF context in the following way
a) ObjectContext:
b) DbContext:
After this, apply an ADO.NET code with OracleScript which will be executed on the obtained OracleConnection object.
JIC: You can get the ADO.NET connection (OracleConnection) of your EF context in the following way
a) ObjectContext:
Code: Select all
(MyObjectContext.Connection as System.Data.EntityClient.EntityConnection).StoreConnection
Code: Select all
(((IObjectContextAdapter)MyDbContext).ObjectContext.Connection as System.Data.EntityClient.EntityConnection).StoreConnection
-
- Posts: 57
- Joined: Tue 11 Mar 2014 07:49
Re: Showstopper: ORA-00933: SQL command not properly ended
I still see no reason of why a semicolon should cause a single SQL statement to fail. According to Oracle documentation an SQL statement can (and should) end with a semicolon - it is not an error.
Re: Showstopper: ORA-00933: SQL command not properly ended
A semicolon can be employed only in PL/SQL and in scripts used for executing in SQL*Plus.
We did not implement a parser for removing semicolon from the statement before sending it to the server because this would affect performance.
We did not implement a parser for removing semicolon from the statement before sending it to the server because this would affect performance.
-
- Posts: 57
- Joined: Tue 11 Mar 2014 07:49
Re: Showstopper: ORA-00933: SQL command not properly ended
Since ObjectContext.ExecuteStoreCommand() is intended to execute only one single statement, it would suffice just to trim whitespaces and known statement delimiters at the end of the script, e.g. by calling CommandText.TrimEnd(' ', '\t', '\r', '\n', ';', '/'). The performance of such operation is nearly O(1) and is really neglectable comparing to the posterior processing, involving a server round-trip and so on.
Re: ORA-00933: SQL command not properly ended
This functionality will not help to execute a script which consists of several commands.Dennis Wanke wrote:it would suffice just to trim whitespaces and known statement delimiters at the end of the script, e.g. by calling CommandText.TrimEnd(' ', '\t', '\r', '\n', ';', '/')
If you expect a delimiter at the end of your commands, please trim the command text correspondingly before assigning it to OracleCommand.
-
- Posts: 51
- Joined: Mon 09 Apr 2012 09:57
Re: ORA-00933: SQL command not properly ended
I have the following C# code that gives me the exact same ORA-00933 error. I HAVE to have the semi-colon at the end of the statement as it's C# code. This code works just fine in LinqPad going against the same Oracle database. This Linq statement also works fine if I create a list with the same fields (hence, not going against a database), so we're assuming it is our Devart dotConnect for Oracle that's got the issues.
var cityResults = db.TAXRATEs
.Where(x => x.TAXTYPE.Equals("CI"))
.OrderByDescending(x => x.EFFDT)
.GroupBy(x => x.DES)
.Select(x => x.First());
var cityResults = db.TAXRATEs
.Where(x => x.TAXTYPE.Equals("CI"))
.OrderByDescending(x => x.EFFDT)
.GroupBy(x => x.DES)
.Select(x => x.First());
Re: ORA-00933: SQL command not properly ended
Please turn on the dbMonitor tool and specify the exact SQL statement which leads to ORA-00933:
http://www.devart.com/dotconnect/oracle ... nitor.html
http://www.devart.com/dbmonitor/dbmon3.exe
http://www.devart.com/dotconnect/oracle ... nitor.html
http://www.devart.com/dbmonitor/dbmon3.exe
-
- Posts: 51
- Joined: Mon 09 Apr 2012 09:57
Re: ORA-00933: SQL command not properly ended
OK, here is the SQL that the above LINQ statement generates. The part I don't understand is the 2nd parameter at the bottom of this SELECT statement (:p1).
-- -1 row(s) affected.
Code: Select all
SELECT (
SELECT t6.AMT
FROM (
SELECT t7.DES, t7.AMT, t7.EFF_DT
FROM DABC_TAB.TAX_RATE t7
WHERE t7.TAX_TYPE = :p0
) t6
WHERE (t1.DES = t6.DES) AND (ROWNUM <= 1)
) AS AMT
FROM (
SELECT t2.DES
FROM (
SELECT t3.DES
FROM DABC_TAB.TAX_RATE t3
WHERE t3.TAX_TYPE = :p0
ORDER BY t3.EFF_DT DESC
) t2
GROUP BY t2.DES
) t1
WHERE (ROWNUM <= 1) AND (((
SELECT t4.DES
FROM (
SELECT t5.DES, t5.EFF_DT
FROM DABC_TAB.TAX_RATE t5
WHERE t5.TAX_TYPE = :p0
) t4
WHERE (t1.DES = t4.DES) AND (ROWNUM <= 1)
)) = :p1)
Re: ORA-00933: SQL command not properly ended
Please send us a small test project with the corresponding DDL/DML script for reproducing the problem.
-
- Posts: 51
- Joined: Mon 09 Apr 2012 09:57
Re: ORA-00933: SQL command not properly ended
Code: Select all
ALTER TABLE MYTAX.TAX_RATE
DROP PRIMARY KEY CASCADE;
DROP TABLE MYTAX.TAX_RATE CASCADE CONSTRAINTS;
CREATE TABLE MYTAX.TAX_RATE
(
TAX_TYPE VARCHAR2(2 BYTE),
DES VARCHAR2(40 BYTE),
AMT NUMBER(6,4),
EFF_DT DATE,
CREATE_USER VARCHAR2(8 BYTE),
CREATE_DATE DATE
)
TABLESPACE TABLES
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
CREATE UNIQUE INDEX MYTAX.TAX_RATE_PK ON MYTAX.TAX_RATE
(CREATE_DATE, EFF_DT, AMT, DES, TAX_TYPE)
LOGGING
TABLESPACE TABLES
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE MYTAX.TAX_RATE ADD (
CONSTRAINT TAX_RATE_PK
PRIMARY KEY
(CREATE_DATE, EFF_DT, AMT, DES, TAX_TYPE)
USING INDEX MYTAX.TAX_RATE_PK
ENABLE VALIDATE);
So there is the code that creates our tax table. It would have rows likes this:
Code: Select all
TAX_TYPE DES AMT EFF_DT CREATE_USER CREATE_DATE
CI PARK CITY 1.9 4/1/2013 NULL 4/1/2013
CO BOX ELDER 0.25 7/1/1999 NULL 7/1/1999
CI PRICE 0.25 7/1/1999 NULL 7/1/1999
CI GARFIELD 1.25 4/1/2000 NULL 4/1/2000
-
- Posts: 51
- Joined: Mon 09 Apr 2012 09:57
Re: ORA-00933: SQL command not properly ended
Here is the code from the Context:
Code: Select all
/// <summary>
/// There are no comments for DotNetContext.TAXRATE in the schema.
/// </summary>
[Table(Name = @"TAX_RATE")]
public partial class TAXRATE : INotifyPropertyChanging, INotifyPropertyChanged
{
private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(System.String.Empty);
private string _TAXTYPE;
private string _DES;
private double _AMT;
private System.DateTime _EFFDT;
private string _CREATEUSER;
private System.DateTime _CREATEDATE;
#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
partial void OnTAXTYPEChanging(string value);
partial void OnTAXTYPEChanged();
partial void OnDESChanging(string value);
partial void OnDESChanged();
partial void OnAMTChanging(double value);
partial void OnAMTChanged();
partial void OnEFFDTChanging(System.DateTime value);
partial void OnEFFDTChanged();
partial void OnCREATEUSERChanging(string value);
partial void OnCREATEUSERChanged();
partial void OnCREATEDATEChanging(System.DateTime value);
partial void OnCREATEDATEChanged();
#endregion
public TAXRATE()
{
OnCreated();
}
/// <summary>
/// There are no comments for TAXTYPE in the schema.
/// </summary>
[Column(Name = @"TAX_TYPE", Storage = "_TAXTYPE", CanBeNull = false, DbType = "VARCHAR2(2 CHAR) NOT NULL", IsPrimaryKey = true)]
public string TAXTYPE
{
get
{
return this._TAXTYPE;
}
set
{
if (this._TAXTYPE != value)
{
this.OnTAXTYPEChanging(value);
this.SendPropertyChanging();
this._TAXTYPE = value;
this.SendPropertyChanged("TAXTYPE");
this.OnTAXTYPEChanged();
}
}
}
/// <summary>
/// There are no comments for DES in the schema.
/// </summary>
[Column(Storage = "_DES", CanBeNull = false, DbType = "VARCHAR2(40 CHAR) NOT NULL", IsPrimaryKey = true)]
public string DES
{
get
{
return this._DES;
}
set
{
if (this._DES != value)
{
this.OnDESChanging(value);
this.SendPropertyChanging();
this._DES = value;
this.SendPropertyChanged("DES");
this.OnDESChanged();
}
}
}
/// <summary>
/// There are no comments for AMT in the schema.
/// </summary>
[Column(Storage = "_AMT", CanBeNull = false, DbType = "NUMBER(6,4) NOT NULL", IsPrimaryKey = true)]
public double AMT
{
get
{
return this._AMT;
}
set
{
if (this._AMT != value)
{
this.OnAMTChanging(value);
this.SendPropertyChanging();
this._AMT = value;
this.SendPropertyChanged("AMT");
this.OnAMTChanged();
}
}
}
/// <summary>
/// There are no comments for EFFDT in the schema.
/// </summary>
[Column(Name = @"EFF_DT", Storage = "_EFFDT", CanBeNull = false, DbType = "DATE NOT NULL", IsPrimaryKey = true)]
public System.DateTime EFFDT
{
get
{
return this._EFFDT;
}
set
{
if (this._EFFDT != value)
{
this.OnEFFDTChanging(value);
this.SendPropertyChanging();
this._EFFDT = value;
this.SendPropertyChanged("EFFDT");
this.OnEFFDTChanged();
}
}
}
/// <summary>
/// There are no comments for CREATEUSER in the schema.
/// </summary>
[Column(Name = @"CREATE_USER", Storage = "_CREATEUSER", DbType = "VARCHAR2(8 CHAR) NULL")]
public string CREATEUSER
{
get
{
return this._CREATEUSER;
}
set
{
if (this._CREATEUSER != value)
{
this.OnCREATEUSERChanging(value);
this.SendPropertyChanging();
this._CREATEUSER = value;
this.SendPropertyChanged("CREATEUSER");
this.OnCREATEUSERChanged();
}
}
}
/// <summary>
/// There are no comments for CREATEDATE in the schema.
/// </summary>
[Column(Name = @"CREATE_DATE", Storage = "_CREATEDATE", CanBeNull = false, DbType = "DATE NOT NULL", IsPrimaryKey = true)]
public System.DateTime CREATEDATE
{
get
{
return this._CREATEDATE;
}
set
{
if (this._CREATEDATE != value)
{
this.OnCREATEDATEChanging(value);
this.SendPropertyChanging();
this._CREATEDATE = value;
this.SendPropertyChanged("CREATEDATE");
this.OnCREATEDATEChanged();
}
}
}
Re: ORA-00933: SQL command not properly ended
We have reproduced the error and are investigating the issue. But the SQL generated in our environment is different from yours. Please specify the exact version (x.x.x) of your dotConnect for Oracle.
-
- Posts: 51
- Joined: Mon 09 Apr 2012 09:57
Re: ORA-00933: SQL command not properly ended
Devart.Data.Oracle.OracleConnection, Devart.Data.Oracle, Version=6.80.332.0, Culture=neutral ...
Devart.Data.Oracle.Linq.Provider.OracleDataProvider, Devart.Data.Oracle.Linq, Version=3.2.64.0, Culture=neutral, ...
Devart.Data.Oracle.Linq.Provider.OracleDataProvider, Devart.Data.Oracle.Linq, Version=3.2.64.0, Culture=neutral, ...
Re: ORA-00933: SQL command not properly ended
The OUTER APPLY/CROSS APPLY constructions are supported by Oracle server starting from the 12c version. Our implementation in dotConnect for Oracle avoids generation of OUTER APPLY/CROSS APPLY if it is possible, but some LINQ queries cannot be translated without employing OUTER APPLY/CROSS APPLY. In this case you should rewrite your LINQ statement.
The behaviour is changed in the new 8.3.146 build of dotConnect for Oracle: the new "OUTER APPLY is not supported by Oracle Database 11g and lower. Oracle 12c or higher is required to run this LINQ statement correctly. If you need to run this statement with Oracle Database 11g or lower, rewrite it so that it can be converted to SQL, supported by the version of Oracle you use." exception is added.
Please upgrade to 8.3.146 and try your code again.
The behaviour is changed in the new 8.3.146 build of dotConnect for Oracle: the new "OUTER APPLY is not supported by Oracle Database 11g and lower. Oracle 12c or higher is required to run this LINQ statement correctly. If you need to run this statement with Oracle Database 11g or lower, rewrite it so that it can be converted to SQL, supported by the version of Oracle you use." exception is added.
Please upgrade to 8.3.146 and try your code again.