Bind to HIGH_VALUE

Question

Using VB.NET I am putting the results of the SQL Query:

select TABLE_NAME, PARTITION_NAME, HIGH_VALUE from user_tab_partitions WHERE TABLE_NAME LIKE 'LXA%' OR TABLE_NAME LIKE 'LNX%'

into a DataTable (via an Oracle.DataAccess.Client.OracleDataReader).

I am then binding a WPF DataGrid to the contents of that table, using ItemsSource="{Binding Source={x:Static l:Handlers.DatabaseHandler}, Path=DBPartitionData}" on the table and Binding="{Binding TABLE_NAME}" on the DataGridTextColumn.

This works fine for TABLE_NAME and PARTITION_NAME, but HIGH_VALUE doesn't display. The SQL query works fine in Oracle SQL Developer.

Looking around, it seems that HIGH_VALUE is stored in some internal oracle format - so my question is:

How do I bind to that value? Or even use it? Which of the Oracle.DataAccess.Types can I use (if any), or if all else fails, how can I display the value directly without parsing it? ToString() on the returned objects seems to give just empty strings.

There's plenty of sites that explain how to get the value using pl/SQL, but nothing for how to display it as (any sort of) a string in .NET.

Thanks for your help.

--edit--

I can't debug and see what's in the returned datatable from the sql query, because I don't have a version of Oracle with partitioning on my local machine - only on the test machine (which doesn't have VS). But, putting in some logging, it seems that: the returned HIGH_VALUE values are non-null and ToString() on them returns a zero-length value.

Answer 1

The USER_TAB_PARTITIONS.HIGH_VALUE column is a LONG, which Oracle deprecated a long time ago but still use internally. They are a bit of a pain to work with, which is why they tell everyone else to use LOBs instead.

From the OracleDataReader documentation:

By default, InitialLONGFetchSize is set to 0. In this case, ODP.NET does not fetch any LONG or LONG RAW column data during the Read method invocations on the OracleDataReader object.

Although it goes on to mention explicitly invoking a typed accessor method, it also then says:

To be able to fetch the entire LONG or LONG RAW data without having a primary key column, a ROWID, or unique columns in the select list, set the size of the InitialLONGFetchSize property on the OracleCommand object to equal or greater than the number of characters or bytes needed to be retrieved.

and

By setting InitialLONGFetchSize to -1, it is possible to fetch the entire LONG or LONG RAW data from the database for a select query, without requiring a primary key, ROWID, or unique column in the select list.

In your case it seems that just changing the InitialLONGFetchSize to -1 solves your problem and causes the entire value to be retrieved and displayed.