How to Execute stored procedure from SQL Plus?

Question

I have a stored procedure in oracle and want to test it from SQLPlus.

If I use

execute  my_stored_proc (-1,2,0.01) 

I get this error

PLS-00306: wrong number or types of arguments in call to my_stored_proc

The beginning for the proc is this

create or replace PROCEDURE my_stored_proc
( a IN NUMBER, 
  b IN NUMBER, 
  c IN NUMBER, 
  z out NUMBER
) AS ....

Do I need to provide the a var for the out parameter, is so how? I tried:

var z  NUMBER;

But get this error when I try to run the proc

execute  my_stored_proc (-1,2,0.01,z) 
PLS-00201: identifier 'Z' must be declared

Also when I was in SQL-Developer it gave me the usage and it show the inputs in reverse order, that is:

execute my_stored_proc(z number,c number,b number,a number);

Do you provide them in reverse order or is that just something with SQL-Developer

I did not write the procedure and I don't normally deal with them so I could be missing something obvious.

Thanks

Answer 1

You have two options, a PL/SQL block (see "OMG! Ponies answered my question!") or SQL*Plus bind variables:

var z number

execute  my_stored_proc (-1,2,0.01,:z)

print z

Answer 2

You forgot to put z as an bind variable.

The following EXECUTE command runs a PL/SQL statement that references a stored procedure:

SQL> EXECUTE -
> :Z := EMP_SALE.HIRE('JACK','MANAGER','JONES',2990,'SALES')

Note that the value returned by the stored procedure is being return into :Z