OraFAQ Forum: SQL & PL/SQL » Can you use a sequence in a prepared statement?

Home » SQL & PL/SQL » SQL & PL/SQL » Can you use a sequence in a prepared statement? (10g, OTLv4, C++)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can you use a sequence in a prepared statement? [message #463356]

Wed, 30 June 2010 23:22

anielsen
Messages: 7
Registered: June 2010
Location: Brisbane, Australia

Junior Member

Hi all,

I'm having some difficulty getting a simple SQL statement to work. It uses bind variables, but has a problem when I introduce a sequence:

INSERT INTO AE_MASTER.STAGE_USAGE (STAGE_USAGE_ID, ANIMAL_USAGE_ID_FK) VALUES (AE_MASTER.AE_GENERIC_SEQ.NEXTVAL, :dummy)

==> ORA-00942: table or view does not exist

But if I remove the sequence, it works:

INSERT INTO AE_MASTER.STAGE_USAGE (STAGE_USAGE_ID, ANIMAL_USAGE_ID_FK) VALUES (1, :dummy)

==> Success

So you might think there is a problem with the sequence, but if I use a name that doesn't exist, it tells me so:

INSERT INTO AE_MASTER.STAGE_USAGE (STAGE_USAGE_ID, ANIMAL_USAGE_ID_FK) VALUES (xxxAE_MASTER.AE_GENERIC_SEQ.NEXTVAL, :dummy)

==> ORA-02289: sequence does not exist

So I'm a bit stumped as to why it's not working! Is it not possible to use sequences with bind variables?

In the past I have gotten around this by using a SELECT statement to get the sequence number then following it with the INSERT statement, but this time I'd really like to get it all done in the same statement if possible.

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463357 is a reply to message #463356]

Wed, 30 June 2010 23:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

EXACTLY how can I reproduce what you report?

Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463359 is a reply to message #463357]

Wed, 30 June 2010 23:28

anielsen
Messages: 7
Registered: June 2010
Location: Brisbane, Australia

Junior Member

Sorry I wasn't asking you to reproduce my problem, I just want to know if that sort of statement can be expected to work! I can't find anything much on the web about using sequences either in other schemas or alongside bind variables, so I was hoping someone here may have done this before and can let me know if it is allowed or not.

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463366 is a reply to message #463356]

Thu, 01 July 2010 00:40

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Although it is not clear from your essay, when it was (not) working, I would firstly focus on the documentation. It is available e.g. online on http://tahiti.oracle.com/

Chapter about sequence pseudocolumns in SQL Reference book is placed here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns002.htm
The most probable reason may be stated here: Quote:

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege

Also bear in mind, that when used in procedure, the grant has to be given directly to the user. It is not sufficient to grant it through the role. See e.g.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1065832643319
http://www.orafaq.com/forum/t/158439/0/

[Edit: documentation link changed to the chapter start]

[Updated on: Thu, 01 July 2010 00:43]

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463373 is a reply to message #463366]

Thu, 01 July 2010 01:00

anielsen
Messages: 7
Registered: June 2010
Location: Brisbane, Australia

Junior Member

Thanks for the pointers! I didn't realise there was that level of official documentation, I find Oracle's website very cumbersome to navigate and have been relying on Google.

I had granted correct permissions, but that document again didn't use a schema prefix on the sequence name, which nobody seems to do. So I tried removing it, to see what error I got (since I had tried it before and it didn't work.) But lo and behold it actually worked this time! So I don't know what the problem was earlier (must've been caused by something else) but the solution is not to specify the schema with a sequence.

So in my code above, I replaced AE_MASTER.AE_GENERIC_SEQ.NEXTVAL with AE_GENERIC_SEQ.NEXTVAL to get it working.

Makes me wonder how you would access a sequence in another schema if you really wanted to...

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463377 is a reply to message #463356]

Thu, 01 July 2010 01:31

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

SQL> conn scott/tiger
Connected.
SQL> create sequence seq start with 1;

Sequence created.


SQL> select seq.nextval from dual;

   NEXTVAL                                                                      
----------                                                                      
         1                                                                      


SQL> create table t601 as select 1 a from dual;

Table created.


SQL> insert into t601 values(seq.nextval);

1 row created.


SQL> select * from t601;

         A                                                                      
----------                                                                      
         1                                                                      
         2                                                                      


SQL> conn hr/hr
Connected.
SQL> create table y601 as select 1 a from dual;

Table created.

Elapsed: 00:00:00.01
SQL> select scott.seq.nextval from dual;
select scott.seq.nextval from dual
             *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> ----------------

SQL> conn scott/tiger
Connected.
SQL> grant select on seq to hr;

Grant succeeded.

SQL> --switching to hr
SQL> conn hr/hr
Connected.
SQL> select scott.seq.nextval from dual;

   NEXTVAL                                                                      
----------                                                                      
         3                                                                      


SQL> insert into y601 values(scott.seq.nextval);

1 row created.

Elapsed: 00:00:00.06
SQL> select * from y601;

         A                                                                      
----------                                                                      
         1                                                                      
         4

You might have sufficient privilege on the tables but not the select privilege for the sequence.

Regards
Ved

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463382 is a reply to message #463377]

Thu, 01 July 2010 02:00

anielsen
Messages: 7
Registered: June 2010
Location: Brisbane, Australia

Junior Member

Aah, that sort of explains it. Except, what happens when you do this?

conn scott/tiger
insert into t601 values(scott.seq.nextval);

Because I was connecting as the same user who created the sequence in the first place, and it does work if I don't specifically list the schema. It's almost as if scott in your example has access to seq.nextval, but not scott.seq.nextval, even though those are different names for the same object.

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463389 is a reply to message #463382]

Thu, 01 July 2010 02:26

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

In own schema, it doesn't matter whether or not you use schema name before object name.

SQL> connect scott@dws
Enter password: *****
Connected.
SQL> create sequence seq start with 1;

Sequence created.

SQL> create table t601 as select 1 a from dual;

Table created.

SQL> insert into t601 values(seq.nextval);

1 row created.

SQL> insert into t601 values(scott.seq.nextval);

1 row created.

SQL> select * from t601;

         A
----------
         1
         1
         2

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

regards,
Delna

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463391 is a reply to message #463389]

Thu, 01 July 2010 02:32

anielsen
Messages: 7
Registered: June 2010
Location: Brisbane, Australia

Junior Member

Hmm, well I just tested this myself and I've definitely got access to it either way:

SQL> select ae_generic_seq.nextval from dual;

   NEXTVAL
----------
    148696

SQL> select ae_master.ae_generic_seq.nextval from dual;

   NEXTVAL
----------
    148697

So it must only be a problem when bind variables are involved...

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463414 is a reply to message #463391]

Thu, 01 July 2010 04:25

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Thanks for the feedback!

SQL> select * from y603 ;

      COL1       COL2                                                           
---------- ----------                                                           
         8          2                                                           
         7          1                                                           

Elapsed: 00:00:00.01
SQL> insert into y603(col1,col2) values(1,:dummy);
SP2-0552: Bind variable "DUMMY" not declared.
Elapsed: 00:00:00.00
SQL> insert into y603(col1,col2) values(1,&dummy);
Enter value for dummy: 3
old   1: insert into y603(col1,col2) values(1,&dummy)
new   1: insert into y603(col1,col2) values(1,3)

1 row created.

Elapsed: 00:00:00.00
SQL> select * from y603;

      COL1       COL2                                                           
---------- ----------                                                           
         8          2                                                           
         1          3                                                           
         7          1

ofcourse you can do this in pl/sql.e.g,

declare
  stmt constant varchar2(52) := 'insert into z603 values(:bind_var_1, :bind_var_2)';
begin
  execute immediate stmt using 4, 0;
  execute immediate stmt using 5, 0;
  execute immediate stmt using 6, 0;
end;
/

Regards
Ved

[Updated on: Thu, 01 July 2010 04:46]

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463561 is a reply to message #463414]

Thu, 01 July 2010 20:55

anielsen
Messages: 7
Registered: June 2010
Location: Brisbane, Australia

Junior Member

Thanks for the info. What happens if you use a bind variable *and* a sequence? Because that was where I was having problems. The sequence on its own works, the bind variables work, but I can't hard-code a sequence into the statement alongside bind variables, e.g. "INSERT INTO ... VALUES (blah.NEXTVAL, :dummy)"

EDIT: Just discovered that the "fix" above (removing the schema name from the sequence) doesn't actually work. It doesn't produce an error, but it also doesn't insert any rows into the table either Sad

[Updated on: Thu, 01 July 2010 21:10]

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463562 is a reply to message #463561]

Thu, 01 July 2010 21:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It works for me

19:06:12 SQL> connect hr/oracle
Connected.
19:06:23 SQL> create table seq_bind (id1 number, id2 number);

Table created.


19:11:38 SQL> @/tmp/bind.sql
19:12:34 SQL> declare
19:12:34   2  	stmt constant varchar2(252) := 'insert into seq_bind values(:bind_var_1, :bind_var_2)';
19:12:34   3  begin
19:12:34   4  	execute immediate stmt using employees_seq.nextval, 0;
19:12:34   5  	execute immediate stmt using locations_seq.nextval, 1;
19:12:34   6  end;
19:12:34   7  /

PL/SQL procedure successfully completed.

19:12:34 SQL>

Report message to a moderator

Re: Can you use a sequence in a prepared statement? [message #463563 is a reply to message #463562]

Thu, 01 July 2010 21:36

anielsen
Messages: 7
Registered: June 2010
Location: Brisbane, Australia

Junior Member

Gah, ok after trying to produce an example to demonstrate the issue I discovered it was my fault after all. As Its_me_ved pointed out, you (of course) need enough permission to the sequence to select it. Which I thought I did. But it turns out my code was connecting under a different account to the one I expected, and that account didn't have enough access. Connecting as the correct user fixed the problem :-/

So thank you everyone for all your assistance, you were all very helpful and I really appreciate it!

Report message to a moderator

Previous Topic:	RANDOM NUMBER GENERATE
Next Topic:	Accessing v$mystat gives table or view does not exist

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 29 19:19:18 CDT 2025