Re: passing PL/SQL variables to SQL PLUS

At this point, I'd have to say you should be thinking about writing a program. SQL*Plus was not meant for performing things like this. Try downloading Oracle IDS (developer suite).

"Eva Haiden" <eva.haiden_at_engnetworld.com> wrote in message news:52046b21.0204051212.1c129eba_at_posting.google.com...
> all,
>
> 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 Sun Apr 07 2002 - 03:30:49 CEST