private static KeyValuePair <OracleObjectIdentifier, OracleColumn> MapTableColumn(IDataRecord reader, Version version)
{
var dataTypeIdentifier = OracleObjectIdentifier.Create(QualifyStringObject(reader["DATA_TYPE_OWNER"]), QualifyStringObject(reader["DATA_TYPE"]));
var dataType =
new OracleDataType
{
FullyQualifiedName = dataTypeIdentifier,
Length = Convert.ToInt32(reader["DATA_LENGTH"]),
Precision = OracleReaderValueConvert.ToInt32(reader["DATA_PRECISION"]),
Scale = OracleReaderValueConvert.ToInt32(reader["DATA_SCALE"])
};
ResolveDataUnit(dataType, reader["CHAR_USED"]);
var column =
new OracleColumn
{
Name = QualifyStringObject(reader["COLUMN_NAME"]),
DataType = dataType,
Nullable = String.Equals((string)reader["NULLABLE"], "Y"),
Virtual = String.Equals((string)reader["VIRTUAL_COLUMN"], "YES"),
DefaultValue = OracleReaderValueConvert.ToString(reader["DATA_DEFAULT"]),
CharacterSize = Convert.ToInt32(reader["CHAR_LENGTH"]),
UserGenerated = true
};
if (version.Major >= 12)
{
column.Hidden = String.Equals((string)reader["HIDDEN_COLUMN"], "YES");
column.UserGenerated = String.Equals((string)reader["USER_GENERATED"], "YES");
}
return(new KeyValuePair <OracleObjectIdentifier, OracleColumn>(
OracleObjectIdentifier.Create(QualifyStringObject(reader["OWNER"]), QualifyStringObject(reader["TABLE_NAME"])), column));
}
public void VisitColumnReference(OracleColumnReference columnReference)
{
var matchedColumns = columnReference.ValidObjectReference?.Columns.Where(c => String.Equals(columnReference.NormalizedName, c.Name)).ToArray();
if (matchedColumns?.Length != 1)
{
return;
}
var dataType = matchedColumns[0].DataType;
if (dataType.IsDynamicCollection)
{
_columns.Add(OracleColumn.BuildColumnValueColumn(OracleDataType.Empty));
}
else
{
var semanticModel = columnReference.Owner.SemanticModel;
if (semanticModel.HasDatabaseModel)
{
var schemaObject = semanticModel.DatabaseModel.GetFirstSchemaObject <OracleTypeCollection>(dataType.FullyQualifiedName);
if (schemaObject != null)
{
_columns.Add(OracleColumn.BuildColumnValueColumn(((OracleTypeCollection)schemaObject).ElementDataType));
}
}
}
}
public void VisitTypeReference(OracleTypeReference typeReference)
{
if (typeReference.SchemaObject.GetTargetSchemaObject() is OracleTypeCollection collectionType)
{
_columns.Add(OracleColumn.BuildColumnValueColumn(collectionType.ElementDataType));
}
}
public static void ValidateCondition(OracleValidationModel validationModel, OracleColumnReference columnReference)
{
var columnDataTypeName = columnReference.ColumnDescription?.DataType.FullyQualifiedName.NormalizedName.Trim('"');
if (String.IsNullOrEmpty(columnDataTypeName) || !columnReference.ColumnDescription.DataType.IsPrimitive)
{
return;
}
var condition = FindCondition(columnReference.RootNode);
if (condition == null)
{
return;
}
var expression2 = condition[2];
if (String.Equals(condition[1]?.Id, NonTerminals.RelationalOperator) &&
String.Equals(expression2?.Id, NonTerminals.Expression))
{
var expression1 = condition[0];
var referenceExpression = columnReference.RootNode.GetAncestor(NonTerminals.Expression);
var expressionToCheck = expression1 == referenceExpression
? expression2
: expression1;
if (expressionToCheck[NonTerminals.ExpressionMathOperatorChainedList] != null)
{
return;
}
var dummyColumn = new OracleColumn();
if (!OracleDataType.TryResolveDataTypeFromExpression(expressionToCheck, dummyColumn))
{
return;
}
var literalDataTypeName = dummyColumn.DataType.FullyQualifiedName.NormalizedName.Trim('"');
var isColumnTimeOrNumber = IsTime(columnDataTypeName) || IsNumeric(columnDataTypeName);
var isLiteralTimeOrNumber = IsTime(literalDataTypeName) || IsNumeric(literalDataTypeName);
if (isColumnTimeOrNumber && IsText(literalDataTypeName) || isLiteralTimeOrNumber && IsText(columnDataTypeName))
{
validationModel.AddNonTerminalSuggestion(expressionToCheck, String.Format(OracleSuggestionType.ImplicitConversionWarning, columnDataTypeName, literalDataTypeName));
}
}
}
private void SetColumns(OracleTableSchema oracleTable)
{
if (oracleTable == null)
{
return;
}
oracleTable.Columns = new ColumnCollection();
string sql = @"SELECT TC.COLUMN_NAME,
TC.DATA_TYPE,
NVL(DECODE(TC.CHAR_LENGTH,0,TC.DATA_PRECISION,TC.CHAR_LENGTH),TC.DATA_LENGTH) AS DATA_LENGTH,
TC.DATA_PRECISION,
NVL(TC.DATA_SCALE, -1) DATA_SCALE,
TC.NULLABLE,
TC.DATA_DEFAULT,
CC.COMMENTS
FROM USER_TAB_COLUMNS TC
LEFT JOIN USER_COL_COMMENTS CC
ON TC.COLUMN_NAME = CC.COLUMN_NAME
AND TC.TABLE_NAME = CC.TABLE_NAME
WHERE TC.TABLE_NAME=:TABLE_NAME ORDER BY TC.COLUMN_ID ASC";
List <IColumn> columns = new List <IColumn>();
OracleParameter para = new OracleParameter("TABLE_NAME", oracleTable.Name);
DbHelper helper = new DbHelper(this._connectionString);
var table = helper.ListBySql(sql, para);
foreach (DataRow row in table.Rows)
{
int scale = Convert.ToInt32(row["DATA_SCALE"]);
string data_type = row["DATA_TYPE"] + string.Empty;
OracleColumn column = new OracleColumn
{
Name = row["COLUMN_NAME"] + string.Empty,
Comment = row["COMMENTS"] + string.Empty,
CsharpType = OracleUtils.TransformDatabaseType(data_type, scale),
DbType = data_type,
DefaultValue = (row["DATA_DEFAULT"] + string.Empty).Trim('\r', '\n'),
IsNullable = (row["NULLABLE"] + string.Empty) == "Y",
Length = Convert.ToInt32(row["DATA_LENGTH"]),
Scale = scale,
Table = oracleTable,
IsNumeric = OracleUtils.IsNumeric(data_type)
};
oracleTable.Columns.Add(column);
}
}
private static ColumnDescriptionItem GetExpandedColumn(OracleReference objectReference, OracleColumn column, bool isPseudocolumn)
{
var nullable = objectReference?.SchemaObject.GetTargetSchemaObject() is OracleView
? (bool?)null
: column.Nullable;
return
(new ColumnDescriptionItem
{
OwnerIdentifier = objectReference?.FullyQualifiedObjectName ?? OracleObjectIdentifier.Empty,
ColumnName = OracleCodeCompletionProvider.GetPrettyColumnName(column.Name),
DataType = column.FullTypeName,
Nullable = nullable,
IsPseudocolumn = isPseudocolumn,
IsHidden = column.Hidden
});
}
protected override IReadOnlyList <OracleColumn> BuildPseudocolumns()
{
var pseudocolumns = new List <OracleColumn>();
var table = SchemaObject.GetTargetSchemaObject() as OracleTable;
if (Type != ReferenceType.SchemaObject || table == null)
{
return(pseudocolumns.AsReadOnly());
}
if (table.Organization == OrganizationType.Heap || table.Organization == OrganizationType.Index)
{
var rowIdPseudocolumn =
new OracleColumn(true)
{
Name = RowIdNormalizedName,
DataType =
new OracleDataType
{
FullyQualifiedName = OracleObjectIdentifier.Create(null, table.Organization == OrganizationType.Index ? TerminalValues.UniversalRowId : TerminalValues.RowIdDataType)
}
};
pseudocolumns.Add(rowIdPseudocolumn);
}
if (FlashbackOption == FlashbackOption.None || FlashbackOption == FlashbackOption.AsOf)
{
var rowSystemChangeNumberPseudocolumn =
new OracleColumn(true)
{
Name = "\"ORA_ROWSCN\"",
DataType = OracleDataType.NumberType
};
pseudocolumns.Add(rowSystemChangeNumberPseudocolumn);
}
else if ((FlashbackOption & FlashbackOption.Versions) == FlashbackOption.Versions)
{
var flashbackVersionColumns =
new[]
{
new OracleColumn(true)
{
Name = "\"VERSIONS_STARTTIME\"",
DataType = OracleDataType.CreateTimestampDataType(0)
},
new OracleColumn(true)
{
Name = "\"VERSIONS_ENDTIME\"",
DataType = OracleDataType.CreateTimestampDataType(0)
},
new OracleColumn(true)
{
Name = "\"VERSIONS_STARTSCN\"",
DataType = OracleDataType.NumberType
},
new OracleColumn(true)
{
Name = "\"VERSIONS_ENDSCN\"",
DataType = OracleDataType.NumberType
},
new OracleColumn(true)
{
Name = "\"VERSIONS_OPERATION\"",
DataType = new OracleDataType {
FullyQualifiedName = OracleObjectIdentifier.Create(null, TerminalValues.Varchar2), Unit = DataUnit.Byte, Length = 1
}
},
new OracleColumn(true)
{
Name = "\"VERSIONS_XID\"",
DataType = new OracleDataType {
FullyQualifiedName = OracleObjectIdentifier.Create(null, TerminalValues.Raw), Length = 8
}
}
};
pseudocolumns.AddRange(flashbackVersionColumns);
}
return(pseudocolumns.AsReadOnly());
}
private OracleColumn BuildColumnDescription()
{
var columnReference = IsDirectReference && ColumnReferences.Count == 1
? ColumnReferences[0]
: null;
var columnDescription = columnReference?.ColumnDescription;
_columnDescription =
new OracleColumn
{
Name = ColumnName,
Nullable = columnDescription == null,
DataType = OracleDataType.Empty
};
if (columnDescription != null)
{
_columnDescription.Nullable = columnDescription.Nullable;
_columnDescription.DataType = columnDescription.DataType;
_columnDescription.CharacterSize = columnDescription.CharacterSize;
if (!_columnDescription.Nullable)
{
if (columnReference.ValidObjectReference is OracleDataObjectReference objectReference)
{
_columnDescription.Nullable = objectReference.IsOuterJoined;
}
}
}
if (IsAsterisk || RootNode.TerminalCount == 0)
{
return(_columnDescription);
}
var expressionNode = RootNode[0];
if (String.Equals(expressionNode.Id, NonTerminals.AliasedExpression))
{
expressionNode = expressionNode[0];
}
if (OracleDataType.TryResolveDataTypeFromExpression(expressionNode, _columnDescription) && !_columnDescription.DataType.IsDynamicCollection)
{
if (_columnDescription.DataType.FullyQualifiedName.Name.EndsWith("CHAR"))
{
_columnDescription.CharacterSize = _columnDescription.DataType.Length;
}
var isBuiltInDataType = _columnDescription.DataType.IsPrimitive && OracleDatabaseModelBase.BuiltInDataTypes.Any(t => String.Equals(t, _columnDescription.DataType.FullyQualifiedName.Name));
if (!isBuiltInDataType && SemanticModel.HasDatabaseModel)
{
var oracleType = SemanticModel.DatabaseModel.GetFirstSchemaObject <OracleTypeBase>(_columnDescription.DataType.FullyQualifiedName);
if (oracleType == null)
{
_columnDescription.DataType = OracleDataType.Empty;
}
}
}
else if (columnDescription == null)
{
expressionNode = expressionNode.UnwrapIfNonChainedExpressionWithinParentheses(out bool isChainedExpression);
if (!isChainedExpression)
{
var programReference = ProgramReferences.SingleOrDefault(r => r.RootNode == expressionNode);
if (programReference == null)
{
var typeReference = TypeReferences.SingleOrDefault(r => r.RootNode == expressionNode);
if (typeReference?.Metadata != null)
{
var x = typeReference.Metadata.ReturnParameter.CustomDataType;
}
}
else if (programReference.Metadata != null)
{
if (programReference.Metadata.ReturnParameter == null)
{
if (programReference.Metadata.Identifier == OracleProgramIdentifier.IdentifierBuiltInProgramCoalesce)
{
}
else if (programReference.Metadata.Identifier == OracleProgramIdentifier.IdentifierBuiltInProgramBinaryToNumber)
{
_columnDescription.DataType = OracleDataType.NumberType;
}
}
else if (!String.IsNullOrEmpty(programReference.Metadata.ReturnParameter.DataType))
{
if (programReference.Metadata.Identifier != OracleProgramIdentifier.IdentifierBuiltInProgramNvl)
{
_columnDescription.DataType = new OracleDataType {
FullyQualifiedName = OracleObjectIdentifier.Create(null, programReference.Metadata.ReturnParameter.DataType)
};
switch (programReference.Metadata.ReturnParameter.DataType)
{
case TerminalValues.Varchar:
case TerminalValues.Varchar2:
_columnDescription.CharacterSize = _columnDescription.DataType.Length = SemanticModel.DatabaseModel.MaximumVarcharLength;
break;
case TerminalValues.Raw:
_columnDescription.DataType.Length = SemanticModel.DatabaseModel.MaximumRawLength;
break;
case TerminalValues.NVarchar:
case TerminalValues.NVarchar2:
_columnDescription.CharacterSize = _columnDescription.DataType.Length = SemanticModel.DatabaseModel.MaximumNVarcharLength;
break;
case TerminalValues.Timestamp:
_columnDescription.DataType = OracleDataType.CreateTimestampDataType(9);
break;
case OracleDatabaseModelBase.BuiltInDataTypeTimestampWithTimeZone:
_columnDescription.DataType = OracleDataType.CreateTimestampWithTimeZoneDataType(9);
break;
}
}
}
}
}
}
return(_columnDescription);
}
protected override IReadOnlyList<OracleColumn> BuildColumns()
{
var columns = new List<OracleColumn>();
var pivotColumns = new List<OracleSelectListColumn>();
var pivotForColumnList = PivotClause[NonTerminals.PivotForClause, NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList];
if (pivotForColumnList != null)
{
var aggregatedColumnNames =
SourceReferenceContainer.ColumnReferences
.Where(c => AggregateFunctions.Any(f => f.SourcePosition.Contains(c.RootNode.SourcePosition)))
.Select(c => c.NormalizedName);
var groupingColumns = pivotForColumnList
.GetDescendants(Terminals.Identifier)
.Select(i => i.Token.Value.ToQuotedIdentifier())
.Concat(aggregatedColumnNames)
.ToHashSet();
switch (PivotClause.Id)
{
case NonTerminals.PivotClause:
var sourceColumns = SourceReference.Columns
.Where(c => !groupingColumns.Contains(c.Name))
.Select(c => c.Clone());
columns.AddRange(sourceColumns);
var withXmlTransformation = PivotClause[Terminals.Xml] != null;
if (withXmlTransformation)
{
var xmlColumnName = String.Join("_", groupingColumns.Select(c => c.Trim('"')));
if (xmlColumnName.Length > 26)
{
xmlColumnName = xmlColumnName.Substring(0, 26);
}
var xmlColumn =
new OracleColumn
{
Name = $"\"{xmlColumnName}_XML\"",
DataType = OracleDataType.XmlType,
Nullable = true
};
columns.Add(xmlColumn);
}
else
{
pivotColumns.AddRange(ResolvePivotColumns());
columns.AddRange(pivotColumns.Select(c => c.ColumnDescription));
}
break;
case NonTerminals.UnpivotClause:
var unpivotColumnSources = new List<StatementGrammarNode>();
var unpivotedColumns = new HashSet<string>();
var unpivotColumnSelectorValues = new List<StatementGrammarNode>();
var columnTransformations = PivotClause[NonTerminals.UnpivotInClause].GetDescendants(NonTerminals.UnpivotValueToColumnTransformationList);
var groupingColumnsNullable = false;
foreach (var columnTransformation in columnTransformations)
{
unpivotedColumns.UnionWith(columnTransformation.GetDescendants(Terminals.Identifier).Select(t => t.Token.Value.ToQuotedIdentifier()));
var columnSelectorValue = columnTransformation[NonTerminals.UnpivotValueSelector, NonTerminals.NullOrStringOrNumberLiteralOrParenthesisEnclosedStringOrIntegerLiteralList];
if (columnSelectorValue != null)
{
unpivotColumnSelectorValues.Add(columnSelectorValue);
groupingColumnsNullable |= columnSelectorValue.TerminalCount == 1 || String.Equals(columnSelectorValue.FirstTerminalNode.Id, Terminals.Null);
}
unpivotColumnSources.AddIfNotNull(columnTransformation[NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList]);
}
var unpivotColumnDataTypes = OracleDataType.FromUnpivotColumnSelectorValues(unpivotColumnSelectorValues);
AreUnpivotColumnSelectorValuesValid = unpivotColumnDataTypes != null;
UnpivotColumnSelectorValues = unpivotColumnSelectorValues.AsReadOnly();
UnpivotColumnSources = unpivotColumnSources.AsReadOnly();
columns.AddRange(SourceReference.Columns
.Where(c => !unpivotedColumns.Contains(c.Name))
.Select(c => c.Clone()));
columns.AddRange(groupingColumns.Select(
(c, i) =>
new OracleColumn
{
Name = c,
Nullable = groupingColumnsNullable,
DataType = groupingColumns.Count == unpivotColumnDataTypes?.Count
? unpivotColumnDataTypes[i]
: OracleDataType.Empty
}));
var unpivotColumnsNullable = PivotClause[NonTerminals.UnpivotNullsClause, NonTerminals.IncludeOrExclude, Terminals.Include] != null;
var unpivotColumns = PivotClause[NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList].GetDescendants(Terminals.Identifier);
columns.AddRange(
unpivotColumns.Select(
i => new OracleColumn
{
Name = i.Token.Value.ToQuotedIdentifier(),
DataType = OracleDataType.Empty,
Nullable = unpivotColumnsNullable
}));
break;
}
}
PivotColumns = pivotColumns.AsReadOnly();
return columns.AsReadOnly();
}
public void VisitProgramReference(OracleProgramReference programReference)
{
var programMetadata = programReference.Metadata;
var semanticModel = programReference.Owner.SemanticModel;
if (programMetadata == null || !semanticModel.HasDatabaseModel)
{
return;
}
if (programReference.ParameterReferences.Count == 2 && programMetadata.Identifier == OracleProgramIdentifier.IdentifierBuiltInProgramCast)
{
var dataTypeNode = programReference.ParameterReferences[1].ParameterNode;
var dataTypeReference = programReference.Container.DataTypeReferences.SingleOrDefault(dt => dt.RootNode == dataTypeNode);
if (dataTypeReference?.SchemaObject.GetTargetSchemaObject() is OracleTypeCollection collectionType)
{
_columns.Add(OracleColumn.BuildColumnValueColumn(collectionType.ElementDataType));
}
}
if (programMetadata.Parameters.Count <= 1)
{
return;
}
var dataTypeType = programMetadata.Parameters[0].DataType;
if (!String.Equals(dataTypeType, OracleTypeCollection.OracleCollectionTypeNestedTable) && !String.Equals(dataTypeType, OracleTypeCollection.OracleCollectionTypeVarryingArray))
{
return;
}
var returnParameter = programMetadata.Parameters.SingleOrDefault(p => p.Direction == ParameterDirection.ReturnValue && p.DataLevel == 1 && p.Position == 1);
if (returnParameter == null)
{
return;
}
OracleSchemaObject schemaObject;
if (String.Equals(returnParameter.DataType, OracleTypeBase.TypeCodeObject))
{
if (semanticModel.DatabaseModel.AllObjects.TryGetValue(returnParameter.CustomDataType, out schemaObject))
{
var attributeColumns = ((OracleTypeObject)schemaObject).Attributes
.Select(a =>
new OracleColumn
{
DataType = a.DataType,
Nullable = true,
Name = a.Name
});
_columns.AddRange(attributeColumns);
}
}
else if (semanticModel.DatabaseModel.AllObjects.TryGetValue(programMetadata.Parameters[0].CustomDataType, out schemaObject))
{
_columns.Add(OracleColumn.BuildColumnValueColumn(((OracleTypeCollection)schemaObject).ElementDataType));
}
}
private object ExecuteScalarInternal(bool needCLStype, bool needRowid, out OciRowidDescriptor rowidDescriptor)
{
OciStatementHandle statementHandle = null;
object oracleValue = null;
int rc = 0;
try
{
statementHandle = this.GetStatementHandle();
ArrayList resultParameterOrdinals = new ArrayList();
this.Execute(statementHandle, CommandBehavior.Default, needRowid, out rowidDescriptor, out resultParameterOrdinals);
if (OCI.STMT.OCI_STMT_SELECT != this._statementType)
{
return oracleValue;
}
OracleColumn column = new OracleColumn(statementHandle, 0, this.ErrorHandle, this._connection);
int offset = 0;
bool success = false;
bool mustRelease = false;
SafeHandle handleToBind = null;
column.Describe(ref offset, this._connection, this.ErrorHandle);
NativeBuffer_RowBuffer buffer = new NativeBuffer_RowBuffer(offset, 1);
RuntimeHelpers.PrepareConstrainedRegions();
try
{
buffer.DangerousAddRef(ref success);
column.Bind(statementHandle, buffer, this.ErrorHandle, 0);
column.Rebind(this._connection, ref mustRelease, ref handleToBind);
rc = TracedNativeMethods.OCIStmtFetch(statementHandle, this.ErrorHandle, 1, OCI.FETCH.OCI_FETCH_NEXT, OCI.MODE.OCI_DEFAULT);
if (100 != rc)
{
if (rc != 0)
{
this.Connection.CheckError(this.ErrorHandle, rc);
}
if (needCLStype)
{
oracleValue = column.GetValue(buffer);
}
else
{
oracleValue = column.GetOracleValue(buffer);
}
}
}
finally
{
if (mustRelease)
{
handleToBind.DangerousRelease();
}
if (success)
{
buffer.DangerousRelease();
}
}
GC.KeepAlive(column);
}
finally
{
if (statementHandle != null)
{
this.ReleaseStatementHandle(statementHandle);
}
}
return oracleValue;
}
