Oracle Apex IG throws error when adding multiple values in one cell

Question

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.

The tag "apex" refers to a product by Salesforce - use oracle-apex. — Koen Lostrie
– Koen Lostrie, Commented Jun 24 at 9:38

Koen Lostrie · Accepted Answer · 2025-06-24 09:49:28Z

0

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

answered Jun 24 at 9:49

Koen Lostrie

19.5k2 gold badges16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wosyn Jun 25 at 6:44

It solved my problem. Thank you very much.

Collectives™ on Stack Overflow

Oracle Apex IG throws error when adding multiple values in one cell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related