Executing a Stored Procedure in Oracle

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 outp开发者_C百科ut 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?

---

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> 

---

declare 
  -- declare variables to keep output values
  output_par_1 varchar2(100);
  output_par_2 number(10);
  ...
begin
  PKGName.ProcedureName(1,'10/10/2010','10/23/2010',output_par_1,output_par_2);

  -- display output values
  dbms_output.put_line('output_par_1: ' || output_par_1);
  dbms_output.put_line('output_par_2: ' || output_par_2);
end;
/

---

If you want to be able to call procedures from select, wrap it with a function or table function. See here for more details: http://technology.amis.nl/blog/1017/calling-stored-procedures-using-plain-sql-for-when-sql-is-allowed-but-calls-to-stored-procedures-are-not (heck the title is almost an article hehehe).

---

Yes, you must provide all arguments. Declare a variable of the appropriate type and pass it as an output argument.

---

You shouldn't really call a procedure using a SELECT statement (and even if you did, the call would be before the FROM part).

Instead, use a SQL*Plus prompt (I think Toad has a built-in SQL*Plus interface):

exec PKGName.ProcedureName(1,'10/10/2010','10/23/2010',7,7);

---

The code in the question is syntactically wrong, it should be

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

However, this would only work for functions. But as it's obviously working in Toad I assume the poster actually did have a function. I also made the assumption that using SQL was a prerequisite.

If the point of the question was how to make use of multiple output parameters - try creating a user defined type. Maybe the question should then be renamed to "Calling a procedure with output parameters from SQL in Oracle".

Otherwise simple wrapper function without output parameters would do the job.