PL/SQL How return all attributes in ROW

Question

I don't know how I can return all attributes with the RETURNING clause

I want 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 * ?

Jodi

side note: it's possible in PostgreSQL - see #8821289 – Parallel

@filiprem: and in Postgres you don't even need a procedural block for that. It can be used in plain SQL which is much more convenient. – Valuer

Answer 1

It would be neat if we could do something like that 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 there may be a logged change request for this feature, because I know lots of people want it. But for the moment all we can do is the long-winded specification of every 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 a lot of columns!

I suspect the rationale is, most tables have relatively few derived columns (sequence assigned to an ID, sysdate assigned to a CREATED_DATE, etc) so most values should already be known (or at least knowable) to the inserting process.

edit

I was care how returning all attributes without long-winded specification of every column ;) Maybe it's impossible.

I thought I had made it clear, but anyway: yes currently it is impossible to use * or some similar unspecific mechanism in a RETURNING clause.

Pip

Thx for reply. I knew that your example is good but I was care how returning all attributes without long-winded specification of every column ;) Maybe it's impossible. Thanks for time – Jodi

Documentation states "returning_clause can retrieve column expressions" download.oracle.com/docs/cd/E11882_01/server.112/e10592/… * (or table_alias.*) is specific to SELECT download.oracle.com/docs/cd/E11882_01/server.112/e10592/… – Value

Answer 2

So far the best solution I believe could be:

DECLARE  
    v_user USER%ROWTYPE;
    rowid_v rowid;
 BEGIN  
     INSERT INTO User 
     VALUES (1,'Bill','QWERTY') 
     RETURNING ROWID INTO rowid_v; 

     SELECT * INTO v_user WHERE rowid = rowid_v ;
END;

https://oracle-base.com/articles/misc/dml-returning-into-clause

Unfortunately, still there is no such functionality as returning * or returning row :) but will be cool.

Patinous