Re: problem with accept statement in PL/SQL loops urgent please new message

worldkumaril_at_yahoo.com (Kumaril Bhatt) wrote in message news:<4a678c58.0303131712.1f7b56c4_at_posting.google.com>...
> Hi everybody,
> My name is Kumaril Bhatt and I know SQL*plus.This is a new message. I
> am also including the errors I encountered. The version of oracle Iam
> using is 9.02. I have made some changes to the loop. The help needed
> is urgent. Please bail me out. If I have to invoke PL/SQL from SQL,
> Please provide me with script. I also used execute immediate statement
> in the loop given below. But no fruitful results
>
> 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.
> If he says 'y' new rows are inserted in the table
>
> DECLARE
> ans char(1) :='y';
> cid char(4);
> vid char(4);
> vname varchar(15);
>
> begin
>
> while(ans = 'y') loop
>
> 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 loop;
> end;
>
>
> SQL> @loop2.sql
> 24 /
> accept cid prompt "Enter customer id : ";
> *
> ERROR at line 11:
> ORA-06550: line 11, column 8:
> PLS-00103: Encountered the symbol "CID" when expecting one of the
> following:
> := . ( @ % ;
>
>
> Thank You very Much
> Kumaril

Kumaril, you posted this question yesterday and you got three responses that all said it isn't going to work. Unless there has been a change made in 9.2 I am unaware of you cannot plact a SQLPlus command: accept into pl/sql code. Once SQLPlus encounters a declare or being then the code is passed to the pl/sql engine and the pl/sql engine does not recognize SQLPlus commands.

Karsten's idea of using a SQLPlus &variable will not work in your case since SQLPlus will substitute a constant for the &variable before it passes the pl/sql to the pl/sql engine so only one value will be involved, and you want a new value in each loop.

HTH -- Mark D Powell -- Received on Fri Mar 14 2003 - 11:06:50 CST