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

[code=oracle]

CREATE OR REPLACE PROCEDURE p_check_room_al loc_dates (
p_start_date LEASE.START_DAT E%TYPE,
p_end_date LEASE.END_DATE% TYPE,
p_room_id LEASE.ROOM_ID%T YPE,
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_DAT E
AND p_start_date <
NVL (LEASE.END_DATE , SYSDATE)
)

OR ( p_start_date <= LEASE.START_DAT E
AND p_end_date > LEASE.START_DAT E
)
)
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;
[/code]

[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_al loc_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_al loc_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:

[code=oracle]
CREATE OR REPLACE PROCEDURE p_check_room_al loc_dates (
p_start_date LEASE.START_DAT E%TYPE,
p_end_date LEASE.END_DATE% TYPE,
p_room_id LEASE.ROOM_ID%T YPE,
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_DAT E
AND p_start_date <
NVL (LEASE.END_DATE , SYSDATE)
)

OR ( p_start_date <= LEASE.START_DAT E
AND p_end_date > LEASE.START_DAT E
)
)
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;
[/code]

[Yew12]

Thanks

Now to execute the code we used the following statment

VARIABLE g_error VARCHAR2(200);

EXECUTE p_check_room_al loc_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?