CODEWITHSUNDEEP
c#oracle-databasestored-proceduresado.netsys-refcursor
alwayslearning
alwayslearning

Reputation: 4633

Obtain column information of result set returned via a ref cursor parameter

I have a stored procedure in Oracle that returns result set(s) via OUT parameter(s) of type 'SYS_REFCURSOR'.

I need to get the column information of this result set via ADO.NET. I execute the stored procedure by creating the parameters (OracleParameter.OracleDbType = OracleDbType.ReCursor etc) and calling 'OracleCommand.ExecuteReader'(CommandBehavior.SchemaOnly). I then call 'reader.GetSchemaTable' on the resultant reader to obtain the DataTable that describes the schema of the result set. 

OracleCommand command = oracleConnection.CreateCommand();
command.CommandText = "ProcedureName";
command.CommandType = System.Data.CommandType.StoredProcedure;

OracleParameter refParameter = command.CreateParameter();
refParameter.Name = "refCursorParam";
refParameter.Direction = System.Data.ParameterDirection.Output;
refParameter.OracleDbType = OracleDbType.ReCursor;
command.Parameters.Add(dbParameter);

var reader = command.ExecuteReader(System.Data.CommandBehavior.SchemaOnly);
var dataTable = reader.GetSchemaTable();

My problem is that the information for each column of the result set is incomplete. I have no information about the column's oracle data type (I do get the column name,nullability,length etc). Only type information I get is the CLR type that column can be mapped to.

Am I doing something wrong or is there a better approach to get the column information for a result set returned via an OUT ref cursor?

Upvotes: 1

Views: 2215

Answers (1)

alwayslearning
alwayslearning

Reputation: 4633

Ok, I managed to find a solution. Each column in the result set is represented by a DataRow in the DataTable returned by reader.GetSchemaTable(). One column in this row is 'ProviderType'. The value for this column is an indicator of the column's data type. The value in this column needs to be used together with the table returned by a call to DbConnection.GetSchema("DataTypes"). The 'DataTypes' collection has a mapping of 'ProviderType->sql type'.

For SQL server there is an additional column 'DataTypeName' which gives the sql type for the column in the result set, but for Oracle you need to make an additional call to connection.GetSchema("DataTypes") and setup a dictionary of providertype->sqltype and then obtain the sql type for each column in the result set.

Upvotes: 2

Related Questions