Re: passing PL/SQL variables to SQL PLUS

all,

[Quoted] Thank you all very much for your high quality input. This helped me a lot. Also, I learned quite some new things that I didn't know.

But, I have another question:

I solved the problem this way:

verify off
echo off
feedback off

ACCEPT firstName prompt 'Enter the first name: ' ACCEPT lastName prompt 'Enter last name: '

DECLARE

 v_first_name names.first_name%TYPE := UPPER('&first_name');
 v_last_name  names.last_name%TYPE  := UPPER('&last_name');
 v_id         names.id%TYPE;

BEGIN
 SELECT NVL(MAX(id)+1, 1)
  INTO v_id
 FROM names;

 INSERT INTO names VALUES (v_id,v_first_name,v_last_name); END;
/  

But I need to insert the children names of the person in the names table into the table CHILDREN. Unfortunately, I don't know in advance how many children someone will have. Therefore I will have to implement a loop, something like this:

verify off
echo off
feedback off

ACCEPT firstName prompt 'Enter the first name: ' ACCEPT lastName prompt 'Enter last name: '

DECLARE

 v_first_name names.first_name%TYPE := UPPER('&first_name');
 v_last_name  names.last_name%TYPE  := UPPER('&last_name');
 v_id         names.id%TYPE;

BEGIN
 SELECT NVL(MAX(id)+1, 1)
  INTO v_id
 FROM names;

 INSERT INTO names VALUES (v_id,v_first_name,v_last_name); END;
/

verify off
echo off
feedback off

ACCEPT numberChildren prompt 'Enter the number of children: '

DECLARE
   v_numChildren NUMBER := '&numberChildren';   i NUMBER := 0;

BEGIN    WHILE i < v_numChildren LOOP

      ACCEPT firstName prompt 'Enter the first name: '
      ACCEPT age prompt 'Enter age of child: '
      
     Insert into CHILDREN
     VALUES (select NVL(max(id)+1, 1), '&first_name', '&age');   

   END LOOP; END;
/

Unfortunately, the ACCEPT and PROMPT does not work inside the loop, also the verify off, feedback off, echo off does not work. Is there any other way with SQL*PLUS or PL/SQL to communicate with the end user?

I welcome any help.

Thank you!
Eva

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3cac59a2$0$227$ed9e5944_at_reading.news.pipex.net>...
> "Eva Haiden" <eva.haiden_at_engnetworld.com> wrote in message
> news:52046b21.0204021330.5011bd38_at_posting.google.com...
> > We don't have and do not want to install a sequence on the id column.
>
> why not? trigger/sequence is the ideal combo for this.
>
> Question: what happens if a user runs your script and doesn't commit before
> the next time the script is run?
> Answer: you get an attempt to insert the same id twice.
>
> This will not happen with a trigger/sequence.
>
>
> --
> 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
>
> ******************************************
Received on Fri Apr 05 2002 - 22:12:09 CEST