I am not sure if I am doing this the wrong way but I have two interactive grids that I believe should have the ability to save data properly.
For example I want the user to be able to create multiple depts and multiple employees and click save at the end. I am having trouble setting this up.
I believe I have the auto-incrementing working mostly but it fails if a user gets click happy and the value is too slow to populate. If there is a better solution to that I would love to see it.
My detail data seems to save fine once the master has already been saved to the database but for some reason it doesn't save the detail data if you create master, create detail under master, and then click save.
I have an example setup in the following link: https://apex.oracle.com/pls/apex/f?p=58641:1:703200879839745:::::
Here is the query for dept and emp grids:
SELECT ROWID row_id, deptno, dname FROM dept
SELECT ROWID row_id, empno, deptno, ename FROM emp
Here is the code for saving the interactive grids
DEPT:
BEGIN
CASE :APEX$ROW\_STATUS
WHEN 'C'
THEN
BEGIN
INSERT INTO DEPT(DEPTNO,DNAME) VALUES (:DEPTNO,:DNAME) RETURNING ROWID INTO :ROW\_ID;
END;
WHEN 'D' THEN
BEGIN
DELETE EMP WHERE DEPTNO = :DEPTNO;
DELETE DEPT WHERE ROWID = :ROW\_ID;
END;
END CASE;
EXCEPTION
WHEN OTHERS
THEN
APEX\_ERROR.ADD\_ERROR (
p\_message =>
'An Unexpected SQL Exception has occured. SQLCODE: '
|| SQLCODE
|| ' | SQLERRM: '
|| SQLERRM,
p\_display\_location => APEX\_ERROR.c\_inline\_in\_notification);
END;
EMP:
BEGIN
CASE :APEX$ROW\_STATUS
WHEN 'C'
THEN
BEGIN
INSERT INTO EMP (EMPNO,DEPTNO,ENAME) VALUES (:EMPNO,:DEPTNO,:ENAME) RETURNING ROWID INTO :ROW\_ID;
END;
WHEN 'D' THEN
BEGIN
DELETE EMP WHERE ROWID = :ROW\_ID;
END;
END CASE;
EXCEPTION
WHEN OTHERS
THEN
APEX\_ERROR.ADD\_ERROR (
p\_message =>
'An Unexpected SQL Exception has occured. SQLCODE: '
|| SQLCODE
|| ' | SQLERRM: '
|| SQLERRM,
p\_display\_location => APEX\_ERROR.c\_inline\_in\_notification);
END;
Here is how my auto-incrementing is working.
I tried using sequence for default value and that doesn't seem to work when you are using PL-SQL custom code for saving.
Please let me know if more detail is needed.