stored functions execution issue

Question

I have a table from where I want get next lowest priority DVDid, give me that as output and delete it from table. I'm doing this by creating a Package...code gets compiled but when I execute it give me error. Any suggestion?

table:
RENTALQUEUE (MEMBERID, DVDID, DATEADDEDINQUEUE, PRIORITY)

Here is the package:

create or replace
PACKAGE  pk_GET_TITLEID AS
procedure sp_GET_TITLEID(memberid_arg IN NUMBER,
titleid_arg out number) ;
end pk_GET_TITLEID;

create or replace
package body pk_GET_TITLEID as 
procedure sp_GET_TITLEID 
(memberid_arg IN NUMBER,
titleid_arg out number)

IS 
v_priority number;
begin

SELECT  MIN(PRIORITY)into v_priority
FROM RENTALQUEUE 
WHERE memberid = memberid_arg;

DBMS_OUTPUT.PUT_LINE (titleid_arg);

DELETE FROM RENTALQUEUE 
WHERE MEMBERID=memberid_arg AND dvdid=titleid_arg;

END sp_GET_TITLEID;
END PK_GET_TITLEID;

Here is how I call it:

execute pk_get_titleid.sp_get_titleid('1');

Here is the error:

Error:
    Error starting at line 403 in command:
    execute pk_get_titleid.sp_get_titleid('1')
    Error report:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'SP_GET_TITLEID'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.

Answer 1

The error is:

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

How many parameters does your procedure have in its signature?

procedure sp_GET_TITLEID 
    (memberid_arg IN NUMBER
    ,   titleid_arg out number)  

Two. How many parameters are you passing?

execute pk_get_titleid.sp_get_titleid('1')

One.

An out parameter is still a parameter. You need to include it in the call. Specifically you need a variable in the calling environment which can receive the answer from the called procedure. The fact that your code doesn't actually populate the titleid_arg parameter doesn't affect this.

---

The non-population of the titleid_arg parameter just highlights the confusion in your code. Perhaps it should be an IN parameter? Perhaps you need to select it from another table? What is the point of v_priority?