Does Oracle support RETURNING in an SQL statement?

user_76349 0
Viewed 315

PostgreSQL supports a RETURNING clause, for instance as in

UPDATE some_table SET x = 'whatever' WHERE conditions RETURNING x, y, z

and MSSQL supports a variant of that syntax with the OUTPUT clause.

However Oracle "RETURNING INTO" seems intended to placing values in variables, from within the context of a stored procedure.

Is there a way to have an SQL equivalent to the one above that would work in Oracle, without involving a stored procedure ?

Note: I am looking for a pure-SQL solution if there exists one, not one that is language-specific, or would require special handling in the code. The actual SQL is dynamic, the code that makes the call is database-agnostic, with only the SQL being adapted.

sql-returning oracle
2 Answers

Oracle does not directly support using the DML returning clause in a SELECT statement, but you can kind of fake that behavior by using a WITH function. Although the below code uses PL/SQL, the statement is still a pure SQL statement and can run anywhere a regular SELECT statement can run.

SQL> create table some_table(x varchar2(100), y number);

Table created.

SQL> insert into some_table values('something', 1);

1 row created.

SQL> commit;

Commit complete.

SQL> with function update_and_return return number is
  2      v_y number;
  3      --Necessary to avoid: ORA-14551: cannot perform a DML operation inside a query
  4      pragma autonomous_transaction;
  5  begin
  6      update some_table set x = 'whatever' returning y into v_y;
  7      --Necessary to avoid: ORA-06519: active autonomous transaction detected and rolled back
  8      commit;
  9      return v_y;
 10  end;
 11  select update_and_return from dual;
 12  /

UPDATE_AND_RETURN
-----------------
                1

Unfortunately there are major limitations with this approach that may make it impractical for non-trivial cases:

  1. The DML must be committed within the statement.
  2. The WITH function syntax requires both client and server versions of 12.1 and above.
  3. Returning multiple columns or rows will require more advanced features. Multiple rows will require the function to return a collection, and the SELECT portion of the statement will have to use the TABLE function. Multiple columns will require a new type for each different result set. If you're lucky, you can use one of the built-in types, like SYS.ODCIVARCHAR2LIST. Otherwise you may need to create your own custom types.
user_4091720

You can do it in SQL, not need to pl/sql, and this depends on your tool and/or language. Here's an example in sqlplus:

SQL> create table t0 as select * from dual;

Table created.

SQL> var a varchar2(2)
SQL> update t0 set dummy='v' returning dummy into :a;

1 row updated.

SQL> print a

A
--------------------------------
v
user_73986530
Related