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.

Answer 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')

Answer 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.