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.

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

Answer 4

You can just Remove the declare to remedy that ora error