Re: passing PL/SQL variables to SQL PLUS

Yes, of course. It's so easy to overlook :) nvl(max(id)+1, 1) would certainly be more correct. Thanks for correction.

--
Vladimir Zakharychev (bob_at_dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3cac5ae1$0$238$ed9e5944_at_reading.news.pipex.net...


> "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message


> news:a8hev7$ll3$1_at_babylon.agtel.net...

> > insert into names

> >  select max(id) + 1, '&firstName', '&lastName' from names

> >

> > will work.

>

> Unless there are no values at all in the id column in which case the first

> calculation evaluates to null.

>

> SQL> create table names (id number,first_name varchar2(50),last_name

> varchar2(50))

>   2  tablespace users;

>

> Table created.

>

> SQL> insert into names

>   2   select max(id) + 1, '&firstName', '&lastName' from names;

> Enter value for firstname: Niall

> Enter value for lastname: Litchfield

> old   2:  select max(id) + 1, '&firstName', '&lastName' from names

> new   2:  select max(id) + 1, 'Niall', 'Litchfield' from names

>

> 1 row created.

>

> SQL> commit;

>

> Commit complete.

>

> SQL> set lines 1000

> SQL> /

>

>         ID FIRST_NAME                                         LAST_NAME

> ---------- -------------------------------------------------- --------------

> --------------------

>            Niall                                              Litchfield

>

> SQL>

>

>

> --

> Niall Litchfield

> Oracle DBA

> Audit Commission UK

> *****************************************

> Please include version and platform

> and SQL where applicable

> It makes life easier and increases the

> likelihood of a good answer

>

> ******************************************

>

>