Selecting the Default value of an oracle column in .Net

Question

I am trying to extract an oracle column default value from user_tab_columns, using .Net. It's data type is long, and when I read it using IDataReader i get an empty string. How do I get it's data?

select column_name,data_default from user_tab_columns where column_name='XXX'

Answer 1

The LONG datatype has a lot of restrictions on its usage. One way to work around it would be to build a stored function like this:

CREATE OR REPLACE FUNCTION column_default
   ( p_table VARCHAR2
   , p_column
   ) RETURN VARCHAR2
IS
    l_retval LONG;
BEGIN
    SELECT data_default
    INTO   l_retval
    FROM   user_tab_columns
    WHERE  table_name = p_table
    AND    column_name = p_column;

    RETURN l_retval;
END;

Now call that function from .Net rather than performing the query directly.