CODEWITHSUNDEEP
.netoracle-database
ic3b3rg
ic3b3rg

Reputation: 14927

Oracle column default value in .NET

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

Upvotes: 0

Views: 649

Answers (2)

ic3b3rg
ic3b3rg

Reputation: 14927

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.

Upvotes: 0

Colin &#39;t Hart
Colin &#39;t Hart

Reputation: 7729

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.

Upvotes: 3

Related Questions