Re: Interactive script using sequence to insert values

On May 10, 7:24 pm, "lazyboy..._at_yahoo.com" <lazyboy..._at_yahoo.com> wrote:
> 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

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 -- Received on Thu May 10 2007 - 19:41:14 CDT