Re: passing PL/SQL variables to SQL PLUS

eva.haiden_at_engnetworld.com (Eva Haiden) wrote in news:52046b21.0204021330.5011bd38_at_posting.google.com:

> Dear Oracle users,
>
> I am trying to receive information from the user via accept and
> prompt. Then I would like to insert this information into a table, but
> before I do that I have to find out the max value of the id field. How
> can I do this?
>
> verify off
> feedback off
> echo off
>
> accept firstName prompt 'Enter the first name: '
> accept lastName prompt 'Enter last name: '
>
> insert into names
> (id,
> first_name,
> last_name)
> values
> (select max(id) + 1 from names, -- does not work, how can I do this?
> &firstName,
> &lastName);
>
> We don't have and do not want to install a sequence on the id column.
>
> Please help!

What you have there is close. I don't have Oracle on my machine here so I can't verify this but I believe you could use:

insert into names (id, first_name, last_name) (select max(id)+1, '&firstName', '&lastName' from names);

Another alternative would be to place use a trigger for insert, but a sequence would be easier.

-- 
Ken Denny
http://www.kendenny.com/

If at first you don't succeed, destroy all evidence that you tried.