Oracle works completely differently from SQL Server in this regard. There is no Identity type in Oracle. Instead, it has a mechanism called a sequence, which is a separate object that provides a sequence of single use values which are used to provide unique key values.
So, if you have a table "foo", you would create a sequence called, say, "foo_sq". You can define various parameters for your sequence - start value, increment value, etc. Now whenever you create a record in foo, you grab a value from foo_sq and use it as your PK id. Once that value is used, foo_sq will never provide it again. It's basically the equivalent of having a table of sequential numbers with a trigger that deletes a value from the table when it's selected. You can access the sequence in a couple of ways. One is using foo_sq.NextVal directly in the insert statement, as the original poster was doing. The other is to select it from DUAL, which is a sort of utility table in Oracle, like so: Select foo_sq.NextVal as id >From DUAL So id is your sequence value, and you can now pass it to your insert query to use as the PK id, as well as return it to the user, as the OP was trying to do. It's just one of those quirky Oracle things. Personally, I really like sequences, but if you're used to SQL Server, it's an odd beast. > -----Original Message----- > From: Doug Brown [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 05, 2006 3:46 PM > To: CF-Talk > Subject: Re: locking, I think > > Here is a little snippet that may help, of course since you are using > Oracle > the syntax would differ. > > Using Identity Keys in SQL Server > Most applications rely on primary keys in database tables. Many of those > same applications use the auto-increment capabilities of some DBMSes like > MSSQL Server. When inserting a record in a table that uses auto-increment > primary keys, you do not have to insert the value of the primary key > yourself - the DB does it for you. But what if you want to know the value > of > the row's primary key that you just inserted? Commonly, developers will > run > two queries in a row, wrapped in <cftransaction>. The first one inserts > the > record and the second one selects the newest record. But using this > syntax, > SQL server can insert the record as well as return the value of the new > primary key all in one query. > > <cfquery name="insert" datasource="DSN"> > set nocount on > insert into customer (firstName, lastName) > values > ('#attributes.firstName#', '#attributes.LastName#') > select @@identity as newID > set nocount off > </cfquery> > > I am the new primary key value: > <cfoutput>#insert.newID#</cfoutput> > > > > ----- Original Message ----- > From: "Daniel Kessler" <[EMAIL PROTECTED]> > To: "CF-Talk" <cf-talk@houseoffusion.com> > Sent: Thursday, October 05, 2006 12:01 PM > Subject: locking, I think > > > > I have an oracle database and I set the ID of a database entry using: > > unique_repair_ticket_s.NEXTVAL - which is basically a counter > > > > I then need to get that ID and send it to the user. Is the process > > for this to then query the database for the last entry? If so, do I > > surround it with a cftransaction or a cflock to inhibit others > > writing to it until that query is complete so that the last number is > > actually the number that I need? > > > > thank you. > > > > -- > > > > Daniel Kessler > > > > College of Health and Human Performance > > University of Maryland > > Suite 2387 Valley Drive > > College Park, MD 20742-2611 > > Phone: 301-405-2545 > > http://hhp.umd.edu > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255713 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
