Hi,

I'm a newbie in Oracle & want to write a interactive script using a
sequence to insert values into a table. In other words, here is what
I'm trying to accomplish:

1. Enter a range of how many inserts will be (i.e., let's says 5
inserts)
2. Use sequence to insert values. Assuming that a sequence already
exists.
3. This will be an interactive script.

Here is my script but so far the syntax doesn't seem to be correct.
Any helps/suggestions are appreciated.

DECLARE
   count NUMBER := 1;
   end_count NUMBER := '&end';

BEGIN
   LOOP
       ACCEPT c_lname -
       PROMPT 'Enter customer ''s last name: '
       ACCEPT c_fname -
       PROMPT 'Enter customer ''s first name: '
       ACCEPT address -
       PROMPT 'Address: '
       ACCEPT city -
       PROMPT 'City: '
       ACCEPT state -
       PROMPT 'State: '
       ACCEPT zipcode -
       PROMPT 'Zip Code: '
       ACCEPT phone -
       PROMPT 'Phone number: '
       INSERT INTO customer VALUES (customer_c_id_seq.NEXTVAL,
'&c_lname', '&c_fname','&address', '&city', '&state', '&zipcode',
'&phone');
       count := count + 1;
  EXIT WHEN count > end_count;

END;
/

TIA,
-Chris

On May 10, 7:24 pm, "lazyboy@yahoo.com" <lazyboy@yahoo.com>
wrote:

ACCEPT is an SQLPlus command while the BEGIN,  END, and  LOOP
statements are PL/SQL.  You cannot intermix the two separate
products.  You can call or execute PL/SQL from SQLPlus but you cannot
issue SQLPlus statements within PL/SQL.

SQLPlus is a command line utility and does not contain a looping
mechanism.

There are various techniques to accomplish what you want.

One is to write a shell script (Korn, perl, DOS, etc....) that loops
executing a SQLPlus script that uses ACCEPT and PROMPT  to ask for and
accept data which it then inserts.

You can also just build the insert statement in the shell and submit
it via SQLPlus executed via the script.

sqlplus user/password @script_name

or

sqlplus user/password <<EOF
insert into target values ($var1, $var2, ...)
EOF

Note - samples typed on the fly so errors may exist

HTH -- Mark D Powell --

-----------------------------------------------Reply-----------------------------------------------