Re: [cx-oracle-users] Can't get nextval

[Anthony T. <an...@co...>]

It isn't strictly necessary to select from dual when you are fetching a
sequence value -- but it is the "accepted" way of doing so since dual
always has one row. Have you tried selecting from dual and does that
solve the problem? It is certainly not necessary to create a stored
procedure to do this -- you can do this in two separate SQL statement if
you wish or as an anonymous PL/SQL block as well. A stored procedure
simply encapsulates the logic in one place and that might be
advantageous to you. If you want to proceed further, I would suggest
sending us the exact code that you are using and the exact output --
using the interactive command line is ideal if you can do it. Thanks.

On Fri, 2004-02-27 at 00:46, Jon and Chiarina Blake wrote:
> Hi Antonio,
>   If you want to treat your sequence like a "table", you must "SELECT
> users_seq.nextval FROM DUAL". You'll see the next sequence value if you
> execute this statement from an SQL*Plus prompt.
> 
> What you seem to be attempting to do, only makes sence from a PL/SQL block.
> Try something like:
> 
> CREATE OR REPLACE PROCEDURE myproc IS
> 
>   nval NUMBER;
> 
> BEGIN
>   SELECT users_seq.nextval INTO nval FROM DUAL;
>   INSERT INTO xxx VALUES(nval,...);
>   ...
> END;
> 
> Then use cx_Oracle's callproc method to execute procedure myproc.
> Hope this helps.
> 
> Regards,
>   Jon
> 
> ----- Original Message ----- 
> From: "Antonio Beamud Montero" <ant...@li...>
> To: <cx-...@li...>
> Sent: Thursday, February 26, 2004 10:56 PM
> Subject: [cx-oracle-users] Can't get nextval
> 
> 
> Hi all:
> I've a big problem because I neet to get the nextval in a sequence, and the
> query returns an empty sequence.
> If I make a query like : INSERT INTO XXXX VALUES (users_seq.nextval, ...)
> All works perfectly, but If I try to do this query in two steps:
> SELECT users_seq.nextval FROM XXXX
> INSERT INTO XXXX VALUES (new_id, ...)
> 
> the SELECT returns [].
> (This query works with sqlplus)
> 
> What I'm doin wrong?
> 
> 
> -------------------------------------------------------
> SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> Build and deploy apps & Web services for Linux with
> a free DVD software kit from IBM. Click Now!
> http://ads.osdn.com/?ad_id56&alloc_id438&op=ick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 
> 
> 
> 
> -------------------------------------------------------
> SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> Build and deploy apps & Web services for Linux with
> a free DVD software kit from IBM. Click Now!
> http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com