PL / SQL How to return all attributes in ROW

Question

I do not know how I can return all attributes with a RETURNING clause

I need something like this:

DECLARE v_user USER%ROWTYPE BEGIN INSERT INTO User VALUES (1,'Bill','QWERTY') RETURNING * INTO v_user; END; 

RETURNING * INTO gets an error, how can I replace * ?

Answer 1

It would be great if we could do something like this, but alas:

 SQL> declare 2 v_row t23%rowtype; 3 begin 4 insert into t23 5 values (my_seq.nextval, 'Daisy Head Maisy') 6 returning * into v_row; 7 end; 8 / returning * into v_row; * ERROR at line 6: ORA-06550: line 6, column 19: PL/SQL: ORA-00936: missing expression ORA-06550: line 4, column 5: PL/SQL: SQL Statement ignored SQL> 

I believe that there may be a registered change request for this function, because I know that many people want this. But for now, all we can do is a long specification of each column:

 SQL> declare 2 v_row t23%rowtype; 3 begin 4 insert into t23 5 values (my_seq.nextval, 'Daisy Head Maisy') 6 returning id, person_name into v_row; 7 end; 8 / PL/SQL procedure successfully completed. SQL> 

Bad news if you have many columns!

I suspect this is a rationale: most tables have relatively few derived columns (the sequence assigned to the identifier, sysdate assigned to CREATED_DATE, etc.), so most values ​​should already be known (or at least known) for insertion process.

change

I didn’t care how to return all the attributes without the long specification of each column;) Perhaps this is not possible.

It seemed to me that I made it clear, but in any case: yes, it is currently impossible to use * or some similar non-specific mechanism in the RETURNING clause.