Oracle – Call Oracle stored procedure with no arguments

oraclestored-procedures

I'm trying to call an Oracle stored procedure that accepts no input parameters. However, when running the procedure, I get an error back that states

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

To call the proc, I'm just entering the following text into TOra:

BEGIN
 SCHEMA.MY_PROC();
END;

I've also tried (same error though)

EXEC  SCHEMA.MY_PROC();

I'm familiar with MSSQL and I'm able to execute SP with no problem using SQL server, but I can't figure out how to do the same with Oracle. I can't view the actual code for the stored procedure, but from the limited documentation I have, it appears it accepts no input parameters and the return value is a ref cursor. I have a feeling that I need to pass in a ref cursor somehow, but everything I've tried in that regard has not worked.

I just want to view the results of the SP as if I had done a SELECT statement, that is, with the records populating the data grid in the results panel in the TOra interface.

Best Answer

It sounds like the procedure does have an OUT parameter (in Oracle, procedures do not return anything but can have OUT and IN OUT parameters, functions return something). So you would have to pass in a variable for that OUT parameter. Something like

DECLARE
  l_results SYS_REFCURSOR;
BEGIN
  schema.my_proc( l_results );
END;

should successfully call the procedure. But then you want your GUI to display the results from that cursor. That, unfortunately, gets a little more complicated because now you're talking about a GUI-specific issue.

I don't use TOra, so I don't know what you need to do in TOra to get the cursor to display. In SQL*Plus (or SQL Developer, Oracle's free GUI), you could do something like

create or replace procedure my_proc( p_rc OUT SYS_REFCURSOR )
as
begin
  open p_rc
   for select 1 col1
         from dual;
end;
/

variable rc refcursor;
exec my_proc( :rc );
print rc;

This creates a stored procedure with an OUT parameter that is a cursor, declares a host variable that can be passed in, and then prints the results.

Related Topic