CODEWITHSUNDEEP
c#oracle-databaseodp.net
masoud vali
masoud vali

Reputation: 1536

Oracle DATE type column default value don't return by ODP in c#

I'm connecting to oracle database via c# and Managed OPD. I get all table columns but I cannot get the default value for DATE type columns. I should note that when i run the same query in Oracle SQL Developer, it shows the default value for the specific column.

my code:

    public DataTable SelectIntoTable(DbCommand cmd, params object[] values)
    {
        if (!SetParamsValues(cmd, values))
            return null;

        DataSet dataSet = new DataSet();
        DataTable table = new DataTable();
        DbDataAdapter adapter = MakeDataAdapter();
        adapter.AcceptChangesDuringFill = false;
        adapter.SelectCommand = cmd;
        LastException = null;
        try
        {
            if (Type == DataSource.DBSourceType.ORACLE)
            {
                adapter.Fill(dataSet);
                table = dataSet.Tables[0];
            }
            else
            {
                adapter.Fill(table);
            }
        }
        catch (Exception exp)
        {
            OnErrorOccured(exp, cmd);
            table = null;
        }

        return table;
    }

my query:

SELECT COLUMN_ID, COLUMN_NAME, TABLE_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, IDENTITY_COLUMN
                FROM user_tab_columns WHERE TABLE_NAME IN ('GPS') ORDER BY TABLE_NAME ASC, COLUMN_ID ASC;

Upvotes: 0

Views: 351

Answers (2)

masoud vali
masoud vali

Reputation: 1536

DATA_DEFAULT column is LONG type which in ODP its length is 0 as default, We should change this by this command(change the InitialLONGFetchSize value to anything except 0):

cmd.InitialLONGFetchSize = -1;

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59485

Data type of DATA_DEFAULT is LONG which is always difficult to handle since it is deprecated for ages and hardly supported by any function.

You can write a function like this to get the value. However, you would write several functions returning different data types and you would call them based on value of DATA_TYPE

CREATE OR REPLACE FUNCTION DATE_Default(tabName IN VARCHAR2, colName IN VARCHAR2) RETURN DATE AS 
    res DATE;
BEGIN

    FOR aVal IN (SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = tabName AND COLUMN_NAME = colName) LOOP
        EXECUTE IMMEDIATE 'BEGIN :res := '||aVal.DATA_DEFAULT||'; END;' USING OUT res;
    END LOOP;
    RETURN res;

END;

Upvotes: 1

Related Questions