How to take value from user during procedure in Oracle 10g

Question

Below is my procedure

create or replace procedure demo_table () is

temp number(5);

begin 

temp:=:temp;

end;

I'm getting the error

ERROR at line 4: PLS-00049: bad bind variable 'TEMP'

2. temp number(5);
3. begin 
4. temp:=:temp;
5. end;

what is wrong with the code?

Answer 1

As it is a stored procedure, the right way to do it is to pass a parameter to the procedure. For example:

SQL> create or replace procedure demo_table(par_temp in number)
  2  is
  3  begin
  4    dbms_output.put_line('You entered number ' || par_temp);
  5  end;
  6  /

Procedure created.

SQL> set serveroutput on
SQL> begin
  2    demo_table(123);
  3  end;
  4  /
You entered number 123

PL/SQL procedure successfully completed.

SQL>

Or, if you want to prompt user to enter some value, you can do it as follows:

SQL> accept par_some_value prompt 'Enter some number: '
Enter some number: 5545
SQL> begin
  2    demo_Table(&par_some_value);
  3  end;
  4  /
old   2:   demo_Table(&par_some_value);
new   2:   demo_Table(5545);
You entered number 5545

PL/SQL procedure successfully completed.

SQL>