CODEWITHSUNDEEP
sqlplsqloracle-apex
Goku
Goku

Reputation: 441

Oracle-Apex process for insert into two tables

I have dialog page, with items from two tables.

I want to fill v_row_table2.fk_id with the value that P23_ID(identity column) will get

I use process for insert:

-- all variables are declared

IF :P23_ID IS NULL THEN
--INSERT

v_row.active                      := :P23_ACTIVE;
v_row.date                        := :P23_DATE;
v_row.start_time                  := :P23_START_TIME;
v_row.current_time                := :P23_CURRENT_TIME;
v_row_table2.logdate              := :P23_LOGDATE;
v_row_table2.filename             := :P23_FILENAME;
v_row_table2.fk_id                -- 


v_id  := pck_table.fn_ins_table_name(table_row => v_row);
v_id2 := pck_table2.fn_ins_table_name(table_row => v_row_table2);

I try to add:

SELECT identity_name.nextval
INTO v_fk_id
FROM dual;

And add v_fk_id into code but, It didn't work.

Insert function:

FUNCTION fn_ins_table_name (
        table_row IN table2%rowtype
    ) RETURN table2.table_id%TYPE 
    IS
        v_table2_id table2.table_id%TYPE;
    BEGIN
        INSERT INTO table2 VALUES table_row RETURNING 
        table_id INTO v_table2_id;  -- table_id is identity

        RETURN v_table2_id;
    END fn_ins_table_name;

Thanks in advance

Upvotes: 0

Views: 590

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18800

This isn't really an apex issue, but a pure pl/sql question. Your question is: "how do i get the generated value of a column as result of an insert". You can use the "RETURNING INTO" clause for that. It returns the value of a column into a variable. Plenty of examples everywhere of this clause.

You have not provided the description of your function fn_ins_table_name but it should return the newly generated id for that row. Your code would then look like this:

IF :P23_ID IS NULL THEN
--INSERT

v_row.active                      := :P23_ACTIVE;
v_row.date                        := :P23_DATE;
v_row.start_time                  := :P23_START_TIME;
v_row.current_time                := :P23_CURRENT_TIME;

v_id  := pck_table.fn_ins_table_name(table_row => v_row);

v_row_table2.logdate              := :P23_LOGDATE;
v_row_table2.filename             := :P23_FILENAME;
v_row_table2.fk_id                := v_id; 

v_id2 := pck_table2.fn_ins_table_name(table_row => v_row_table2);

Upvotes: 1

Related Questions