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;

Answers


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.


Need Your Help

Sublime Text / TextMate scope parameter

javascript syntax-highlighting textmate sublimetext color-scheme

Sublime Text (and TextMate) language and color-theme files interacts via scope parameter. It is a dotted name, that might be useful in styling (and other cases).

MVC5 EF Entity apparently saving, but is null when retrieved

c# entity-framework asp.net-mvc-5

I have an Account class that holds a list of Payments, which holds a list of Products that are Pending, Sold or Refunded.