procedure doesnt like the parameters passed into it
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: procedure doesnt like the parameters passed into it

  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Post procedure doesnt like the parameters passed into it

    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;

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:
    Code:
    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...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width