Hello,
I'm trying to save the content of a shuttle item. I´m using a "Save"-Button which triggers a Process (PL/SQL anonymous block).
The content of the shuttle item shall be stored into a table.
Filling the shuttle item with a computation works. Storing new items that have been draged from the left side to the right side works as well.
Unfortunately I´m not able to delete an existing item of the right side.
If I remove an item from the right side to the left side and click the "Save"-Button, I got the error "ORA-01722: invalid number".
I´m using the following PL/SQL block:
DECLARE
v_count NUMBER := 0;
v_shuttle_ids VARCHAR2(1000);
v_shuttle APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
v_shuttle := APEX_UTIL.STRING_TO_TABLE(:P47_PFLANZLICHE_HERKUNFT);
FOR i in 1 .. v_shuttle.count LOOP
SELECT COUNT(*) INTO v_count FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id = v_shuttle(i);
IF v_count = 0 THEN
INSERT INTO rjm.material_objekte (objekte_id, material_id) VALUES (:P47_OBJEKTE_ID, v_shuttle(i));
END IF;
v_shuttle_ids := v_shuttle_ids || v_shuttle(i) || ',';
END LOOP;
v_shuttle_ids := SUBSTR(v_shuttle_ids, 0, LENGTH(v_shuttle_ids) - 1);
DELETE FROM rjm.material_objekte WHERE objekte_id = :P47_OBJEKTE_ID AND material_id NOT IN (v_shuttle_ids);
END;
The Delete-Statement fails, because the datatypes are not compatible. The variable v_shuttle_ids has the datatype VARCHAR2 and the database column material_id has the datatype NUMBER.
Has anybody a solution for the issue (for example casting the datatypes) or has a better way of deleting existing items in the shuttle item?
Thank you in advance,
Patrick