PL/SQL: how do I prompt user input in a procedure?

Question

This is a question about a small part of a large project I'm doing. I tried the following but I just get the two errors below it:

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE HELLO AS
DECLARE
variable1 NUMBER(1);
variable2 CHAR(1);

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
variable1 := &please_enter_1_or_0;
variable2 := &please_enter_y_or_n;
END;
/

Error(2,5): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

Error(10,8): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with
<< continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall merge pipe purge

We were given a markscheme of how our code would be marked, and for this section, the relevant criteria would be:

"Does the script use a procedure?" and

"Does the script prompt for right/wrong and team/individual and handle the data provided correctly?".

The project brief quotes "Develop a procedure that prompts for RIGHT/WRONG (using &), then updates table" (where table is the name of a table).

The purpose of the variables was to update an existing record attribute. i.e. if user chose 1 and n then update the null in the record to 2. if it was 1 and y then update to 1, and if 0 and y/n then update to 0.

Solution 1

PL/SQL is a language for writing autonomous programs. It is not designed for user interactivity. Input values are passed as parameters. So your program should look like this

CREATE OR REPLACE PROCEDURE hello
    ( p1 in number
    , p2 in varchar2 )
AS
    l_salutation varchar2(20) := 'Hello World';
BEGIN
    DBMS_OUTPUT.PUT_LINE(l_salutation);
    DBMS_OUTPUT.PUT_LINE('p1 = ' || p1);
    DBMS_OUTPUT.PUT_LINE('p2 = ' || p2);
END;
/

Note there is no need for DECLARE with a named Procedure. The section between AS and BEGIN is for declaring variables, as I've done with l_salutation.

You can provide values for those parameters when invoking the program. In SQL*Plus it would work like this:

SET SERVEROUTPUT ON

accept p1 prompt "please enter 1 or 0: "
accept p2 prompt "please enter Y or N: "

exec HELLO (&p1, '&p2')

Solution 2

This piece of code works only in SQL*Plus and can't be used to produce a stored procedure!!!

DECLARE
variable1 NUMBER(1);
variable2 CHAR(1);

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
variable1 := &please_enter_1_or_0;
variable2 := '&please_enter_y_or_n';
END;

Mind the difference in the last statement where the last substitution variable is quoted in a string to be properly accepted by the PL/SQL syntax. Anyway, as I told you in the last comment to your question this is not a user interaction but just the result of a statement preprocessing. Every time you input different values the RDBMS executes a different source code.

Probably your requirement to use a "procedure" doesn't meant to use a STORED procedure(that is impossible to do so), but they just intended a SQL*Plus script, ask for clarifications.

Solution 3

You cannot directly receive messages from the client in a PL/SQL procedure or package.

The best you can do to emulate this is to interface with table data, and have users insert data into the table and react to that, or use Advanced Queueing (which amounts to pretty much the same thing).

Alternatively, accept the user input as parameters when the procedure is called.

Comments

[Alessandro Rossi]

PL/SQL doesn't handle user interactions when running on the RDBMS, thus you should first get parameters in a different way then pass them to the procedure or anonymous block through binding. User interactions must be handled by the client or a middle-tier architecture.

[Alessandro Rossi]

One more thing the & symbol is accepted only by SQL*Plus that preprocesses the statement and makes the needed substitutions before sending the code with substituted values to the RDBMS. Ask your colleagues to help you on finding information about it. This side of server programming may be quite ugly to face by a novice. Mind that once the PL/SQL block began there are no easy ways to interact, and it's always better to avoid doing it.

[user3120554]

I'm not quite sure what to do. I have a requirement to use a "procedure". Your suggestion, while great, seems to go beyond the contents of my training. It it matters, the purpose of the variables was to update an existing record attribute. i.e. if user chose 1 and y then update null in record to 2. if it was 1 and n then update to 1, ad if 0 and y/n then update to 0

[David Aldridge]

You just can't interact with the user to prompt them, but you can accept parameters as part of the procedure call , so the procedure would be called as: hello( 1 , 'Y' )

[user3120554]

I removed declare, and ran the script. I got the message that it was compiled but had errors: Error(9,5): PL/SQL: Statement ignored. Error(9,18): PLS-00201: identifier 'Y' must be declared

[user3120554]

Thank you for the comment. Apologies for my inexperience, I'm still learning PL/SQL. It may be the reason for my misunderstanding. We were given a markscheme of how our code would be marked, and for this section, the relevant criteria would be: "Does the script use a procedure?" and "Does the script prompt for right/wrong and team/individual and handle the data provided correctly?". The project brief quotes "Develop a procedure that prompts for RIGHT/WRONG (using &), then updates table" (where table is the name of a table I have)

[Christof Kälin]

Somehow that did not work out for me, I had to use exec HELLO (&p1, &p2) in my case otherwise there were troubles with binding.