Class OracleCommon

This class contains the common SQL-syntax for the Oracle DB.

Inheritance
object
Dialect
OracleCommon
Oracle12
Inherited Members
Namespace: SuperOffice.Data.Dialect
Assembly: SoDataBase.BusinessLogic.dll
Syntax
public class OracleCommon : Dialect

Properties

CaseSensitiveDatabase

Is the database case-sensitive by default (something we work hard to make disappear)?

Declaration
protected override bool CaseSensitiveDatabase { get; }
Property Value
Type Description
bool
Overrides

DefaultConnectionString

Default connection string for Oracle

Declaration
public override string DefaultConnectionString { get; }
Property Value
Type Description
string
Overrides

Methods

CheckForDistinct(PrivateSelect)

Some databases (ORACLE!!) are unable to do DISTINCT over the datatype we use for our 2k text strings; in those cases we need to 'fake' a distinct. This method is called when generating the SQL text, and controls whether we should say "distinct" as part of the select

Declaration
public override bool CheckForDistinct(PrivateSelect cmd)
Parameters
Type Name Description
PrivateSelect cmd
Returns
Type Description
bool
Overrides

CheckForDuplicateRow(IDataReader)

Some databases (ORACLE!!) are unable to do DISTINCT over the datatype we use for our 2k text strings; in those cases we need to 'fake' a distinct. This method is called from the SoDataReader to determine if the current row is such a duplicate.

Declaration
public override bool CheckForDuplicateRow(IDataReader reader)
Parameters
Type Name Description
IDataReader reader

Reader containing the row to be evaluated
Returns
Type Description
bool

If true, then this row is a duplicate of one that has been previously passed to this method
Overrides

ConvertFromDbType(object, FieldDataType)

Strings with one space on oracle should be an empty string.

Declaration
public override object ConvertFromDbType(object value, FieldDataType dataType)
Parameters
Type Name Description
object value

value of the field as returned from the database
FieldDataType dataType

data type of the field in the database.
Returns
Type Description
object

Value that can be casted to the default .net type representing the database type.
Overrides
Exceptions
Type Condition
SoIllegalOperationException

Thrown if the data type is of an unsupported format.

ConvertToDbParameterType(object, FieldDataType)

This class contains the common SQL-syntax for the Oracle DB.

Declaration
public override object ConvertToDbParameterType(object value, FieldDataType dataType)
Parameters
Type Name Description
object value
FieldDataType dataType
Returns
Type Description
object
Overrides

ConvertToDbType(object, FieldDataType)

Empty strings are saved by SuperOffice on Oracle databases as a space, so we override this.

Declaration
public override string ConvertToDbType(object value, FieldDataType dataType)
Parameters
Type Name Description
object value

The value to convert.
FieldDataType dataType

The data type of the value in the database.
Returns
Type Description
string

A string that can be used in a database SQL command.
Overrides
Exceptions
Type Condition
SoIllegalOperationException

Thrown if the data type is of an unsupported format.

DataReaderGetInt16(SoDataReader, IDataReader, int)

Gets the value of the specified column as a 16-bit signed integer.

Declaration
public override short DataReaderGetInt16(SoDataReader soReader, IDataReader innerReader, int i)
Parameters
Type Name Description
SoDataReader soReader

DataReader to read from.
IDataReader innerReader

inner data reader
int i

column index
Returns
Type Description
short

the integer or 0 if null
Overrides

DataReaderGetItem(SoDataReader, IDataReader, int)

Oracle specific data read - does some special Oracle handling on number columns

Declaration
protected override object DataReaderGetItem(SoDataReader soReader, IDataReader innerReader, int i)
Parameters
Type Name Description
SoDataReader soReader

outer reader
IDataReader innerReader

inner reader
int i

column index
Returns
Type Description
object

number, string or whatever
Overrides

DataReaderGetString(SoDataReader, IDataReader, int)

Gets the value of the specified column as a string.

Declaration
public override string DataReaderGetString(SoDataReader soReader, IDataReader innerReader, int i)
Parameters
Type Name Description
SoDataReader soReader

DataReader to read from.
IDataReader innerReader
int i
Returns
Type Description
string
Overrides

DateToVarchar(string)

Subclass override: How to convert a datetime to a varchar (or whatever it's called), in the database, taking only the DATE part

Declaration
protected override string DateToVarchar(string innerValue)
Parameters
Type Name Description
string innerValue

The actual reference to the datetime to work on; this is an opaque parameter that the method should NOT attempt to understand, just assume it will be a valid datetime value or parameter at query-execution time
Returns
Type Description
string
Overrides

GetIsolationLevel(IsolationLevel)

The oracle databases only support ReadCommitted or Serializable through the Microsoft Oracle data provider.

Declaration
public override IsolationLevel GetIsolationLevel(IsolationLevel requestedLevel)
Parameters
Type Name Description
IsolationLevel requestedLevel

Requested isolation level
Returns
Type Description
IsolationLevel

Chaos, ReadUncommitted and ReadCommitted result in ReadCommitted. All other result in Serializable.
Overrides

TimeToVarchar(string)

Subclass override: How to convert a datetime to a varchar (or whatever it's called), in the database, taking only the TIME part

Declaration
protected override string TimeToVarchar(string innerValue)
Parameters
Type Name Description
string innerValue

The actual reference to the datetime to work on; this is an opaque parameter that the method should NOT attempt to understand, just assume it will be a valid datetime value or parameter at query-execution time
Returns
Type Description
string
Overrides

ToSql(Add)

The Add-operator will return the correct field and its correct sqlOperator.

T0.associate_id = T0.associate_id + 1;
Declaration
protected override string ToSql(Add arg)
Parameters
Type Name Description
Add arg

The Add-argument
Returns
Type Description
string

The parsed Add operation as a string expression.
Overrides

ToSql(DateAdd)

This class contains the common SQL-syntax for the Oracle DB.

Declaration
protected override string ToSql(DateAdd arg)
Parameters
Type Name Description
DateAdd arg
Returns
Type Description
string
Overrides

ToSql(Equal)

left = right or UPPER(left) = UPPER(right) for case-insensitive string searches

The text-field in the database is of type CLOB. It is not possible to use the EQUAL operator on this type, and conversions to VARCHAR via the DBMS_LOB.SUBSTR will fail if the data in the table expands to more than 4000 bytes. Fortunately the LIKE operator does work in these circumstances, so we'll use it.
Declaration
protected override string ToSql(Equal arg)
Parameters
Type Name Description
Equal arg

The EQUAL arguments.
Returns
Type Description
string

A parsed and build sql string according with the necessary alterations if dealing with a clob-field.
Overrides

ToSql(FieldTypeConvert)

ToSql( FieldTypeConvert arg ) parse the FieldTypeConvert-part of an sql-command and returns the correct database-specific syntax for the conversion.

Declaration
protected override string ToSql(FieldTypeConvert arg)
Parameters
Type Name Description
FieldTypeConvert arg

The argument to parse.
Returns
Type Description
string

A parsed sql-statement corresponding to the incoming argument.
Overrides

ToSql(In)

Oracle needs special handling of the In-operator.

Declaration
protected override string ToSql(In arg)
Parameters
Type Name Description
In arg

The In-argument to parse.
Returns
Type Description
string

A parsed sql-statement corresponding to the incoming argument.
Overrides
Remarks

If the number of parameters in the In-clause are equal to 1, it prefers that the equal-operator is used instead. ToSql( In arg ) checks the number of arguments and does one of two. Call the base-class ToSql( In arg )-method or cast the in-parameter as an UnEqual-object. Then we call the ToSql( UnEqual arg ) method.

ToSql(JsonValue)

Oracle requires the second parameter (the Path) to be a literal, part of the SQL. Variables are not supported

Declaration
protected override string ToSql(JsonValue arg)
Parameters
Type Name Description
JsonValue arg
Returns
Type Description
string
Overrides
Remarks

Sql injection risk? The JsonValue constructor has strict filtering of the legal path characters.

ToSql(Like)

Case insensitive LIKE operator on Oracle database. Note that = is case sensitive on Oracle.

Declaration
protected override string ToSql(Like arg)
Parameters
Type Name Description
Like arg

The Like argument
Returns
Type Description
string

UPPER(left) LIKE UPPER(right)
Overrides
Remarks

We add UPPER on both sides of the LIKE operator.

ToSql(NotIn)

Oracle needs special handling of the NotIn-operator.

Declaration
protected override string ToSql(NotIn arg)
Parameters
Type Name Description
NotIn arg

The NotIn-argument to parse.
Returns
Type Description
string

A parsed sql-statement corresponding to the incoming argument.
Overrides
Remarks

If the number of parameters in the NotIn-clause are equal to 1, it prefers that the not equal-operator is used instead. ToSql( NotIn arg ) checks the number of arguments and does one of two. Call the base-class ToSql( NotIn arg )-method or cast the NotIn-parameter as an UnEqual-object. Then we call the ToSql( UnEqual arg ) method.

ToSql(NotLike)

Common handling of the NotLike operator on Oracle database.

Declaration
protected override string ToSql(NotLike arg)
Parameters
Type Name Description
NotLike arg

The Not Like argument
Returns
Type Description
string

A parsed sql-statement corresponding to the incoming argument.
Overrides
Remarks

We add UPPER on both sides of the operator.

ToSql(OrderBy)

The Order By is used when you want your data shown in a specific order. On Oracle, CLOB fields need to be converted to more "ordinary" substrings before they can be used for orderby

Declaration
protected override string ToSql(OrderBy arg)
Parameters
Type Name Description
OrderBy arg

The OrderBy clause
Returns
Type Description
string

The Order By sql-clause.
Overrides

ToSql(Parameter)

Return a parameter

Declaration
protected override string ToSql(Parameter arg)
Parameters
Type Name Description
Parameter arg

the Parameter argument
Returns
Type Description
string
Overrides

ToSql(UnEqual)

The text-field in the database is of type CLOB. It is not possible to use the UNEQUAL operator on this type, and conversions to VARCHAR via the DBMS_LOB.SUBSTR will fail if the data in the table expands to more than 4000 bytes. Fortunately the LIKE operator does work in these circumstances, so we'll use it.

Declaration
protected override string ToSql(UnEqual arg)
Parameters
Type Name Description
UnEqual arg

The UNEQUAL argument.
Returns
Type Description
string

A parsed and build sql string according with the necessary alterations if dealing with a clob-field.
Overrides

ToSql(WaitFor)

This class contains the common SQL-syntax for the Oracle DB.

Declaration
protected string ToSql(WaitFor arg)
Parameters
Type Name Description
WaitFor arg
Returns
Type Description
string

Extension Methods