Reputation: 441
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
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