inserting record into table w/index sequence

[inncoggnitto]

i want to insert a record into a table that has a primary key that is autogenerated. i have used TOAD to generate the insert statement for the record and it includes the key but i want to create a completely new record to replace one that was lost during data migration.

i get this from toad for an existing record.
INSERT INTO RC_PAY_ITEM_DETAIL ( RCPAYDPK, RCPAYIPK, RCAMNDPK, RCTASKPK, PRPAYDPK, SEQ, QTY, AMT,
UNIT_PRICE, ADD_DESCRIPTION, MOD_DT, MOD_USR, TSTDATE ) VALUES (
342700, 212155, 17975, NULL, NULL, 0, 1, 1000, 1000, NULL, TO_TIMESTAMP('3/23/2006 6:43:57.334000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM')
, 'fpconvert', TO_TIMESTAMP('3/23/2006 6:43:57.334000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;

the first field is the index (primary key) field the other PKs are really foreign keys. if i want to insert a record and have the system generate the key what should i do?

 

[SantaMufasa]

Inncoggnitto,

Welcome, for the first time, to Oracle forums (after your long tenure here at Tek-Tips)!

i want to insert a record into a table that has a primary key that is autogenerated.

Oracle generates "autogenerated" values via a named SEQUENCE that you specify in the INSERT statement. Assuming that you (or someone/something) has already created an Oracle SEQUENCE by the name "RC_PAY_ITEM_DETAIL_SEQ", your manual INSERT statement might look like this:

INSERT INTO RC_PAY_ITEM_DETAIL ( [b]RC_PAY_ITEM_DETAIL_SEQ.nextval[/b], RCPAYIPK, RCAMNDPK, RCTASKPK, PRPAYDPK, SEQ, QTY, AMT,
UNIT_PRICE, ADD_DESCRIPTION, MOD_DT, MOD_USR, TSTDATE ) VALUES ( 
342700, 212155, 17975, NULL, NULL, 0, 1, 1000, 1000, NULL, TO_TIMESTAMP('3/23/2006 6:43:57.334000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM')
, 'fpconvert', TO_TIMESTAMP('3/23/2006 6:43:57.334000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));

Additionally, the use of "fm" in your date-format masks seems extremely convoluted. Each invocation of "fm" in a format mask causes a toggling of that feature. So, as an example from above, the mask, "'fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'" causes the feature to behave in this fashion: "<on>MM<off>/<on>DD<off>/YYYY <on>HH12<off>:MI:SS.FF AM". This behaviour is difficult to justify in my opinion.

Let us know, however, if the above answers your question.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[inncoggnitto]

thanks for your reply.

i checked the sequence generator using TOAD and i get it to be SYS_C003488 but when i try that in the sql statment i get an error stating "ORA-02289: sequence does not exist".

that is the name of the index shown by TOAD. apparently it is not the sequence name. how would one find out the correct sequence name?

thanks for the welcome too dave of sandy.

mike of evanston past here.

 

[SantaMufasa]

Mike,

The name "SYS_C003488" is the Oracle-generated name for the unique index that Oracle created by virtue of your declaring either a PRIMARY KEY or a UNIQUE constraint on one or more columns in your table.

You are correct that the Oracle SEQUENCE is, in fact, a different organism from the index. They are, however, related in that the SEQUENCE provides unique values that populate the column to which your unique index refers.

Oracle discloses information about its SEQUENCES in views such as USER_SEQUENCES, ALL_SEQUENCES, AND DBA_SEQUENCES. (The differences are that USER_... limits the sequences it lists to just the ones that the querying user owns; ALL_... limits the sequences to those to which the querying user can access; DBA_... list all SEQUENCES for all users in that Oracle instance.)

You can see the available columns for those sequences by doing a SQL*Plus (or TOAD) "describe <object_name>"

Let us know if you have follow-up questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[inncoggnitto]

hmmm, i found the sequences tab but it shows only one and it is not associated with any of the tables but an unassociated one that uses a batch job that runs an automatic update at night.

it's lastval is 17890, whereas the id in the table i need to use is 250000+.

i have a call into the contractors that built the db and interface but most have moved on and finding one w/knowledge is a difficult task.

 

[SantaMufasa]

Inncoggnitto, Under those circumstances, there are the possibilities that I see that the developers could have implemented for the sequence:

1) The developers store the sequence source value in some user-defined, application-based (i.e., non-Oracle-sequence based) column in some user-defined table, or

2) Some other Oracle user/schema owns the SEQUENCE that is the source of unique values.

You can do some detective work (without the original developers) to locate the source of the unique-identifier values if you have access to the source code that includes the INSERT statement that sets up new records...Just read that code and see what it is using as a numeric resource for that unique value.

Please do let us know what you find.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[002siya]

INSERT INTO RC_PAY_ITEM_DETAIL (RCPAYDPK, RCPAYIPK, RCAMNDPK, RCTASKPK, PRPAYDPK, SEQ, QTY, AMT,
UNIT_PRICE, ADD_DESCRIPTION, MOD_DT, MOD_USR, TSTDATE ) VALUES (
RC_PAY_ITEM_DETAIL_SEQ.nextval, 212155, 17975, NULL, NULL, 0, 1, 1000, 1000, NULL, TO_TIMESTAMP('3/23/2006 6:43:57.334000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM')
, 'fpconvert', TO_TIMESTAMP('3/23/2006 6:43:57.334000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));

 

[inncoggnitto]

thanks, that will help me out and keep the dba's happy too.