I would use a stored procedure. Only because I have yet to learn how to connect to a function in oracle via cf. If this cane be done please let me know what syntax to use.
Thanks, Frederic Troy Simpson wrote: > Freddy, > > I believe that using a stored procedure might be the way to go with this. > Should I use a Procedure or a Function though? > > I was thinking that if I used a function, I could call it like this in a <CFQUERY> >statement: > > <cfquery name="qryPK" datasource = "DSN"> > SELECT > Add_Record( "My Name" ) AS PrimaryKey > FROM > dual > </cfquery> > > Or would using a procedure with IN OUT variables be the better route? > > Thanks, > Troy > > freddy wrote: > > > You could try: > > <cftransaction action="BEGIN"> > > <cfquery name="qryCurrval" datasource = "DSN"> > > SELECT > > tbl_seq.nextval as tbl_currval > > FROM > > dual > > </cfquery> > > > > <cfquery name="qryInsert" datasource="DSN"> > > Insert Into tbl( name ) > > values( 'My Name' ) > > </cfquery> > > </cftransaction> > > > > I am not sure if this would work but it seems that it would get the nextval prior >to it being assingned using the > > trigger. > > > > Alternately couldn't you also use a stored procedure to insert the the name and >return the curval? > > > > Thanks, > > Frederic > > > > Troy Simpson wrote: > > > > > Dave, > > > > > > Thanks for bring the CURRVAL pseudocolumn to light. > > > I forgot to mention this part. > > > I used the NEXTVAL pseudocolumn in the first <CFQUERY> INSERT statement as > > > follows: > > > > > > <cfquery name="qryInsert" datasource="DSN"> > > > Insert Into tbl( name ) > > > values( 'My Name' ) > > > </cfquery> > > > > > > I then use another <CFQUERY> to get the value in the CURRVAL pseudocolumn as > > > follow <cfquery name="qryCurrval" datasource = "DSN"> > > > SELECT > > > tbl_seq.currval as tbl_currval > > > FROM > > > dual > > > </cfquery>s: > > > > > > > > > I get the following Oracle error: > > > > > > ORA-08002: sequence TBL_SEQ.CURRVAL is not yet defined in this session > > > > > > According to the documentation it say this (Pay close attention to the last > > > sentence): > > > > > > When you create a sequence, you can define its initial value and the > > > increment between its values. The first reference to NEXTVAL returns the > > > sequence's initial value. Subsequent references to NEXTVAL increment the > > > sequence value by the defined increment and return the new value. Any > > > reference to CURRVAL always returns the sequence's current value, which is > > > the value returned by the last reference to NEXTVAL. Note that before you > > > use CURRVAL for a sequence in your session, you must first initialize the > > > sequence with NEXTVAL. > > > > > > According to the last sentence, I can only assume that the reason CURRVAL is > > > not available is because each <CFQUERY> statement creates a new Oracle > > > Session. Therefore CURRVAL is never available. Also since it is possible > > > that ColdFusion maintains persistant connects for all web requests, this > > > might introduce problems. > > > > > > I thought that I might be able to put two statements into one <CFQUERY>. > > > I have not tried this, but I would assume that it is not possible. > > > > > > Any other ideas? > > > Thanks for your input. > > > > > > Sincerely, > > > Troy > > > > > > Dave Carabetta wrote: > > > > > > > Correct me if I'm wrong, but you should be able to get the current value > > > > by using currval. For example, from your post, your sequence name is > > > > tbl_seq. Therefore, you would just type "tbl_seq.currval" to get the > > > > current value, which should also be the max id value. This is one of > > > > those really nice feature of Oracle over SQL Server. > > > > > > > > Hope this helps, > > > > Dave. > > > > > > > > -----Original Message----- > > > > From: Troy Simpson [mailto:[EMAIL PROTECTED]] > > > > Sent: Thursday, December 06, 2001 06:12 PM > > > > To: CF-Talk > > > > Subject: Get PK value from Oracle table with AutoNumber Trigger (tds)? > > > > > > > > All, > > > > > > > > I have read the the various postings about using the MAX() function to > > > > get the last value entered into the Primary Key field. > > > > This will not do for me as you will see. > > > > Plus I believe it is proned to errors. > > > > > > > > I have a table that is defined as follows: > > > > create table tbl ( pk number(11) not null primary key, name varchar2(20) > > > > ) > > > > > > > > I have created a sequences as follows: > > > > create sequence tbl_seq; > > > > > > > > The table has a trigger on the PK column for autonumbering and it is > > > > defined as follows: > > > > create trigger tbl_autonumber before insert on tbl for each row > > > > begin > > > > select tbl_seq.nextval into :NEW.pk from dual; > > > > end; > > > > > > > > After four people have inserted four seperate new records at the same > > > > time, how can I find out the Primary Key that was assigned to my new > > > > record? > > > > > > > > Here is a possible solution that I was thinking about but not sure if it > > > > is the optimal solution. > > > > Add an additional column to the table and use it as a stamp. > > > > When I insert the new record I would put a unique number into this > > > > column so that I can find the record in my next SELECT query. > > > > > > > > Ideally, I would like the INSERT statement to return the Primary Key > > > > value to me. > > > > Maybe an Oracle procedure or Oracle function would be the way to go. > > > > > > > > Sincerely, > > > > Troy > > > > > > > > -- > > > > Troy Simpson | North Carolina State University > > > > NCSU Libraries | Campus Box 7111 | Raleigh | North Carolina > > > > ph.919.515.3855 | fax.919.513.3330 > > > > > > > > It is better to be hated for what you are > > > > than to be loved for what you are not. > > > > -- Andre Gide > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
