Curiousities of the returning clause

View All Scripts Login to Run Script

Script Name Curiousities of the returning clause
Description The returning clause allows you to retrieve information about the SQL statement just executed, without having to run a second SQL statement. This script explores some of the more interesting and less common ways to use returning.
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday August 09, 2021

Statement 1

create table earth_parts (  
   part_id    integer,  
   part_name  varchar2(100) 
)

Table created.

Statement 2

begin  
   insert into earth_parts values (100,  'Ocean' );  
   insert into earth_parts values (200,  'Mountain' );  
   insert into earth_parts values (300,  'Tree' );  
   commit;  
end;

Statement processed.

Statement 3

Call a SQL function in the returning clause

declare  
   l_name1  earth_parts.part_name%type;  
   l_name2  earth_parts.part_name%type;  
begin  
   select part_name  
     into l_name1  
     from earth_parts  
    where part_id = 200;  
  
   update earth_parts  
      set part_name = upper(part_name)  
    where part_id = 200 returning substr(part_name, 1, 5) into l_name2;  
  
   dbms_output.put_line(l_name1  
                        || '-'  
                        || l_name2);  
   rollback;  
end;

Statement processed.
Mountain-MOUNT

Statement 4

Of course, you can also do that *after* you get the value out of the returning clause!

Call a PL/SQL built-in function

declare
   l_name1  earth_parts.part_name%type;
   l_name2  earth_parts.part_name%type;
begin
   select part_name
     into l_name1
     from earth_parts
    where part_id = 200;

   update earth_parts
      set part_name = upper(part_name)
    where part_id = 200 
    returning 'Random! ' || to_char (dbms_random.value (1, length (part_name))) 
         into l_name2;

   dbms_output.put_line(l_name1
                        || '-'
                        || l_name2);
   rollback;
end;
/

Statement 5

create or replace function first_five ( 
   p_string in varchar2 
) return varchar2 is 
begin 
   return substr(p_string, 1, 5); 
end;

Function created.

Statement 6

Call a user defined function in the returning clause

declare  
   l_name1  earth_parts.part_name%type;  
   l_name2  earth_parts.part_name%type;  
begin  
   select part_name  
     into l_name1  
     from earth_parts  
    where part_id = 200;  
  
   update earth_parts  
      set part_name = upper(part_name)  
    where part_id = 200 returning first_five(part_name) into l_name2;  
  
   dbms_output.put_line(l_name1  
                        || '-'  
                        || l_name2);  
   rollback;  
end;

Statement processed.
Mountain-MOUNT

Statement 7

But not a nested function!

declare  
   l_name1  earth_parts.part_name%type;  
   l_name2  earth_parts.part_name%type;  
   function nested_first_five (  
      p_string in varchar2  
   ) return varchar2 is  
   begin  
      return substr(p_string, 1, 5);  
   end;  
begin  
   select part_name  
     into l_name1  
     from earth_parts  
    where part_id = 200;  
  
   update earth_parts  
      set part_name = upper(part_name)  
    where part_id = 200 returning nested_first_five(part_name) into l_name2;  
  
   dbms_output.put_line(l_name1  
                        || '-'  
                        || l_name2);  
   rollback;  
end;

ORA-06550: line 18, column 35:
PLS-00231: function 'NESTED_FIRST_FIVE' may not be used in SQL

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 8

Execute a query inside the returning clause

declare  
   l_name1  earth_parts.part_name%type;  
   l_name2  earth_parts.part_name%type;  
begin  
   update earth_parts ep  
      set part_name = upper(part_name)  
    where part_id = 200 returning (  
      select ep2.part_name  
        from earth_parts ep2  
       where part_id = 200  
   ),  
   part_name into l_name1, l_name2;  
  
   dbms_output.put_line(l_name1  
                        || '-'  
                        || l_name2);  
   rollback;  
end;

Statement processed.
Mountain-MOUNTAIN

Statement 9

create or replace function first_five return varchar2 is 
   l_return earth_parts.part_name%type; 
begin 
   select substr(part_name, 1, 5) 
     into l_return 
     from earth_parts 
    where part_id = 200; 
   return l_return; 
end;

Function created.

Statement 10

But that's the sort of thing that can happen when you call a function in SQL that in turn executes SQL!

Mutating table errors are possible!

declare  
   l_name1  earth_parts.part_name%type;  
   l_name2  earth_parts.part_name%type;  
begin  
   update earth_parts ep  
      set part_name = upper(part_name)  
    where part_id = 200 returning part_name,  
   (  
      select first_five  
        from dual  
   ) into l_name1, l_name2;  
  
   dbms_output.put_line(l_name1  
                        || '-'  
                        || l_name2);  
   rollback;  
end;

ORA-04091: table SQL_DYPJZFKLYREAHJVNSHUJXPRGP.EARTH_PARTS is mutating, trigger/function may not see it ORA-06512: at "SQL_DYPJZFKLYREAHJVNSHUJXPRGP.FIRST_FIVE", line 4
ORA-06512: at line 5
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-04091

Statement 11

Can bulk collect when changing more than one row

declare  
   type names_t is table of earth_parts.part_name%type  
      index by pls_integer;  
   l_names names_t;  
begin  
   update earth_parts ep  
      set part_name = upper(part_name)  
      returning part_name bulk collect into l_names;  
  
   dbms_output.put_line(l_names.count);  
   rollback;  
end;

Statement processed.
3

Statement 12

But the collection must be integer-indexed

declare  
   type names_t is table of earth_parts.part_name%type  
      index by varchar2(100);  
   l_names names_t;  
begin  
   update earth_parts ep  
      set part_name = upper(part_name)  
      returning part_name bulk collect into l_names;  
  
   dbms_output.put_line(l_names.count);  
   rollback;  
end;

ORA-06550: line 8, column 45:
PLS-00657: Implementation restriction: bulk SQL with associative arrays with VARCHAR2 key is not supported.

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 13

In some ways, forall is like insert-select (for example, the behavior for statement-level insert triggers). But you can use returning with forall, which you cannot do with insert-select.

Returning with FORALL - static or dynamic

create or replace type numlist is table of number
/

create or replace type namelist is table of varchar2 (15)
/

create table employees as select * from hr.employees
/

create or replace procedure update_emps (col_in      in varchar2
                                       , empnos_in   in numlist)
is
   enames   namelist;
begin
   forall indx in empnos_in.first .. empnos_in.last
      execute immediate
            'UPDATE employees SET '
         || col_in
         || ' = '
         || col_in
         || ' * 1.1 WHERE employee_id = :1
         RETURNING last_name INTO :2'
         using empnos_in (indx)
         returning bulk collect into enames;

   for indx in 1 .. enames.count
   loop
      dbms_output.put_line ('10% raise to ' || enames (indx));
   end loop;
end;
/

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database