Pl/Sql: How to Prompt User Input in a Procedure

asking for user input in PL/SQL

SQL> set serveroutput on; -- for "dbms_output.put_line" to take effect
SQL> DECLARE 
a number := &i_nr; -- there's no need to restrict a non-decimal numeric variable to a length
BEGIN 
--a:= 10; --no need this when initialization is in declaration section 
-- check the boolean condition using if statement  
IF( a < 20 ) THEN 
  -- if condition is true then print the following   
  dbms_output.put_line('a is less than 20 ' ); 
END IF; 
dbms_output.put_line('value of a is : ' || a); 
END; 
/
-- it prompts you for value of &i_nr "enter a numeric value, for example 10", for string values it must be in quotes '&i_str'

Accept user input and set assign to variable PL/SQL

I suggest making the input a parameter.

    CREATE OR REPLACE PROCEDURE nums (pInput IN VARCHAR2)
    AS
       x_num VARCHAR(20);
    BEGIN
       x_num := pInput ;
       dbms_output.put_line('You entered: ' || x_num);
    END;
    /

PLSQL Procedure get user input at runtime

Short answer: you can't. PL/SQL is executed inside the database engine, and the database engine has no access to the terminal window (or database tool) you are using to start the procedure.

The code in your quesion seems to partially work, because it asks for input once, but what really happens is that: the tool (SQL*Plus, SQL Developer or whatever) parses over the PL/SQL block and sees the &-Signs, so it asks what to replace them with. Once the input is given, the PL/SQL-Block - including the entered values - is given to the database for execution.

Since you can't do that in PL/SQL, better create a front-end program first that collects the values, then sends them to the database.

Input parameters in procedure

Would this help?

Sample data:

SQL> select deptno, ename from emp where deptno in (10, 20) order by deptno;

    DEPTNO ENAME
---------- ----------
        10 MILLER
        10 CLARK
        10 KING
        20 FORD
        20 ADAMS
        20 JONES
        20 SMITH
        20 SCOTT

8 rows selected.

Procedure:

SQL> create or replace procedure p_test (input_param in varchar2) as
  2    l_cnt number;
  3  begin
  4    select count(*) into l_cnt
  5    from emp e join dept d on e.deptno = d.deptno
  6    where d.deptno in (select regexp_substr(input_param, '[^,]+', 1, level)
  7                       from dual
  8                       connect by level <= regexp_count(input_param, ',') + 1
  9                      );
 10    dbms_output.put_line('Count = ' || l_cnt);
 11  end;
 12  /

Procedure created.

SQL> exec p_test('10, 20');
Count = 8

PL/SQL procedure successfully completed.

SQL>

How to get input from user at runtime

To read the user input and store it in a variable, for later use, you can use SQL*Plus command ACCEPT.

Accept <your variable> <variable type if needed [number|char|date]> prompt 'message'

example

accept x number prompt 'Please enter something: '

And then you can use the x variable in a PL/SQL block as follows:

declare 
  a number;
begin
  a := &x;
end;
/

Working with a string example:

accept x char prompt 'Please enter something: '

declare 
  a varchar2(10);
begin
  a := '&x';   -- for a substitution variable of char data type 
end;           -- to be treated as a character string it needs
/              -- to be enclosed with single quotation marks

---

Related Topics