CODEWITHSUNDEEP
sqlplsqloracle-sqldeveloperplsqldeveloperoracle19c
Julio
Julio

Reputation: 551

Returning id from an Insert into from select from select

Hello guys i need some help with this problem. I'm using this query inside a plsql:

  INSERT INTO HS_CPQ_COSTCHANNELDISCOUNT (CREATED_BY,UPDATED_BY,ORIGIN,PART_ID,CHANNEL_ID,DISCOUNTPERCENTAGE,EFFECTIVEDATE)
                Select M.CREATED_BY,M.UPDATED_BY,M.ORIGIN,P.ID,C.ID,M.DISCOUNTPERCENTAGE,M.EFFECTIVEDATE
                from (
                    select 'APEX_APP' CREATED_BY
                           ,'APEX_APP' UPDATED_BY
                           ,ORIGIN
                           ,part
                           ,channelid
                           ,REPLACE(DISCOUNTPERCENTAGE,'.',',') DISCOUNTPERCENTAGE
                           ,to_date(EFFECTIVEDATE,'YYYY-MM-DD HH24:MI:SS') EFFECTIVEDATE
                    from local_test_table 
                    where id = :tmp_ID
                ) m
                left outer join hs_mib_part p on (m.part = p.part)
                left outer join hs_mib_channel c on (m.channelid = c.CHANNELID)
                RETURNING id INTO new_id;

But i have the next error:

Informe de error -
ORA-06550: línea 128, columna 21:
PL/SQL: ORA-00933: comando SQL no terminado correctamente
ORA-06550: línea 113, columna 21:
PL/SQL: SQL Statement ignored

Why i can't return id from this insert?

How can i solve this part? - I need the new ID created

enter image description here

Best regards

Upvotes: 0

Views: 1386

Answers (2)

Shahed Mahmoudi
Shahed Mahmoudi

Reputation: 281

in older version of Oracle you can't directly read NEXTVAL / CURRVAL into var and have to do:

SELECT some_tab_seq.CURRVAL
INTO your_var
FROM DUAL;

but in new version you can fetch current value of the sequence by

temp_val := tab_seq.CURRVAL

Upvotes: 0

William Robertson
William Robertson

Reputation: 15991

returning into is only supported for single-row DML (insert values, not insert select):

documentation

You may be able to split the insert into

  1. select ... bulk collect into collection
  2. forall i in collection.first .. collection.last insert into hs_cpq_costchanneldiscount

where collection is a locally declared associative array or nested table variable matching your inserted columns.

Or for a single row, retrieve the values you want into a PL/SQL record before inserting.

Upvotes: 2

Related Questions