Hello, as a beginner in Oracle Apex, i have problem with Interactive Grid. While adding more values in Person ID column, i have an error
ORA-06502: PL/SQL: value or conversion error: character to number conversion error.
Here is what my process do.
DECLARE
l_PERSON_ID apex_application_global.vc_arr2;
v_K2S_ID NUMBER(10) :=:STATUS_ID;
BEGIN -- It runs row by row for interactive grid
IF :APEX$ROW_STATUS = 'D' THEN -- DELETE
-- Delete the record
RETURN;
ELSIF :APEX$ROW_STATUS = 'C' THEN -- CREATE
-- Insert the record
INSERT INTO STATUS_2_PERSON =STATUS_ID, PERSON_ID)
(
SELECT TO_NUMBER(:=STATUS_ID),
P.ID
FROM BM_PERSON P
WHERE P.ID IN (
SELECT TO_NUMBER(REPLACE(TRIM(column_value), ':', ''))
FROM TABLE(APEX_STRING.SPLIT(:PERSON_ID, ','))
WHERE TRIM(column_value) IS NOT NULL )
);
RETURN;
ELSIF :APEX$ROW_STATUS = 'U' THEN -- UPDATE
-- Update the record
RETURN;
END IF;
END;
My Person ID is shuttle item with delimited list with colon. I appriciate every help.
I want to insert values into status_2_person.
That insert statement looks a bit weird, it will not compile. Looks edited but not verified.
For multi select items, loop through the individual elements with a FOR loop.
DECLARE
l_person_id apex_t_number; -- use apex_t_number for numeric values
BEGIN -- It runs row by row for interactive grid
IF :APEX$ROW_STATUS = 'D' THEN -- DELETE
-- Delete the record
RETURN;
ELSIF :APEX$ROW_STATUS = 'C' THEN -- CREATE
-- Insert the record
l_person_id := apex_string.split_numbers(:PERSON_ID,':'); -- use split_numbers
FOR i IN 1 .. l_person_id.COUNT LOOP
INSERT INTO status_2_person (status_id, person_id) VALUES (:STATUS_ID,l_person_id(i));
END LOOP;
ELSIF :APEX$ROW_STATUS = 'U' THEN -- UPDATE
-- Update the record
RETURN;
END IF;
END;
The code above is untested, I guessed the columns and their names in table status_2_person. But this should give you an idea on how to proceed