No no, thanks for the clarification. I didn't set up the sequences when I was working with Oracle. I'm always happy to know more.
Good Fortune, Rick Walters Webmaster, Davita Laboratory Services Office: (800) 604-5227 Cell: (407) 491-9848 -----Original Message----- From: Kreig Zimmerman [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 11:26 AM To: CF-Talk Subject: Re: CF-Talk-list V1 #141 > ------------------------------ > Date: Fri, 15 Feb 2002 10:47:47 -0500 > From: Rick Walters <[EMAIL PROTECTED]> > [snip] > > Oracle has a nifty little table called DUAL. It's a system table that will > always have only one row. So, if you "Select nextval from Dual" you will > have a unique id you can then store in a variable and use for inserts. If > you use this for all your tables, then your ID values will be different in > all of your tables, an interesting thing to play with in joins or table > merges. No record will ever have the same ID. Beware, I have heard of > resetting the Dual Table. Never had it happen to me. This is completely off-base. Instead of using the "Identity" feature which SQL Server has (which is a counter attached to a particular column on a table in the DB), Oracle has separate objects called Sequences. These are basically counter objects with extreme flexibility. Someone (usually a DBA, but it's trivial to do so) must set up a sequence for you to use before you can actually do something like: SELECT thisSq.NEXTVAL FROM SYS.DUAL. These sequences do not magically appear. Having the new row-ids selected explicitly from a separate table avoids the potential ID contention issue which SQL Server has due to the fact the IDs are implicit, and a long data insert sometimes causes SQL Server to give two requests the same ID. (One way to avoid that is insert a NULL row first, get the ID, and then do an update on that row). There is nothing special about the so-called DUAL table. It's simply a dummy table. You could just as easily select the next value in a sequence from any table (or the sysdate for that matter, as in SELECT sysdate FROM SYS.DUAL). Doing it from DUAL makes more sense since it is ever-present (and locks you into using Oracle, hahaha :). No offense Rick. I just wanted to ensure people knew what exactly the deal was with Sequences in Oracle. Cheers, Kreig FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ ______________________________________________________________________ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
