Why do I get the PLS-00306: wrong number or types of arguments error when calling a Package PROC?

Question

Here is the top part of my Package Body method:

PROCEDURE GetPortfolioAppsAndProjects
(
   loginId          IN VARCHAR2 := NULL,
   portfolioId      IN NUMBER := NULL,
   portfolioType    IN VARCHAR := NULL,
   ic               IN VARCHAR := NULL,
   AppIds_CUR       IN OUT SYS_REFCURSOR
) 
IS
  INVALID_PORTFOLIO_TYPE EXCEPTION;
BEGIN

This runs fine when I execute from the PL/SQL execute dialogue. I need to be able to run this from an SQL Query window though and join to other tables and what not to develop.

I'm trying to execute this sql from the Sql Window:

DECLARE AppIds_CUR SYS_REFCURSOR;
BEGIN
   IREPORT_PORTFOLIOS.GetPortfolioAppsAndProjects('EVANSF', null, null,    null, :AppIds_CUR);
END;

And I get this error:

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

I count 5 incoming (including one IN OUT cursor). In my call I pass 5 including the cursor. How can I get the results of the cursor to the output vars window.

Answer 1

AppIds_CUR is declared locally in your block; you don't need to treat it as a bind variable by prepending a colon:

DECLARE
  AppIds_CUR SYS_REFCURSOR;
BEGIN
   IREPORT_PORTFOLIOS.GetPortfolioAppsAndProjects('EVANSF', null, null, null, AppIds_CUR);
END;

If you run what you had as a statement in SQL Developer it would prompt you for a bind value, which isn't what you want to happen, and would treat it as a varchar not a ref cursor - hence the wrong-type error.

You'd need to loop over the cursor and display it's values manually with dbms_output with this approach. But you could use a bind variable if you run it as a script, by declaring the type - which would be outside the block, so you don't need the declare:

variable AppIds_CUR REFCURSOR;

BEGIN
   IREPORT_PORTFOLIOS.GetPortfolioAppsAndProjects('EVANSF', null, null, null, :AppIds_CUR);
END;
/

print AppIds_CUR

You can display the cursor contents with the SQL Developer print command.