469,356 Members | 1,958 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

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

23
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE PROCEDURE p_check_room_alloc_dates (
  3.       p_start_date         LEASE.START_DATE%TYPE,       
  4.       p_end_date           LEASE.END_DATE%TYPE,       
  5.       p_room_id            LEASE.ROOM_ID%TYPE,         
  6.       p_lease_id           LEASE.LEASE_ID%TYPE,           
  7.       p_error        OUT   VARCHAR2                   
  8.    )
  9.  
  10.    IS
  11.       p_error        VARCHAR2 (200)                  := NULL;
  12.       v_dummy        NUMBER;
  13.  
  14.  
  15.     BEGIN
  16.  
  17.       IF p_start_date > p_end_date
  18.       THEN
  19.          p_error := 'End date cannot be earlier than start date';
  20.       END IF;
  21.  
  22.  
  23.       SELECT COUNT (*)
  24.         INTO v_dummy
  25.         FROM LEASE
  26.  
  27.        WHERE (   (    p_start_date >= LEASE.START_DATE
  28.                   AND p_start_date <
  29.                                   NVL (LEASE.END_DATE, SYSDATE)
  30.                  )
  31.  
  32.               OR (    p_start_date <= LEASE.START_DATE
  33.                   AND p_end_date > LEASE.START_DATE
  34.                  )
  35.              )
  36.          AND LEASE.ROOM_ID = p_room_id
  37.          AND LEASE.LEASE_ID != p_lease_id;                  
  38.  
  39.       IF v_dummy > 0
  40.       THEN
  41.          p_error := 'An Overlapping Room Booking Exists for these dates';
  42.       END IF;
  43.  
  44.      EXCEPTION
  45.       WHEN OTHERS
  46.       THEN
  47.          p_error :=
  48.                'System Error '
  49.             || SUBSTR (TO_CHAR (SQLCODE) || ' ' || SQLERRM, 1, 100);    
  50.  
  51. END;
  52.  
Apr 1 '08 #1
8 18756
amitpatel66
2,367 Expert 2GB
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?
Apr 1 '08 #2
Yew12
23
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.
Apr 1 '08 #3
amitpatel66
2,367 Expert 2GB
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?
Apr 1 '08 #4
Yew12
23
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
Apr 1 '08 #5
Yew12
23
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
Apr 1 '08 #6
amitpatel66
2,367 Expert 2GB
Here is the problem:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE p_check_room_alloc_dates (
  2.       p_start_date         LEASE.START_DATE%TYPE,       
  3.       p_end_date           LEASE.END_DATE%TYPE,     
  4.       p_room_id            LEASE.ROOM_ID%TYPE,   
  5.       p_lease_id           LEASE.LEASE_ID%TYPE,        
  6.       p_error        OUT   VARCHAR2                 
  7.    )
  8.  
  9.    IS
  10.       --p_error        VARCHAR2 (200)                  := NULL; -- This variable was same as parameter variable causing the problem. This variable is not required.
  11.       v_dummy        NUMBER;
  12.  
  13.  
  14.     BEGIN
  15.  
  16.       IF p_start_date > p_end_date
  17.       THEN
  18.          p_error := 'End date cannot be earlier than start date';
  19.       END IF;
  20.  
  21.  
  22.       SELECT COUNT (*)
  23.         INTO v_dummy
  24.         FROM LEASE
  25.  
  26.        WHERE (   (    p_start_date >= LEASE.START_DATE
  27.                   AND p_start_date <
  28.                                   NVL (LEASE.END_DATE, SYSDATE)
  29.                  )
  30.  
  31.               OR (    p_start_date <= LEASE.START_DATE
  32.                   AND p_end_date > LEASE.START_DATE
  33.                  )
  34.              )
  35.          AND LEASE.ROOM_ID = p_room_id
  36.          AND LEASE.LEASE_ID != p_lease_id;                  
  37.  
  38.       IF v_dummy > 0
  39.       THEN
  40.          p_error := 'An Overlapping Room Booking Exists for these dates';
  41.       END IF;
  42.  
  43.      EXCEPTION
  44.       WHEN OTHERS
  45.       THEN
  46.          p_error :=
  47.                'System Error '
  48.             || SUBSTR (TO_CHAR (SQLCODE) || ' ' || SQLERRM, 1, 100);    
  49.  
  50. END;
  51.  
Apr 1 '08 #7
Yew12
23
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.
Apr 1 '08 #8
amitpatel66
2,367 Expert 2GB
I dont find any insert statement in your procedure?
Apr 1 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Mourad | last post: by
2 posts views Thread by Kingkel | last post: by
5 posts views Thread by josephrthomas | last post: by
7 posts views Thread by Danieltbt05 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.