PL/SQL:PLS-00410 duplicate fields in RECORDTABLE or argument list are not permitted

[Yew12]

Hi,

The following code is supposed to stop a booking being made if falls between another bookings start and end date.

Unfortunately I get the following error message

PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted

Any help would be greatly appreciated.

Thanks in advance

 
CREATE OR REPLACE PROCEDURE p_check_room_alloc_dates (
      p_start_date         LEASE.START_DATE%TYPE,       
      p_end_date           LEASE.END_DATE%TYPE,       
      p_room_id            LEASE.ROOM_ID%TYPE,         
      p_lease_id           LEASE.LEASE_ID%TYPE,           
      p_error        OUT   VARCHAR2                   
   )
 
   IS
      p_error        VARCHAR2 (200)                  := NULL;
      v_dummy        NUMBER;
 
 
    BEGIN
 
      IF p_start_date > p_end_date
      THEN
         p_error := 'End date cannot be earlier than start date';
      END IF;
 
 
      SELECT COUNT (*)
        INTO v_dummy
        FROM LEASE
 
       WHERE (   (    p_start_date >= LEASE.START_DATE
                  AND p_start_date <
                                  NVL (LEASE.END_DATE, SYSDATE)
                 )
 
              OR (    p_start_date <= LEASE.START_DATE
                  AND p_end_date > LEASE.START_DATE
                 )
             )
         AND LEASE.ROOM_ID = p_room_id
         AND LEASE.LEASE_ID != p_lease_id;                  
 
      IF v_dummy > 0
      THEN
         p_error := 'An Overlapping Room Booking Exists for these dates';
      END IF;
 
     EXCEPTION
      WHEN OTHERS
      THEN
         p_error :=
               'System Error '
            || SUBSTR (TO_CHAR (SQLCODE) || ' ' || SQLERRM, 1, 100);    
 
END;
 

[amitpatel66]

Your error doesnt show up in your procedure. Could you please post if you are using any other record or table types in any of your code?

[Yew12]

Sorry but we are unsure as to what you mean.

If you are meaning the p_error then we have used it several times.

This procedure is only going to be used on one table.

Thank you very much for your patience with a novice.

[amitpatel66]

Sorry but we are unsure as to what you mean.

If you are meaning the p_error then we have used it several times.

This procedure is only going to be used on one table.

Thank you very much for your patience with novice.

Is your procedure getting compiled properly?

[Yew12]

No. It gives me the response

Warning: Procedure created with compilation errors.

So I used the following script to check the errors.

show errors procedure p_check_room_alloc_dates

That's how I got the unhelpful responses from PL/SQL

[Yew12]

No. It gives me the response

Warning: Procedure created with compilation errors.

So I used the following script to check the errors.

show errors procedure p_check_room_alloc_dates

That's how I got the unhelpful responses from PL/SQL

After messing with the code some more and taking out the line p_error varchar

the compliation errors go away.

But the script still doesn't perform the correct action.

hope this helps

[amitpatel66]

Here is the problem:

CREATE OR REPLACE PROCEDURE p_check_room_alloc_dates (
      p_start_date         LEASE.START_DATE%TYPE,       
      p_end_date           LEASE.END_DATE%TYPE,     
      p_room_id            LEASE.ROOM_ID%TYPE,   
      p_lease_id           LEASE.LEASE_ID%TYPE,        
      p_error        OUT   VARCHAR2                 
   )
 
   IS
      --p_error        VARCHAR2 (200)                  := NULL; -- This variable was same as parameter variable causing the problem. This variable is not required.
      v_dummy        NUMBER;
 
 
    BEGIN
 
      IF p_start_date > p_end_date
      THEN
         p_error := 'End date cannot be earlier than start date';
      END IF;
 
 
      SELECT COUNT (*)
        INTO v_dummy
        FROM LEASE
 
       WHERE (   (    p_start_date >= LEASE.START_DATE
                  AND p_start_date <
                                  NVL (LEASE.END_DATE, SYSDATE)
                 )
 
              OR (    p_start_date <= LEASE.START_DATE
                  AND p_end_date > LEASE.START_DATE
                 )
             )
         AND LEASE.ROOM_ID = p_room_id
         AND LEASE.LEASE_ID != p_lease_id;                  
 
      IF v_dummy > 0
      THEN
         p_error := 'An Overlapping Room Booking Exists for these dates';
      END IF;
 
     EXCEPTION
      WHEN OTHERS
      THEN
         p_error :=
               'System Error '
            || SUBSTR (TO_CHAR (SQLCODE) || ' ' || SQLERRM, 1, 100);    
 
END;
 

[Yew12]

Thanks

Now to execute the code we used the following statment

VARIABLE g_error VARCHAR2(200);

EXECUTE p_check_room_alloc_dates (26, 1, '12-MAR-2008', '24-MAR-2008', :g_error);

Print g_error;

As we know that the following booking is already in use it does give the error and does not insert the record, but if we enter data that should not fail the g_error comes back blank but no data is inserted.

[amitpatel66]

I dont find any insert statement in your procedure?