By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

user interaction in pl/sql

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

"DottingTheNet" <do***********@hotmail.com> wrote in message
news:11**************************@posting.google.c om...
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
Jul 19 '05 #2

P: n/a

"Jim Kennedy" <ke****************************@attbi.net> wrote in message
news:LAndc.95053$gA5.1237341@attbi_s03...
|
| "DottingTheNet" <do***********@hotmail.com> wrote in message
| news:11**************************@posting.google.c om...
| > 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
Jul 19 '05 #3

P: n/a
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?
Jul 19 '05 #4

P: n/a
Hans Forbrich <fo******@yahoo.net> wrote in message news:<pKndc.23709$J56.18035@edtnps89>...
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.
Jul 19 '05 #5

P: n/a

"niranjan v sathe" <sa***********@yahoo.com> wrote in message
news:c3**************************@posting.google.c om...
| Hans Forbrich <fo******@yahoo.net> wrote in message
news:<pKndc.23709$J56.18035@edtnps89>...
| > 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

SQL> begin
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

SQL> create 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.

SQL> show 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 variable> avg
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
Jul 19 '05 #6

P: n/a
niranjan v sathe wrote:
Hans Forbrich <fo******@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.
Jul 19 '05 #7

P: n/a
Hans Forbrich <fo******@yahoo.net> wrote in message news:<PXDdc.26544$J56.21293@edtnps89>...
niranjan v sathe wrote:
Hans Forbrich <fo******@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
Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.