I tried this code in Oracle SQL Developer and return an error "an INTO clause is expected in this SELECT statement"

declare 
flag_id_header int;
begin

select NVM(MAX(A.ID),0) as VAL into flag_id_header from TBL_FUNDING_HEADER A;
flag_id_header := flag_id_header   1;

select flag_id_header, B.DATE_OF_TRANSACTION
from TBL_FUNDING_SOURCE B
;

end;

In Sql Server I can do this very easily

declare @var1 int = 1;

select @var1, col1, col2, col3 from tbl_whatever

I need this select before insert them into temporary table.

CodePudding user response：

Don't use MAX to find the next value in a sequence; if you have two users running concurrent statements then they will both get the same value and you will have duplicate entries.

Use a sequence.

CREATE SEQUENCE flag_id_header__seq;

Then increment the sequence and insert:

DECLARE
  v_id YOUR_TEMP_TABLE.COLUMN1%TYPE;
BEGIN
  -- Set the variable to the next sequence value.
  v_id := flag_id_header__seq.NEXTVAL;

  -- Use the sequence value in the insert.
  INSERT INTO your_temp_table (column1, column2)
  SELECT v_id,
         DATE_OF_TRANSACTION
  FROM   TBL_FUNDING_SOURCE;
END;
/

I think I forgot to mention that there are TBL_FUNDING_HEADER to be inserted first to get the ID (it is INT PK AUTO INCREMENT), and then I need the ID selected with TBL_FUNDING_SOURCE to be inserted into TBL_FUNDING as FK

Oracle does not support AUTO_INCREMENT; you need to use GENERATED ALWAYS AS IDENTITY.

If you want to insert into an IDENTITY column and then use that later, then use a RETURNING clause with the initial INSERT.

DECLARE
  v_id TBL_FUNDING_HEADER.ID%TYPE;
BEGIN
  INSERT INTO TBL_FUNDING_HEADER (column1)
  VALUES ('something')
  RETURNING id INTO v_id;

  -- Then use the returned identity value in the insert.
  INSERT INTO your_temp_table (column1, column2)
  SELECT v_id,
         DATE_OF_TRANSACTION
  FROM   TBL_FUNDING_SOURCE;
END;
/

fiddle