Rahul Vijay Dawda Rahul Vijay Dawda - 2 months ago 16
C# Question

all_ind_expressions view of Oracle

I'm trying to execute the below query through

OracleDataReader
in .NET but when I try to read the value of the column_expressions column, I always get an empty string.

SELECT ic.column_name,
ie.column_expression
FROM all_ind_columns ic
LEFT JOIN all_ind_expressions ie
ON ie.index_owner = ic.index_owner
AND ie.index_name = ic.index_name
AND ie.column_position = ic.column_position
WHERE ic.index_owner = 'owner_name'
AND ic.index_name = 'index_name'


I realized that the datatype of the column
id
is
LONG
but I'm not sure if that's the reason. Is there a way I can read the actual value of the column?

When I execute the same query through Oracle SQL developer, I can see the value.

Answer

To be able to read a column that is of LONG data type the InitialLONGFetchSize property of OracleCommand has to be set to a none zero(zero by default) value:

Unfortunately you did not provide your .NET code, so I'll give you a C# + ODP.NET unmanaged driver example:

Set-up:

create table t1(
  col1 varchar2(11)
);

create index FBI on t1(upper(col1));

table T1 created.
index FBI created.

C# code:

string oraConnectionString = "Data source=nkpdb;User id=hr;password=password;";
OracleConnection oraConnection = new OracleConnection(oraConnectionString);
oraConnection.Open();
/* Would be better to put this in a stored procedure */      
string sqlQuery = "select ic.column_name "  +
                  "     , ie.column_expression " +
                  "  from all_ind_columns ic " +
                  "  left join all_ind_expressions ie " +
                  "    on ie.index_owner      = ic.index_owner " +
                  "   and ie.index_name      = ic.index_name " +
                  "   and ie.column_position = ic.column_position " +
                  " where ic.index_owner   = :INDOwner " +
                  "   and ic.index_name    = :INDName" ;
OracleCommand oraCmd     = new OracleCommand(sqlQuery, oraConnection);
OracleParameter indOwner = new OracleParameter("INDOwner", 
                                                OracleDbType.Varchar2);
OracleParameter indName  = new OracleParameter("INDName", 
                                                OracleDbType.Varchar2);
indOwner.Value = "HR";
indName.Value = "FBI";
oraCmd.Parameters.Add(indOwner);
oraCmd.Parameters.Add(indName);
/* set up initial amount of data that the OracleDataReader 
 * fetches for LONG column */
oraCmd.InitialLONGFetchSize = 1000;  /* set initial size */ 
OracleDataReader oraDataReader = oraCmd.ExecuteReader();
if (oraDataReader.HasRows)
{
     while (oraDataReader.Read())
     {
       Console.WriteLine(oraDataReader.GetString(
                               oraDataReader.GetOrdinal("column_expression")));
      }
 }

Result:

enter image description here

By default the InitialLONGFetchSize property is set to 0. That's the reason why you are getting an empty string. So you either need to set this property to a value greater than zero or set it to -1 to fetch an entire LONG column.

Comments