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

Brought to you by: atuining

cx-oracle-users

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

From: Antonio B. M. <ant...@li...> - 2004-02-26 12:06:50

Hi all:
I've a big problem because I neet to get the nextval in a sequence, and t=
he=20
query returns an empty sequence.=20
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?

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

From: Anthony T. <an...@co...> - 2004-02-26 15:04:14

Could you send us the exact code you are using? This code works for me:

import cx_Oracle

connection = cx_Oracle.connect("user/pw@tns")
cursor = connection.cursor()
cursor.execute("select users_seq.nextval from dual")
seqValue, = cursor.fetchone()

On Thu, 2004-02-26 at 04:56, Antonio Beamud Montero wrote:
> 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=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

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

From: Antonio B. M. <ant...@li...> - 2004-02-26 16:13:42

query =3D "SELECT allocation_seq.nextval FROM allocation"
self._cursor.execute(query)
newid, =3D self._cursor.fetchone()
print newid

But it returns nothing...=20
If I do:
self._cursor.execute('select * from user_sequences')
r =3D self._cursor.fetchall()
print r
-------------------
[('ALLOCATION_SEQ', 1, 999999999999999999999999999L, 1, 'N', 'N', 20, 81)=
]
-------------------

El Jue 26 Feb 2004 15:54, Anthony Tuininga escribi=F3:
> Could you send us the exact code you are using? This code works for me:
>
> import cx_Oracle
>
> connection =3D cx_Oracle.connect("user/pw@tns")
> cursor =3D connection.cursor()
> cursor.execute("select users_seq.nextval from dual")
> seqValue, =3D cursor.fetchone()
>
> On Thu, 2004-02-26 at 04:56, Antonio Beamud Montero wrote:
> > Hi all:
> > I've a big problem because I neet to get the nextval in a sequence, a=
nd
> > the query returns an empty sequence.
> > If I make a query like : INSERT INTO XXXX VALUES (users_seq.nextval, =
=2E..)
> > 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=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

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

From: Jon a. C. B. <jc....@bi...> - 2004-02-27 07:57:18

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

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

From: Anthony T. <an...@co...> - 2004-02-27 15:10:06

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

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

From: Antonio B. M. <ant...@li...> - 2004-02-27 15:16:42

Selecting from dual has solved my problem.=20
With this is sufficient... :-)
A lot of thanks.

El Vie 27 Feb 2004 15:59, Anthony Tuininga escribi=F3:
> 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 i=
f
> 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 y=
ou
> > 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, a=
nd
> > the query returns an empty sequence.
> > If I make a query like : INSERT INTO XXXX VALUES (users_seq.nextval, =
=2E..)
> > 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=3Dick
> > _______________________________________________
> > 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=3D1356&alloc_id=3D3438&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users