user534498
user534498

Reputation: 3984

Read oracle tables using .NET, one LONG type column always returns empty string, how to solve it?

I am reading from a table all_tab_columns, there is a "data_default" column, which is a type of LONG.

If I query the oracle server using sqlplus, such as "select data_default from all_tab_columns where table_name='testtable'", everything is fine. The correct default value for each columns of the table "testtable" can be returned.

But if I use the Oracle.ManagedDataAccess.dll (well, I am using Spartacus library, which in turn uses Oracle.ManagedDataAccess.dll), for the same query, the data_default column always returns empty string '', I tried several other columns such as column_name, table_name, owner etc. they are all fine.

My question is how do I debug such case? I couldn't locate the root cause of this peculiar error. Any suggestion will be welcome.

Thanks.


The source code for spartacus to read from oracle is as follows,

this.v_con = new OracleManaged.OracleConnection(this.v_connectionstring);
this.v_con.Open();
this.v_cmd = new OracleManaged.OracleCommand(p_sql, this.v_con);
if (this.v_timeout > -1)
    this.v_cmd.CommandTimeout = this.v_timeout;
this.v_reader = this.v_cmd.ExecuteReader();

v_table = new System.Data.DataTable(p_tablename);
for (int i = 0; i < this.v_reader.FieldCount; i++)
    v_table.Columns.Add(this.v_reader.GetName(i), typeof(string));

while (this.v_reader.Read())
{
    v_row = v_table.NewRow();
    for (int i = 0; i < this.v_reader.FieldCount; i++)
        v_row[i] = this.v_reader[i].ToString();
    v_table.Rows.Add(v_row);
}

I tried to debug, the v_reader[i].ToString() for data_default column is empty already. Seems there's no way for me to solve it? Is it probably the .dll problem?

Upvotes: 7

Views: 2495

Answers (1)

atokpas
atokpas

Reputation: 3351

Obtaining LONG and LONG RAW Data

ODP.NET fetches and caches rows from the database during the Read method invocations on the OracleDataReader object. The amount of LONG and LONG RAW column data that is retrieved from this operation is determined by InitialLONGFetchSize. More

You may need to set the InitialLONGFetchSize to a non-zero value and use the OracleDataReader's GetOracleString() method, and use ToString() to convert the result into a .Net string.

Upvotes: 11

Related Questions