procedure doesnt like the parameters passed into it

[VikkyB]

HI Ive created a package + body the following procedure has an OUT, IN,IN declaration at the top of the procedure, Ive tried running the script and it keeps throwing the error below, its something really easy but at the mo cant see the wood for the trees. thanks in advance for any help.

SQL> execute ManageHotel.AddGuest ('null', 'John Poulton', 'Wrexham')
BEGIN ManageHotel.AddGuest ('John Poulton', 'Wrexham'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ADDGUEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

-- AddGuest adds the given guest to the guest table
PROCEDURE AddGuest(out_guest_no OUT Guest.Guest_no%TYPE,
in_guest_name IN Guest.name%TYPE,
in_guest_address IN Guest.address%TYPE)
IS
v_Count INT;
BEGIN

-- Check to see if guests exists with the name and address
-- Use UPPER to ignore case settings in queries or data
SELECT COUNT(*) INTO v_Count
FROM Guest
WHERE UPPER(NAME) = UPPER(in_guest_name)
AND UPPER(Address) = UPPER(in_guest_address);

-- If this guest does not existcreate and entry and commit
IF (v_Count = 0) THEN

--create a new guest number
SELECT MAX(GUEST_NO)+1 INTO out_guest_no
FROM GUEST;

-- Now create the new record and commit it to the database
INSERT INTO Guest(GUEST_NO, NAME, ADDRESS)
VALUES (out_guest_no, in_guest_name, in_guest_address);
COMMIT;
DBMS_OUTPUT.PUT_LINE('New guest record inserted');

ELSE

-- Guest already exists guest number is returned
SELECT GUEST_NO INTO out_guest_no
FROM Guest
WHERE UPPER(NAME) = UPPER(in_guest_name)
AND UPPER(Address) = UPPER(in_guest_address);
END IF;

END AddGuest;

[jmodic]

Couple of thingsd here:

What is the type of Guest.Guest_no column? I belive it is NUMBER (or some subtype of this type), no? And you pass a string 'null' to it - certanly a type missmatch that causes PLS-00306 error. if you want to pass a NULL value to a number parameter, use TO_NUMBER(NULL) expression, that is NULL without any quotes.

Besides, your OUT_GUEST_NO parameter is output parameter, so you probably want to store the returned value in some kind of variable, don't you? So in Sql*Plus, you would do something like this:

VARIABLE v_guest_no NUMBER

execute ManageHotel.AddGuest (:v_guest_no, 'John Poulton', 'Wrexham');

print v_guest_no

And on top of that all, my general remark about your procedure's code is:
- it's very poorly written and hence terribly inefficient
- many examples of "worst programing practices" in it
- it probably also use wrong logic - if your GUEST_NO is a primary key of that table you could some day be surprised that your procedure fails with "primary key violated" error...

[VikkyB]

Thank you jmodoic for your (kind?) words of advice, I would like to add that I am learning to code at the moment and can only hope to one day be as good as your kind self, good on you, my crappy procedure works at last!