Oracle SQL*Plus Variable Declaration

Question

I can't for the life of me find out to get the behavior I want:

old:insert into build_pc values (&&buildA, 35,40,32,29,26,22,22,null,13,11,7,1,999) new:insert into build_pc values (build_id_seq.nextval, 35,40,32,29,26,22,22,null,13,11,7,1,999) 1 rows inserted.

As part of an SQL transaction I'm trying to save a variable, moreover an integer ID number, so that it can be entered in a few places across the transaction. The problem is SQLDev is being too clever and converting back to he nextval function each time instead of just storing an int.

Google says use set, but that throws this:

SQLPLUS Command Skipped: set buildA = build_id_seq.nextval

Alex Poole · Accepted Answer · 2015-04-16 15:24:25Z

3

As I understand, you want to use a bind variable in SQL Plus. Please, try this:

variable buildA NUMBER;
exec select build_id_seq.nextval into :buildA from dual;
insert into build_pc values (:buildA, 35,40,32,29,26,22,22,null,13,11,7,1,999);

If you use 11g you do not need to SELECT the value of sequence, you can use:

variable buildA NUMBER;
exec :buildA := build_id_seq.nextval;
insert into build_pc values (:buildA, 35,40,32,29,26,22,22,null,13,11,7,1,999);

Hope this helps!

edited Apr 16, 2015 at 15:24

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

answered Apr 16, 2015 at 15:13

Guneli

1,7311 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Linef4ult Over a year ago

I had no idea what we are using, but it appears to be 12c: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production. Your solution is exactly what I was looking for, but I'm getting some strange behaviour. When I create a second variable, buildB, in the same fashion, it throws undeclared, but after a few attempts starts working fine. Ive no idea whats changing.

Guneli Over a year ago

Hi. I am glad it helped. If it still works this strange way, maybe you can post your code with buildB, so I can have a look at the reasons of this behaviour.

Linef4ult Over a year ago

After further discussion with my lecturer (CS Undergrad) my approach was quite the way off. I'd misunderstood transactions, and as such its not surpising the code failed or behaved erratically. For what I asked though, your answer was great. Will mark answered. Thanks again.

Alex Poole · Accepted Answer · 2015-04-16 15:20:33Z

You should be using define rather than set for a substitution variable:

define buildA = build_id_seq.nextval

... but it will be substituted verbatim wherever you reference &buildA, incrementing the sequence each time.

You could use a bind variable or the new_value syntax, but you probably just want to refer to build_id_seq.nextval the first time you use it, and build_id_seq.currval on all the subsequent references, rather than trying to store it yourself.

As the documentation says:

CURRVAL: Returns the current value of a sequence

NEXTVAL: Increments the sequence and returns the next value

So when you call nextval the sequence is incremented, and subsequent calls to currval in the same session get the same value, without incrementing again.

This is useful for creating parent/child records, for example, where you use nextval to create the primary key value and then currval to use the same value as a foreign key in child records.

Collectives™ on Stack Overflow

Oracle SQL*Plus Variable Declaration

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related