Taking 2 inputs from user and calling procedure in PLSQL

Question

Hey I'm trying to call a procedure which takes two parameters in and both of them should be taken from user.So i came up with this code which is not working.(I'm working with SQL Developer)

create or replace procedure upd( x in binary_double , y in char) as
res binary_double;
begin
  dbms_output.put_line('Hello World');
end;

/*User input and calling of function*/
accept X binary_double prompt 'Enter percentage X (lower case)';
accept Y char(2) prompt 'Enter product type (lower case)';
call upd(&X, &Y);

It gives following error after prompting twice for input

old:call upd(&X, &Y)
new:call upd(12, a)

Error starting at line 69 in command:
call upd(&X, &Y)
Error report:
SQL Error: ORA-06576: not a valid function or procedure name
06576. 00000 -  "not a valid function or procedure name"
*Cause:    Could not find a function (if an INTO clause was present) or
           a procedure (if the statement did not have an INTO clause) to
           call.
*Action:   Change the statement to invoke a function or procedure

It works smoothly if I call the procedure like this " call upd(&X,'a') " but fails miserably if i call it in above manner.So question is why this happens and how to get rid of it ?

Answer 1

Firstly, accept doesn't let you specify char length, just a SQL*Plus style format, so the prompt should be:

accept Y char format a2 prompt 'Enter product type (lower case): '

(I prefer to end each prompt with a colon and blank space as it makes it clearer for the person entering a value, but it's up to you.)

Secondly, you need to quote string values. Try this:

call upd(&X, '&Y')