Re: Sqlplus question

From: DanHW <danhw_at_aol.com>
Date: 18 Jun 1998 02:31:17 GMT
Message-ID: <1998061802311700.WAA10742_at_ladder01.news.aol.com>


>Is it possible to make a sqlplus query for the nextval of a sequence in one
>query, store the result in a variable, and use the variable in a subsequent
>sqlplus query? I don't want to have to use pl/sql to do this, only sqlplus.
>If this is possible, please enlighten me as to how I can go about it. Thanks.
>
>

For any kind of value returned from a SQLPLUS query, you can save the result and use it in a later statement. Here's how:

column my_col new_value temp_val
select seq.nextval my_col from dual;

(There is now a sqlplus 'variable' named temp_val, created by looking at the value returned by the column alias my_col, that can be referenced by '&')

insert into tab1 (col1) values (&temp_val);

(this will insert a row that has col1= the sequence # generated)

The secret is the new_value part of the column definition; I don't have the manual handy, so I may be mistaken about the syntax. I seem to remember that the manual was very cryptic in regards to this, but it does work.

Dan Received on Thu Jun 18 1998 - 04:31:17 CEST

Original text of this message