user interaction in pl/sql

[DottingTheNet]

hi!!
just wanna know how to accept user input??
if i prompt a question "Are u happy today?(Y/N)"
how do i receive user's answer?
thnx

[Jim Kennedy]

Re: user interaction in pl/sql


"DottingThe Net" <dottingthenet@ hotmail.comwrot e in message
news:11bf8d5a.0 404081756.62189 5f0@posting.goo gle.com...

hi!!
just wanna know how to accept user input??
if i prompt a question "Are u happy today?(Y/N)"
how do i receive user's answer?
thnx

You can do this in sqlPlus but not in pl/sql.
Jim

[Mark C. Stock]

Re: user interaction in pl/sql


"Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message
news:LAndc.9505 3$gA5.1237341@a ttbi_s03...
|
| "DottingThe Net" <dottingthenet@ hotmail.comwrot e in message
| news:11bf8d5a.0 404081756.62189 5f0@posting.goo gle.com...
| hi!!
| just wanna know how to accept user input??
| if i prompt a question "Are u happy today?(Y/N)"
| how do i receive user's answer?
| thnx
| You can do this in sqlPlus but not in pl/sql.
| Jim
|
|


pl/sql is run in the database (unless you're using oracle's Form or Report
Builder tools to developer GUI apps)

so, there is no communication with the client tool while PL/SQL is running,
hence no prompting

what is your development environment?

;-{ mcs

[Hans Forbrich]

Re: user interaction in pl/sql

DottingTheNet wrote:

hi!!
just wanna know how to accept user input??
if i prompt a question "Are u happy today?(Y/N)"
how do i receive user's answer?
thnx

Counter-question - if the database is on a server, and the pl/sql is run in
the database, where is the answer coming from?

You might want to give some hint of the environment you are running. Are
you displaying in a browser or in SQL Plus?

[niranjan v sathe]

Re: user interaction in pl/sql

Hans Forbrich <forbrich@yahoo .netwrote in message news:<pKndc.237 09$J56.18035@ed tnps89>...

DottingTheNet wrote:
>

hi!!
just wanna know how to accept user input??
if i prompt a question "Are u happy today?(Y/N)"
how do i receive user's answer?
thnx

>
Counter-question - if the database is on a server, and the pl/sql is run in
the database, where is the answer coming from?
>
You might want to give some hint of the environment you are running. Are
you displaying in a browser or in SQL Plus?

you can try with colon (:variablename) in pl-sql. This should prompt for a value.

[Mark C. Stock]

Re: user interaction in pl/sql


"niranjan v sathe" <satheniranjan@ yahoo.comwrote in message
news:c3fae289.0 404090915.3ac8e 1c4@posting.goo gle.com...
| Hans Forbrich <forbrich@yahoo .netwrote in message
news:<pKndc.237 09$J56.18035@ed tnps89>...
| DottingTheNet wrote:
| >
| hi!!
| just wanna know how to accept user input??
| if i prompt a question "Are u happy today?(Y/N)"
| how do i receive user's answer?
| thnx
| >
| Counter-question - if the database is on a server, and the pl/sql is run
in
| the database, where is the answer coming from?
| >
| You might want to give some hint of the environment you are running.
Are
| you displaying in a browser or in SQL Plus?
|
| you can try with colon (:variablename) in pl-sql. This should prompt for a
value.

not exactly... if you're submitting anonymous pl/sql blocks, some tools may
notice the bind variable and prompt for it, but SQL*Plus will not
automatically prompt for bind variable

SQLbegin
2 dbms_output.put _line('A bind variable... ' || : x );
3 end;
4 /
SP2-0552: Bind variable "X" not declared.


additionally, stored PL/SQL program units will not compile with bind
variables

SQLcreate or replace procedure bindx
2 is
3 begin
4 dbms_output.put _line('A bind variable... ' || : x );
5 end;
6 /

Warning: Procedure created with compilation errors.

SQLshow errors
Errors for PROCEDURE BINDX:
....

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/50 PLS-00103: Encountered the symbol ":" when expecting one of the
following:
( - + mod null <an identifier>
<a double-quoted delimited-identifier<a bind variableavg
count current max min prior sql stddev sum variance execute
forall time timestamp interval date
<a string literal with character set specification>
<a number<a single-quoted SQL string>
The symbol ":" was ignored.

;-{ mcs

[Hans Forbrich]

Re: user interaction in pl/sql

niranjan v sathe wrote:

Hans Forbrich <forbrich@yahoo .netwrote 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.

[The Elemenatlist]

Re: user interaction in pl/sql

Hans Forbrich <forbrich@yahoo .netwrote in message news:<PXDdc.265 44$J56.21293@ed tnps89>...

niranjan v sathe wrote:
>

Hans Forbrich <forbrich@yahoo .netwrote 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(sub str(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(sub str(nvl('&areyo uhappy',
'N'), 1, 1)));
begin
-- place code here
end;
/

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

The Elementalist