Re: sequences

--0__=THJTprQvPoxksn3BKz4TzpS1sBdbEk46RQnxjv5leKLkknjFQtSb6vkt
Content-type: text/plain; charset=us-ascii Content-Disposition: inline

I use sequences to insert unique keys, to get the next value of the sequence typically I use it within PL/SQL scripts as follows

select sequence_name.nextval
into a_variable
from dual;

you can use it within triggers (which is basically a PL/SQL block) but I have heard that the use of sequences
within triggers is discouraged , this came form a source at Oracle , perhaps some of you other listers could confirm if it's still a bad idea in 8i ??

hope this helps
Brian.

Glenn Travis <Glenn.Travis_at_wcom.com> on 07/12/2000 12:03:36 PM

To: "Oracledba_at_Lazydba. Com" <oracledba_at_lazydba.com>, "Oracledba_at_Quickdoc. Co.

      Uk" <oracledba_at_quickdoc.co.uk>, ORACLE-L_at_fatcity.com cc: (bcc: Brian McQuillan/GELCO)

Subject: sequences

I wanted to ask how people are handling the insertion of unique keys in Oracle. Please understand that my experience with generating unique keys comes from an Informix background, wherein you can have a column with datatype of 'serial'. This is an integer column which gets generated at insert time with the next value automatically. You can then check the sqlca area (return buffer) for the inserted value.

I know in Oracle you can accomplish this with the SEQUENCE function. As this is not automatically inserted by Oracle, how is the unqiue key value inserted? Do most people use a before trigger, stored procs, or call the sequence themselves? If using a client program (c, java), how is the value for the unique key returned to the program which performed the insert?

Examples, methods, design recommendations welcome...

--0__=THJTprQvPoxksn3BKz4TzpS1sBdbEk46RQnxjv5leKLkknjFQtSb6vkt
Content-type: text/html;

        name="att1.htm"

Content-Disposition: attachment; filename="att1.htm"
Content-transfer-encoding: base64
Content-Description: Internet HTML

PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDQuMCBUcmFuc2l0aW9uYWwv L0VOIj4NCjxIVE1MPjxIRUFEPg0KPE1FVEEgY29udGVudD0idGV4dC9odG1sOyBjaGFyc2V0PWlz by04ODU5LTEiIGh0dHAtZXF1aXY9Q29udGVudC1UeXBlPg0KPE1FVEEgY29udGVudD0iTVNIVE1M IDUuMDAuMjkxOS42MzA3IiBuYW1lPUdFTkVSQVRPUj48L0hFQUQ+DQo8Qk9EWSBiZ0NvbG9yPSNm ZmZmZmY+DQo8RElWPjxGT05UIGZhY2U9QXJpYWwgc2l6ZT0yPjxTUEFOIGNsYXNzPTc3MDA3NTYx NS0xMjA3MjAwMD5JIHdhbnRlZCB0byBhc2sgaG93IA0KcGVvcGxlIGFyZSBoYW5kbGluZyB0aGUg aW5zZXJ0aW9uIG9mIHVuaXF1ZSBrZXlzIGluIE9yYWNsZS4mbmJzcDsgUGxlYXNlIA0KdW5kZXJz dGFuZCB0aGF0IG15IGV4cGVyaWVuY2Ugd2l0aCBnZW5lcmF0aW5nIHVuaXF1ZSBrZXlzIGNvbWVz IGZyb20gYW4gSW5mb3JtaXggDQpiYWNrZ3JvdW5kLCB3aGVyZWluIHlvdSBjYW4gaGF2ZSBhIGNv bHVtbiB3aXRoIGRhdGF0eXBlIG9mICdzZXJpYWwnLiZuYnNwOyBUaGlzIA0KaXMgYW4gaW50ZWdl ciBjb2x1bW4gd2hpY2ggZ2V0cyBnZW5lcmF0ZWQgYXQgaW5zZXJ0IHRpbWUgd2l0aCB0aGUgbmV4 dCB2YWx1ZSANCmF1dG9tYXRpY2FsbHkuJm5ic3A7IFlvdSBjYW4gdGhlbiBjaGVjayB0aGUgc3Fs Y2EgYXJlYSAocmV0dXJuIGJ1ZmZlcikgZm9yIHRoZSANCmluc2VydGVkIHZhbHVlLjwvU1BBTj48 L0ZPTlQ+PC9ESVY+DQo8RElWPjxGT05UIGZhY2U9QXJpYWwgc2l6ZT0yPjxTUEFOIA0KY2xhc3M9 NzcwMDc1NjE1LTEyMDcyMDAwPjwvU1BBTj48L0ZPTlQ+Jm5ic3A7PC9ESVY+DQo8RElWPjxGT05U IGZhY2U9QXJpYWwgc2l6ZT0yPjxTUEFOIGNsYXNzPTc3MDA3NTYxNS0xMjA3MjAwMD5JIGtub3cg aW4gT3JhY2xlIHlvdSANCmNhbiBhY2NvbXBsaXNoIHRoaXMgd2l0aCB0aGUgU0VRVUVOQ0UgZnVu Y3Rpb24uJm5ic3A7IEFzIHRoaXMgaXMgbm90IA0KYXV0b21hdGljYWxseSBpbnNlcnRlZCBieSBP cmFjbGUsIGhvdyBpcyB0aGUgdW5xaXVlIGtleSB2YWx1ZSBpbnNlcnRlZD8mbmJzcDsgRG8gDQpt b3N0IHBlb3BsZSB1c2UmbmJzcDthIGJlZm9yZSB0cmlnZ2VyLCBzdG9yZWQgcHJvY3MsIG9yIGNh bGwgdGhlIHNlcXVlbmNlIA0KdGhlbXNlbHZlcz8mbmJzcDsgSWYgdXNpbmcgYSBjbGllbnQgcHJv Z3JhbSAoYywgamF2YSksIGhvdyBpcyB0aGUgdmFsdWUgZm9yIHRoZSANCnVuaXF1ZSBrZXkgcmV0 dXJuZWQgdG8gdGhlIHByb2dyYW0gd2hpY2ggcGVyZm9ybWVkIHRoZSANCmluc2VydD88L1NQQU4+ PC9GT05UPjwvRElWPg0KPERJVj4mbmJzcDs8L0RJVj4NCjxESVY+PEZPTlQgZmFjZT1BcmlhbCBz aXplPTI+PFNQQU4gY2xhc3M9NzcwMDc1NjE1LTEyMDcyMDAwPkV4YW1wbGVzLCBtZXRob2RzLCAN CmRlc2lnbiByZWNvbW1lbmRhdGlvbnMgd2VsY29tZS4uLjwvU1BBTj48L0ZPTlQ+PC9ESVY+DQo8 RElWPiZuYnNwOzwvRElWPjwvQk9EWT48L0hUTUw+DQoNCg== Received on Wed Jul 12 2000 - 12:18:44 CDT