plsqloracle-apexinteractive-grid

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


Interactive Grid Image

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.


Solution

  • 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