Our tables don't have foreign key constraints or sequences and these are enforced using code in the legacy frontend. Now, while modernizing these apps to APEX web apps, I need to encapsulate all the constraint logic into REST APIs so they could be tested individually without having to run the app and the APIs could also be used in other apps. Later on, we may enforce foreign key constraints and sequences but for now, I need to isolate my code into ORDS REST APIs so frequent DEV environment db refresh from PROD does not overwrite my changes.
Here is some code in the POST API:
DECLARE
v_Count INTEGER := 0;
BEGIN
SELECT COUNT(ADDRESS_ID)
INTO v_Count
FROM ADDRESS_TYPE
WHERE description = :description;
htp.p(v_Count); -- Doesn't work
IF v_Count > 0 THEN
RAISE_APPLICATION_ERROR( -20001, 'Address Type Description already exists!');
:status_code := 400;
RETURN;
END IF;
SELECT MAX(NVL(ADDRESS\_ID, 0)) + 1
INTO :address\_id
FROM ADDRESS\_TYPE;
htp.p(:address_id); -- Doesn't work
INSERT INTO ADDRESS_TYPE
( address_id, description)
VALUES
(:address_id, :description);
:forward_location := :address_id;
:status_code := 201;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM); -- Doesn't work
RAISE;
END;
When I try to insert a new row through an APEX Form, it fails saying, "ORA-01400 Cannot insert NULL into ADRESS_TYPE.ADDRESS_ID".
Not sure why it is bypassing the code above the INSERT statement that fills in the ADDRESS_ID with MAX+1 value.
Please help...
Thanks!