RE: oracle returning sequesnce number

Paul McCollum Fri, 21 Jan 2005 07:51:41 -0800

programmatically that's pretty much it, once you select sequence.nextval, the value is incremented.. you can create a database trigger that does the work for you though. so that it functions just like sql server. on insert "update mytable.currentrecord set mytable.pkfield = (select sequence.nextval from dual)" again this is pseudocode google for proper syntax. i like to do it this way to take programming load off when i don't need the key and to make it harder for someone to screw up the pk later.

but then you are stuck with the same amount of work[1 query] to get the index back with sequence.currval. if there is a better way that can be done without multiple sql statements i hope someone posts it.

----Original Message Follows----
From: "Schreck, Tom" <[EMAIL PROTECTED]>
Reply-To: [email protected]
To: Paul McCollum <[EMAIL PROTECTED]>
CC: [email protected]
Subject: RE: oracle returning sequesnce number
Date: Fri, 21 Jan 2005 09:35:03 -0600

I'm using the following within a transaction:

                <cfquery name="qryGetSeq" datasource="#variables.dsn#">
                        SELECT [sequence].nextval as pkID
                        FROM DUAL
                </cfquery>
                <cfquery name="qrySQL" datasource="#variables.dsn#">
                        INSERT INTO table(Fields...)
                        VALUES(values...)
                </cfquery>

It seems to be working.  Is there a better way?

Thanks

Tom Schreck
972-361-9943

-----Original Message-----
From: Paul McCollum [mailto:[EMAIL PROTECTED]
Sent: Friday, January 21, 2005 9:31 AM
To: [EMAIL PROTECTED]
Subject: RE: oracle returning sequesnce number

i may get contradicted on the list shortly so i'm sending to you directly,
but as of version 8 which i was familiar with it doesn't really have one.
There is a way to emulate this behaviour in oracle but it can be messy.

if someone has gone as far to set up the triggers to auto increment a index
tied to a sequence which is the oracle hack for what's pretty
straightforward in sqlserver and access here's the answer.

so given all that the way to select it is "select sequence_name.currval from

user_sequences". currval and nextval are the keywords you're looking for i'm

not positive on the syntax but you should be able to google it from that
point. sorry i haven't done that for several years but that should get you
close enough to help yourself if someone hasn't gotten you there already.


if it's set up and working i can explain it all to you just ask.. but if
it's not then it's amount of work to get it there and i can point you in the

right direction to start that too.. lemme know..

HTH

paul

----Original Message Follows----
From: "Schreck, Tom" <[EMAIL PROTECTED]>
Reply-To: [email protected]
To: [email protected]
Subject: oracle returning sequesnce number
Date: Fri, 21 Jan 2005 09:09:39 -0600

What's Oracle's version of SQL Server's @@identity?

Thanks

Tom Schreck

Applications Developer

Dresser, Inc.

15455 Dallas Parkway, Suite 1100

Addison, TX  75001-4690

972-361-9943

[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>

----------------------------------------------------------
To post, send email to [email protected]
To unsubscribe:
   http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe:
   http://www.dfwcfug.org/form_MemberRegistration.cfm

---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm

RE: oracle returning sequesnce number

Reply via email to