SQL command not properly ended errror database oracle-CodePudding

I have a request to add data that triggers a trigger that checks one condition, and only after that adds it to the purchase table. He also swears at the lines of initializations total_rasr and id_buyer invalid number, although there are the same types in the table and in the trigger. And the biggest question is, this trigger worked, but at one point it stopped and gives these errors.

INSERT INTO PAYMENT (ID, ADDRESS, DATE_PAYMENT, PAYMENT_METHOD_ID, EMPLOYEE_ID, BUYER_ID) 
VALUES (Key('PAYMENT'), 'Moscow', TO_DATE('2002-08-23', 'YYYY-MM-DD'),'005!00002','1','002!00005');

trigger

create or replace TRIGGER checking_the_availability_work
    BEFORE INSERT
    ON PAYMENT
    FOR EACH ROW
    
DECLARE
    rasr_is_possible boolean := true;
    total_rasr VARCHAR(10);
    id_buyer VARCHAR(10);
    AVAILABILITY_OF_WORK VARCHAR(5);
    xSQL VARCHAR(100);
BEGIN
    total_rasr := :NEW.PAYMENT_METHOD_ID;
    id_buyer := :NEW.BUYER_ID;

    IF total_rasr = '005!00002' THEN
            xSQL := 'select AVAILABILITY_OF_WORK FROM BUYER WHERE ID =' || id_buyer;
            execute immediate xSQL into AVAILABILITY_OF_WORK;
                    IF AVAILABILITY_OF_WORK = 'Нет' THEN
                         rasr_is_possible := false;
                    END IF;
     END IF;

    if not rasr_is_possible THEN
        Raise_application_error(-20201, 'У вас нет места работы!');
    end if;
END;

CodePudding user response：

Why use dynamic SQL here at all? It seems really convoluted and is resulting in an error, perhaps when :new.buyer_id is null or has some other unexpected value? Try something like this, and perhaps consider a check on :new.buyer_id to make sure it has a value as expected.

DECLARE
    l_rasr_is_possible boolean := true;
    L_AVAILABILITY_OF_WORK VARCHAR(5);
BEGIN
    IF :NEW.PAYMENT_METHOD_ID = '005!00002' THEN
        select AVAILABILITY_OF_WORK into L_AVAILABILITY_OF_WORK 
            FROM BUYER WHERE ID = :NEW.BUYER_ID;

        IF L_AVAILABILITY_OF_WORK = 'Нет' THEN
             rasr_is_possible := false;
        END IF;
    END IF;

    if not l_rasr_is_possible THEN
        Raise_application_error(-20201, 'У вас нет места работы!');
    end if;
END;