Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: query whether sequences exist

Re: query whether sequences exist

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 2 Mar 2006 06:59:29 -0500
Message-ID: <>

"Daniel Wetzler" <> wrote in message
: Dear Mark & Daniel,
: thank you very much for your replies.
: In my special case I'm interested in finding out whether a table-column
: is
: auto iterated or not. In MySQL databases we use the AUTO_INCREMENT
: attribute for that.
: As far as I understood (I'm new to Oracle) the same task is done by
: sequences. Did I understand this right ?
: In my application I get the running databases and have to compare them.
: So my
: problem is to find out whether a sequence for is used to count up a
: column or not.
: So if I understand your right I cannot perform that task. The problem
: with the solutions you discribed is that they cannot be automated
: within a script (what I have to do).
: Are there additionally ways to perform the automatically counting up of
: values within a column with Oracle besides sequences ?
: Best regards and many thanks,
: Daniel Wetzler

in oracle, using a sequence is the standard method, which is done:

[_] in a before-insert for-each-row trigger
[_] via a stored procedure or package that implements a table API
[_] via application code

for the first, you could look at the USER_TRIGGERS (or DBA_TRIGGERS) table to see if the appropriate type of trigger exists, but that does not guarantee that it uses a sequence -- you actually have to find a references to the table, sequence, and NEXTVAL keyword in code -- either in USER_TRIGGERS, which is tough since the code is stored in a LONG datatype or via USER_SOURCE (DBA_SOURCE) for packages, procedures, and (sometimes) triggers (at some point Oracle started to exposed trigger code via USER_SOURCE, but there are inconsistencies)

++ mcs Received on Thu Mar 02 2006 - 05:59:29 CST

Original text of this message