ORA-00933: SQL command not properly ended

[Dennis Wanke]

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

[Shalex]

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:

Code: Select all

(MyObjectContext.Connection as System.Data.EntityClient.EntityConnection).StoreConnection

b) DbContext:

Code: Select all

(((IObjectContextAdapter)MyDbContext).ObjectContext.Connection as System.Data.EntityClient.EntityConnection).StoreConnection

After this, apply an ADO.NET code with OracleScript which will be executed on the obtained OracleConnection object.

[Dennis Wanke]

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.

[Shalex]

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.

[Dennis Wanke]

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.

[Shalex]

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', ';', '/')

This functionality will not help to execute a script which consists of several commands.
If you expect a delimiter at the end of your commands, please trim the command text correspondingly before assigning it to OracleCommand.

[dcoracle600pro]

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());

[Shalex]

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

[dcoracle600pro]

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).

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)

-- -1 row(s) affected.

[Shalex]

Please send us a small test project with the corresponding DDL/DML script for reproducing the problem.

[dcoracle600pro]

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

[dcoracle600pro]

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();
                }
            }
        }

[Shalex]

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.

[dcoracle600pro]

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, ...

[Shalex]

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.