Trying create a procedure that takes user input in pl/sql

Question

I am trying to create a procedure to take user input for username and password, but I keep getting an error because of the '&'.

CREATE OR REPLACE FUNCTION Approval (Input_S_ID Sales.Sales_ID%Type, discount_amount NUMBER)
RETURN NUMBER 
IS    

   UN Employee.E_un%Type := &UN; 
   PS Employee.E_pass%Type := &PS;

Why don't you simply pass those values as function parameters? — Littlefoot, Dec 08 '19 at 11:07
`&` is not a special character in PL/SQL, and PL/SQL does not prompt interactively. You would need some other framework or tool to manage the interaction (Shellscript, PowerShell, Perl etc). — William Robertson, Dec 08 '19 at 14:40

score 0 · Answer 1 · answered Dec 08 '19 at 11:01

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

The best way to 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.

Further more elaboration please find link here.

Trying create a procedure that takes user input in pl/sql

1 Answers1