Oracle Apex Middle Table Insert

Question

I have the following tables:- evaluations, evaluation_options and options. I am trying to create an evaluation and evaluation_option on one page.

To create the evaluation_option I will need evaluation_id after an evaluation is created. I am getting the option_id from a List of Value.

At this point, I am not sure how to get this done as I am new to PL-SQL & SQL.

Maybe this can help https://stackoverflow.com/questions/34811283/retrieve-oracle-last-inserted-identity — romeuBraga, Oct 31 '18 at 14:33

score 1 · Accepted Answer · answered Oct 31 '18 at 20:36

For this, I did a dynamic query to create both tables. I don't think this is the best way of getting the job done, I am open up to resolve this in the right way.

This is my code:-

    DECLARE
    row_id evaluations.id%TYPE;
    BEGIN

       INSERT INTO EVALUATIONS (class_student_rotations_id, strengths, 
       suggestions) VALUES (:P12_CLASS_STUDENT_ROTATIONS_ID, :P12_STRENGTHS, 
       :P12_SUGGESTIONS);

       SELECT id into row_id FROM EVALUATIONS WHERE ROWID=(select max(rowid) 
       from EVALUATIONS);

       INSERT ALL
         INTO evaluation_options (option_id, evaluation_id) VALUES 
       (:P12_APPLICATION_OF_BASICS, row_id)

       SELECT * FROM DUAL;

     END;

You could try with the example given on that link ... example: INSERT INTO my_table (my_column) VALUES (my_value) RETURNING my_id_column INTO some_variable; In this case you don't need a query to get the last id inserted. — romeuBraga, Oct 31 '18 at 21:17
Works better. Now to place exceptions just in case. Gratitude. — , Nov 01 '18 at 13:21

Oracle Apex Middle Table Insert

1 Answers1