Executing a Stored Procedure in Oracle

Question

I have a stored procedure, on Toad for Oracle I am calling the procedure using

SELECT FROM PKGName.ProcedureName(1,'10/10/2010','10/23/2010',7,7) 
  FROM DUAL

I have 3 output parameter on this procedure as well I am getting an

ORA-00904: PKGName.ProcedureName : Invalid Identifier

Do have to mention the output parameter on the procedure call as well? If yes how can I use it?

Answer 1

You cannot use a procedure in a SELECT statement. Functions yes (with appropriate return types), procedures no. Items in a SELECT list must be expressions, which must resolve to a value. A procedure does not meet this criteria.

And yes, you do need to mention the output variables in your parameter list. The procedure is going to set those parameters to some values, there needs to be a output parameter specified for each to receive them. @schurik shows you how it is usually done in PL/SQL. @Datajam is close to how you'd do it in SQL*Plus, but leaves out the output parameters:

SQL> var num_var number
SQL> var txt_var varchar2(15)
SQL> var txt_var2 varchar2(20)

SQL> exec PKGName.ProcedureName(1,'10/10/2010','10/23/2010',7,7, :num_var, :txt_var, :txt_var2);

PL/SQL procedure successfully completed
num_var
---------------
42
txt_var
-----------------
some text
txt_var2
-------------------
some other text

SQL>