Write an insert statement with select clause returning id in oracle

Question

I am trying to write a query as follows:

Eg:

 DECLARE 
 V_Output varchar(20):='';

 BEGIN
   INSERT INTO T(ID, MY_PK, NAME, VALUE)
   (SELECT ID, NEXT_TRAN_VALUE('T'), NAME, VALUE FROM T WHERE MY_PK = 'NO0000000000013');
   RETURNING MY_PK INTO V_Output;
   DBMS_OUTPUT.PUT(V_Output);
 END;

using below function

create or replace FUNCTION NEXT_TRAN_VALUE (field IN VARCHAR2)   RETURN 
VARCHAR2
IS
n_value   VARCHAR2 (20);
P_APR     VARCHAR2(3);

CURSOR rec_exists IS SELECT * FROM TRANSACTION_SEQ where SEQ_NAME = field ;      
jk_seq_rec  TRANSACTION_SEQ%ROWTYPE;   
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT LC_CODE into P_APR FROM LOCATION_CONFIG;
OPEN rec_exists;
FETCH rec_exists INTO jk_seq_rec;

IF rec_exists%FOUND THEN
    UPDATE TRANSACTION_SEQ SET curr_value =   curr_value + 1 WHERE SEQ_NAME = field;
    COMMIT;
END IF;

--SELECT curr_value INTO n_value FROM TRANSACTION_SEQ WHERE SEQ_NAME = field; 
END;

But, it shows error.

the select statement shall always return single statement to be inserted, so in my opinion it should return the my_pk of inserted row.

Answer 1

That won't work. RETURNING clause can't be used the way you're doing it, i.e.

insert into t (id, my_pk)
select some_id, your_function from ...
returning into v_output

but would work if you inserted VALUES, as

insert into t
values (id, your_function)
returning my_pk into v_output

It means that you'll either have to rewrite that code, or look at a workaround described in returning with insert..select article (written by Adrian Billington).

BTW, wouldn't an ordinary Oracle sequence suit your purpose? Won't be gapless, but would be simple & effective. Mind the performance when inserting huge amount of data, using your solution.

BTW #2, what's the purpose of the last line in your function? You never use N_VALUE.