Re: Sequences

crd_at_cs.odu.edu (CRAIG READ DUNCAN) wrote:

>I'm using sqlldr to bring a text database into Oracle7.3.3 (Solaris 2.51).
>If entries are introduced to the Oracle database (not with sqlldr),
>sequences create primary and foreign keys. This is done via a gui (O. forms).
 

The sequences only provide values for the keys. They don't actually create them.

>With sqlldr, I have to "force-feed" these keys. The entries are then visable
>from the sqlplus command line, but not through the gui.

The key values are populated via a trigger or a procedure in your form. You can also populate these on a pre-insert trigger at the database level. This way SQL*Loader or any other mechanism would also populate these values.

>I have been advised to drop the sequences, then recreate them. I believe I
>need to see the sequence "codes" before I drop them so I know how to recreate
>them.

Dropping the sequences isn't the problem. The problem is the contraints on the primary and foriegn keys.

You can't see the code of the sequences, but as with all objects within an Oracle database, they do appear in the all_objects view. You can also see what the next value of the sequence is by entering the following in SQL*Plus

         Select (sequence_name).nextval from dual;

The DDL syntax to create a sequence is

       Create sequence (sequence_name) increment by (an integer value)

There are optional parameters that specify a maxvalue, minvalue, wether the numbers are cycled, and how many sequence values to cache within the SGA, and wether to provide them in order.

>Does this sound right? How does one "see" a squence (or the definition of
>one?)

Probably the simplest solution would be:

1. If the columns are mandatory, that is defined as not null then alter the table to allow null values in the columns end if;
2. alter the table to remove the primary and foriegn key constraints
3. load the new records
4. re-create the constraints and NOT NULL attributes of the columns