All, I have just determined that this will not work. Refer to the following article from oracle.
************************************************************* This article is being delivered in Draft form and may contain errors. Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article. ************************************************************* Problem Description ------------------- You get ORA-14551 when selecting a PL/SQL or Java Stored Procedure from dual. i.e. SELECT myFunction INTO myVar FROM DUAL; Myfunction looks like this create or replace function myFunction return varchar2 as begin update emp set empno = empno +1 where empno = 0; return 'Yeah'; end; / Solution Description -------------------- You need to use the syntax: var myVar NUMBER call myFunction() INTO :myVar; -- Paranthesis are must in the above call. Explanation ----------- The error is from the restriction of doing a DML from within a select statement. The function myFunction has within its code some form of DML, (INSERT, UPDATE or DELETE). Functions may only do selects and still be callable from DUAL or used in any form of select. The error ORA-14551 is new in 8i databases and may have resulted in ORA-6571 in prior versions. Thanks, Troy Troy Simpson wrote: > Freddy, > > I am assuming that most Oracle Functions should be designed to return one value. > If this is true then you could get that value like any other query. > > <cfquery name="qry"> > Select Max(pk) from tbl > </cfquery> > > So in this case, if the Maximum value in the pk column of the table tbl is 1000 then >the statement > <cfoutput>#qry.id#</cfoutput> > would print 1000. > > If my assumptions are correct, this should work? > > Sincerely, > Troy > > freddy wrote: > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
