Re: Problem with accept in PL/SQL

worldkumaril_at_yahoo.com (Kumaril Bhatt) wrote in message news:<4a678c58.0303130024.474f6677_at_posting.google.com>...
> Hi everybody,
> My name is Kumaril Bhatt and I know SQL*plus. I am not that familiar
> with PL/SQL. I want use loops in PL/SQL with user interaction. So I
> tried using an accept statement in a PL/SQL loop. But I got errors. I
> dont know much about scripts. So if any one could provide me with a
> script, so that the loop below can be executed. The loop asks the user
> whether he wants to insert more rows.
>
> DECLARE
> ans char(1) :='y';
> cid char(4);
> vid char(4);
> vname varchar(15);
>
> begin
>
> while(ans = 'y')
>
> accept cid prompt "Enter customer id : ";
> accept vid prompt "Enter video id : ";
> accept vname prompt "Enter videoname : ";
>
> insert hire
> values (cid,vid,vname);
>
> accept ans prompt "Any more y/n : ";
>
> end;
>
> Thank You very Much
> Kumaril

Kumaril, SQLPlus and PL/SQL are two seperate programs. You cannot mix Sqlplus commands and pl/sql statements. You can call pl/sql from sqlplus and you can pass scalar values back and forth but once you hit the begin statement till you hit the end statement it is pl/sql that sqlplus passes to the pl/sql engine to process. The pl/sql engine does not recognize sqlplus commands.

You will need a wrapper program like pro*c or a shell script with an inline sqlplus script: <<EOF, so that you can use the shell to prompt for the value and repeatedly call sqlplus to execute the pl/sql or in your case just run an insert from sqlplus.

HTH -- Mark D Powell -- Received on Thu Mar 13 2003 - 08:59:17 CST