Re: Problem with accept in PL/SQL

Kumaril Bhatt wrote:
> 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

Several problems, oh where to begin.
No ORACLE version or error info ("but I got errors" doesn't tell us anything),   but some general rules apply.

SQL*PLUS is not PL/SQL and vice versa. Think of it this way, once inside the DECLARE block, you are using a different language. ACCEPT is not supported by PL/SQL. It is a SQL*PLUS command.

Other problems:
No Commit; or Rollback;
No error handling (maybe you left it out to simplify the sample code, but I doubt it).
No LOOP...END LOOP as require for the WHILE() to work.

Some questions:
  have you done procedural programming in any language befor this?   Have you read the manuals for your question?

(I suspect the answer is a double no.)

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost