Re: user interaction in pl/sql

From: The Elemenatlist <the_elementalist_at_hotmail.com>
Date: 13 Apr 2004 14:12:33 -0700
Message-ID: <d04b9a68.0404131312.5da642b0@posting.google.com>

Hans Forbrich <forbrich_at_yahoo.net> wrote in message news:<PXDdc.26544$J56.21293_at_edtnps89>...
> niranjan v sathe wrote:
>
> > Hans Forbrich <forbrich_at_yahoo.net> wrote in message
> >
> > you can try with colon (:variablename) in pl-sql. This should prompt for a
> > value.
>
> Not unless you are in SQL*Plus (or iSQLPlus). And even then, you must set
> it up right.

Try an Ampersand "&" ... it's about the closest you can get to prompting while in PL/SQL ... but as Jim pointed out, you can't do the "prompt" in pl/sql ..

Here's the difference (both run on Unix O/S):

SQL*Plus:

accept happy prompt "Are You Happy Today? (Y/N) "

select something
  from somewhere
 where some_column = '&happy'
/

• OR ... fall into pl/sql after gathering the prompts ...

declare
  lv_happy varchar2(1) := upper(ltrim(substr(nvl('&happy', 'N'), 1, 1)));
begin

• place code here end; /

  ---

But for PL/SQL ... you really only have this choice:

declare
  lv_happy varchar2(1) := upper(ltrim(substr(nvl('&areyouhappy', 'N'), 1, 1)));
begin

• place code here end; /

  ---

and that's about the best you can do ....

The Elementalist Received on Tue Apr 13 2004 - 16:12:33 CDT