Oracle column default value in .NET

Question

When I run the following SQL in Oracle, the column default value is reported correctly ('Test'). When I run the same SQL in .NET, the reported default value is NULL:

SELECT column_name, data_default FROM user_tab_columns WHERE table_name='MYTABLENAME'

The column in question is Varchar2. I also tested a number column - same problem.

The database version is Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

The .NET connector is ODP.NET

The operating system is Windows 7 / 64

I get the same result (NULL) from all_tab_columns

.NET Code:

Dim provider As DbProviderFactory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client")
Using connection As DbConnection = provider.CreateConnection
  connection.ConnectionString = "Data Source=localhost; User Id=userid; Password=password"
  connection.Open
  Using dt As New DataTable
    Using command As DbCommand = provider.CreateCommand
      command.CommandText = "SELECT column_name, data_default FROM user_tab_columns WHERE table_name='MYTABLENAME'"
      command.Connection = connection
      Using da As DbDataAdapter = provider.CreateDataAdapter
        da.SelectCommand = command
        da.Fill(dt)
      End Using
    End Using
    For Each row As DataRow in dt.Rows
      Response.Write(row("COLUMN_NAME") & " " & row("DATA_DEFAULT") & "<br>")
    Next
  End Using
End Using

Can you post your .NET code? The Oracle query is fine so the problem must be there. — Ed Gibbs
– Ed Gibbs, Commented Mar 29, 2013 at 15:05
Hmm, this appears to be solid. The only strange thing I see is this line: Using command. I work mostly with C# so my VB.NET is terrible, but in C# you're supposed to instantiate the variable in the using construct - you shouldn't instantiate it ahead of time and supply the instance variable to Using as you've done. A couple of things to try for diagnostic purposes: (1) see what happens if you change the WHERE clause of the query from WHERE table_name='MYTABLENAME' to WHERE ROWNUM < 10. (2) what happens if you get rid of the Using command wrapper? — Ed Gibbs
– Ed Gibbs, Commented Mar 29, 2013 at 15:58

Colin 't Hart · Accepted Answer · 2013-03-29 16:15:49Z

3

data_default is of Oracle type LONG. Going by this documentation http://docs.oracle.com/html/B14164_01/featData.htm#i1007197 it seems that you need to do some special work to retrieve LONG values.

answered Mar 29, 2013 at 16:15

Colin 't Hart

7,8084 gold badges34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ic3b3rg · Accepted Answer · 2013-03-29 21:00:35Z

0

I wound up creating an Oracle function to handle this:

CREATE OR REPLACE 
  FUNCTION GetColumnDefaultValue(
    TableName IN varchar2,
    ColumnName IN varchar2
  )
  RETURN varchar2
    AS ddLong long; 
    BEGIN
      SELECT data_default
      INTO ddLong
      FROM user_tab_columns
      WHERE table_name = TableName
        AND column_name = ColumnName;
    RETURN
      substr(ddLong,0,255);
    END;

The default value is retrieved using:

SELECT GetColumnDefaultValue('TABLENAME','FIELDNAME') FROM dual

Note that this retrieves only the first byte of the default value. Modify substr(ddLong,0,255); to return more.

answered Mar 29, 2013 at 21:00

ic3b3rg

15k4 gold badges33 silver badges55 bronze badges

1 Comment

ic3b3rg Over a year ago

@Colin'tHart I'm no Oracle expert, but what I've read on the web is that String functions can be used on Long fields within PLSQL. I've tested this function and it does work. I also found a low-level method in .NET to determine which data_default fields of user_tab_columns are not null, so I only need to use this function on those fields.

Collectives™ on Stack Overflow

Oracle column default value in .NET

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related