Return the ID of an inserted row which uses subquery
Yet another sql (oracle) question.
I'm trying to return the ID of an inserted row, but there's a catch. The insert uses a subquery.
create or replace procedure NEW_FORECAST_PROFILE (CA_ID IN NUMBER, FP_ID OUT NUMBER) BEGIN INSERT INTO FORECAST_PROFILE( NAME, COMPANY_ACCOUNT_ID ) SELECT TO_CHAR(SYSDATE, 'MON-DD-yyyy HH:MM AM') AS NAME, COMPANY_ACCOUNT_ID FROM MASTER_DEFAULTS WHERE COMPANY_ACCOUNT_ID = CA_ID RETURNING FORECAST_PROFILE_ID INTO FP_ID; END;
What I'm trying to do:
1) Find a single row in one table and copy it to another table.
2) Return the new row's ID (FORECAST_PROFILE_ID), which was auto-incremented by a trigger/sequence combo.
I'm open to ideas. This doesn't work because I'm guessing the sql parser expects there to be multiple IDs returning.
I found something close but I need to convert to oracle syntax:
DECLARE @inserted_ids TABLE ([id] INT); INSERT INTO [dbo].[some_table] ([col1],[col2],[col3],[col4],[col5],[col6]) OUTPUT INSERTED.[id] INTO @inserted_ids VALUES (@col1,@col2,@col3,@col4,@col5,@col6)
** Update ** One (bad) way to solve the problem is to get the current value of the sequence used to generate the column. However, I would think this would run into problems if multiple users were updating the table.
FP_ID := FORECAST_PROFILES_SEQ.CURRVAL;
The problem here is that your select might return multiple rows. Maybe not now, but who knows who is going to butcher your schema in the future. Oracle doesn't bother to check whether company_account_id has a unique constraint and that the constraint is valid and enabled.
I would re-organise the statement as follows:
FOR r_profile IN ( SELECT TO_CHAR(SYSDATE, 'MON-DD-yyyy HH:MM AM') AS NAME, COMPANY_ACCOUNT_ID FROM MASTER_DEFAULTS WHERE COMPANY_ACCOUNT_ID = CA_ID ) LOOP INSERT INTO FORECAST_PROFILE ( NAME, COMPANY_ACCOUNT_ID ) VALUES ( r_profile.NAME, r_profile.COMPANY_ACCOUNT_ID ) RETURNING FORECAST_PROFILE_ID INTO L_FP_ID ; END; FP_ID := L_FP_ID;
You'd just need to declare your L_FP_ID local variable, although you could probably skip that altogether and use FP_ID. The downside is that if there were ever changes to the system such that the query DOES return more than one row, you'll only return the last id.