Determining Oracle Column gets filled by sequence and trigger (Identity Column like SQLServer)

Question

I have to determine, if a column gets filled up automatically.

Mostly this is done at PrimaryKey Columns. This has to be done with Oracle database as well as with SQLServer.

The background of my question is (only for your better understanding), i'm reading a XML File where the specification says, the element tags match column names of a table. The table will be given by the user and he chooses the xml File i have to parse. After that i am looking for all the elements inside the xml file, if they are matching a column's name in the table.

It is not clear if the xml file will give me Primary Key information. It could be, but could also not be. If the column's name will be inside the xml all would be fine. But if not, i have to look up if

i have a PK Column given in the xml File and also
if that PK column is automatically filled up with values

if this is not given, i have to raise an exception. Therefor i have to determine that Schema Information.

With SQLServer, it is no problem. IsAutoIncrement will be true, if it is an Identity Column.

I read the table schema data like this (only short example)

System.Data.Common.DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
dtTable = reader.GetSchemaTable();

Now i'm getting all the information i want. Unfortunatelly with Oracle, the IsAutoIncrement property is set to false.

I know, how should GetSchemaTable() determine if the column is set by an sequence if it is not stated at the Column by the DBMS, as SQLServer does. That is clear to me.

Does anyone have a idea, how to solve this? Determining if a column in a table gets filled by a sequence inside a trigger? Maybe parsing the triggers body?

Thanks in advance for any help

My solution looks like this now:

            if (IsOracleClient)
            {
                // reading Trigger Information
                string sql = "select\n" +
                             "      a.trigger_body\n" +
                             "  from\n" +
                             "      all_triggers a,\n" +
                             "      all_trigger_cols b\n" +
                             "  where\n" +
                             "      a.table_name   = '{0}'\n" +
                             "  and b.table_name   = a.table_name\n" +
                             "  and b.column_name  = '{1}'\n" +
                             "  and a.trigger_name = b.trigger_name";

                command.CommandText = String.Format(sql, this.Tabelle.ToUpper(), this.PrimaryKeyColumn.ColumnName.ToUpper());
                using (System.Data.Common.DbDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        string body = reader.GetString(0);
                        if (body.Contains("NEXTVAL"))
                            this.PrimaryKeyColumn.IsAutoIncrement = true;
                    }
                    reader.Close();
                }
            }

score 2 · Accepted Answer · answered Sep 17 '13 at 07:17

Short Answer: You can't tell from schema info if a column is updated by a sequence.

Likely the version of Oracle you are using does not have a concept of Identity columns. The functionality is typically replicated via a sequence and a trigger on the table.

In Oracle DB, a sequence is not tied to a specific column. Any given sequence can be used to get a value to be inserted into any numeric column you want. That sequence could be used in many spots... Triggers, procedures, functions, separate application code, etc.

I think parsing the body of your triggers is an option. That's viable if you only care that a column is updated via a trigger though.

This is probably bad advice, but if you are certain of naming conventions in the db you could maybe make some assumptions, like that table and/or column names are referenced in the sequence name. Then again though there is that pesky 30 char name limit in Oracle that can easily shatter those assumptions.

i also thought like this. i now have thought about selecting all_trigger_cols and looking in Column_Name for my PK column. As i know, our Database Environment in oracle only uses triggers for setting PK information by sequence. There is no other usage. So i think, i could solve it in my case also like that. Unlikely i cannot up-vote you because of missing reputation :( — Dom84, Sep 17 '13 at 07:21
as my reputation has grown a little, here your deserved up-vote :) — Dom84, Dec 09 '13 at 16:16

Determining Oracle Column gets filled by sequence and trigger (Identity Column like SQLServer)

1 Answers1