Hello there,
I am using APEX 4.2.0.00.27 with Oracle DB 11.2.0.3.0.
I am working with a wizard-created tabular form to insert and update on a table using the built-in MRU process (sequence 10). I am attempting to use a PL/SQL anonymous block process (sequence 30) to do some further manipulation of table after the records have been inserted or updated. The manual process is associated with my tabular form and I am using the column name bind variables in my program block.
My table (rsn_test) has 3 columns: test_id (number), test_nbr (number), test_id2 (number). Column test_id is identified as the primary key and its source type is existing sequence rsn_test_seq. Column test_id2 gets its default value of 0 from a hidden page item.
I would like to use my manual process to update the value of column test_id2. If it is 0 then I want to set it to the value of column test_id. If it is some other value then it should stay that value. My logic works fine for an existing row, but I am running into a problem with newly added rows. The new rows get inserted, but the test_id2 column stays at the default 0. I can tell from the debugger that the MRU process fires first and inserts the row, then my manual process fires. The problem seems to be that the bind variable :TEST_ID for the primary key column stays NULL after the insert. I don't know how to get value of the test_id column from my newly created row to use in my PL/SQL block to make my update.
PL/SQL process:
DECLARE
BEGIN
:P7_SHOW := NULL;
:P7_SHOW := NVL(:TEST_ID2,555) || ' and ' || NVL(:TEST_ID,787) || ' and ' || NVL(:TEST_NBR,9999);
IF :TEST_ID2 = 0 AND :TEST_ID IS NOT NULL THEN
UPDATE rsn_test
SET test_id2 = :TEST_ID
WHERE test_id = :TEST_ID;
ELSE
:TEST_ID2 := :TEST_ID2;
END IF;
END;
Debugger excerpt:
0.01625 0.00010 Processes - point: ON_SUBMIT_BEFORE_COMPUTATION
0.01635 0.00008 Branch point: Before Computation
0.01643 0.00003 Process point: AFTER_SUBMIT
0.01646 0.00022 Tabs: Perform Branching for Tab Requests
0.01668 0.00008 Branch point: Before Validation
0.01676 0.00024 Validations:
0.01700 0.00135 Perform basic and predefined validations:
0.01835 0.00020 Perform custom validations:
0.01855 0.00049 ...Validation "TEST_NBR must be numeric" - Type: ITEM_IS_NUMERIC
0.01904 0.00007 ......Skip for row 1 because row hasn't changed
0.01911 0.00016 ......Skip for row 2 because row hasn't changed
0.01927 0.00012 ...Validation "TEST_ID2 must be numeric" - Type: ITEM_IS_NUMERIC
0.01939 0.00007 ......Skip for row 1 because row hasn't changed
0.01945 0.00018 ......Skip for row 2 because row hasn't changed
0.01964 0.00005 Branch point: Before Processing
0.01968 0.00004 Processes - point: AFTER_SUBMIT
0.01972 0.00588 ...Process "ApplyMRU" - Type: MULTI_ROW_UPDATE
0.02560 0.00154 ...Execute Statement: declare function x return varchar2 is begin begin for c1 in ( select "RSN_TEST_SEQ".nextval pk from sys.dual ) loop return c1.pk; end loop; end; return null; end; begin wwv_flow.g_value := x; end;
0.02714 0.00140 ......Row 3: insert into "APPPCSRSN"."RSN_TEST" ( "TEST_ID", "TEST_NBR", "TEST_ID2") values ( :b1, :b2, :b3)
0.02854 0.00011 ...Process "ApplyMRD" - Type: MULTI_ROW_DELETE
0.02865 0.00004 ......Skip because condition or authorization evaluates to FALSE
0.02869 0.00015 ...Process "Process Submit" - Type: PLSQL
0.02884 0.00007 ......Skip for row 1 because row hasn't changed
0.02891 0.00012 ......Skip for row 2 because row hasn't changed
0.02903 0.00012 ......Process row 3
0.02915 0.00429 ...Execute Statement: begin DECLARE BEGIN :P7_SHOW := NULL; :P7_SHOW := NVL(:TEST_ID2,555) || ' and ' || NVL(:TEST_ID,787) || ' and ' || NVL(:TEST_NBR,9999); IF :TEST_ID2 = 0 AND :TEST_ID IS NOT NULL THEN UPDATE rsn_test SET test_id2 = :TEST_NBR WHERE test_id = :TEST_ID; ELSE :TEST_ID2 := :TEST_ID2; END IF; END; end;
0.03344 0.00013 ...Session State: Saved Item "P7_SHOW" New Value="0 and 787 and 1300"
0.03356 0.00004 Branch point: After Processing
0.03360 0.00048 ...Evaluating Branch: "AFTER_PROCESSING" Type: REDIRECT_URL Button: (No Button Pressed) Condition: (Unconditional)
0.03407 0.00013 Redirecting to f?p=290:7:8717971109610:::::&success_msg=0%20row(s)%20updated%2C%201%20row(s)%20inserted.Success%2FEBD244168556408CBA714E3974918C09%2F
0.03420 0.00012 Stop APEX Engine detected
0.03432 0.00007 Stop APEX Engine detected
0.03439 - Final commit
Any suggestions?