Sunday, November 16, 2008

RETURNING INTO

While doing a quality check on new code, I encountered some row by row processing. This always triggers me to make a remark about rewriting the code to a single SQL, or at least bulk processing. But in this case, inside the loop, several local variables were used to do some counting. And at the end of the loop, a processing report was created to inform the end user about how many records had been processed, split out for several statusses. When rewriting it to a single SQL, you can use the SQL%ROWCOUNT to determine how many rows were processed, but if you want to split this number up per status, how to do that?

Here is an example, which doesn't look like the original code in any way, but it shows the same principle. First, let's create a table with some random statusses and create a procedure that creates a processing report in the dbms_output buffer:

rwijk@ORA11GR1> create table t (id,col,status)
2 as
3 select level
4 , '**********'
5 , cast(dbms_random.string('u',1) as varchar2(1))
6 from dual
7 connect by level <= 1000
8 /

Table created.

rwijk@ORA11GR1> select status
2 , count(*)
3 from t
4 where status in ('A','B','C','D','E')
5 group by status
6 order by status
7 /

S COUNT(*)
- ----------
A 38
B 42
C 37
D 39
E 29

5 rows selected.

rwijk@ORA11GR1> create procedure print_processing_report
2 ( p_count_a in number
3 , p_count_b in number
4 , p_count_c in number
5 , p_count_d in number
6 , p_count_e in number
7 )
8 is
9 procedure print_one_line
10 ( p_status in t.status%type
11 , p_count in number
12 )
13 is
14 begin
15 dbms_output.put_line
16 ( '* Total number of ' || p_status || '''s processed: ' ||
17 rpad(to_char(p_count),4) || '*'
18 );
19 end print_one_line
20 ;
21 begin
22 dbms_output.put_line('**************************************');
23 dbms_output.put_line('* P R O C E S S I N G R E P O R T *');
24 dbms_output.put_line('* ================================== *');
25 print_one_line('A',p_count_a);
26 print_one_line('B',p_count_b);
27 print_one_line('C',p_count_c);
28 print_one_line('D',p_count_d);
29 print_one_line('E',p_count_e);
30 dbms_output.put_line('**************************************');
31 end;
32 /

Procedure created.


And here is the code doing row by row processing:

rwijk@ORA11GR1> declare
2 cursor c_t
3 is
4 select t.status
5 from t
6 where t.status in ('A','B','C','D','E')
7 for update of t.status
8 ;
9 a pls_integer := 0;
10 b pls_integer := 0;
11 c pls_integer := 0;
12 d pls_integer := 0;
13 e pls_integer := 0;
14 begin
15 for r in c_t
16 loop
17 update t
18 set col = 'z'
19 where current of c_t
20 ;
21 case r.status
22 when 'A' then a := a + 1;
23 when 'B' then b := b + 1;
24 when 'C' then c := c + 1;
25 when 'D' then d := d + 1;
26 when 'E' then e := e + 1;
27 end case
28 ;
29 end loop
30 ;
31 print_processing_report(a,b,c,d,e);
32 end;
33 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 38 *
* Total number of B's processed: 42 *
* Total number of C's processed: 37 *
* Total number of D's processed: 39 *
* Total number of E's processed: 29 *
**************************************

PL/SQL procedure successfully completed.


When rewriting it to a single SQL statement, it is of course possible to execute another statement to retrieve the numbers per status, but that's an extra query, and a query that's executed at a different time, so you'd have to switch to a serializable isolation level, or use flashback technology to be accurate.

Or you can use aggregate functions in your returning into clause. When reading about the RETURNING INTO clause in the documentation, it talks about a "single_row_expression" being "An expression that returns a single row of a table." So it is not immediately obvious that an aggregate can be used. But it is allowed:

rwijk@ORA11GR1> rollback
2 /

Rollback complete.

rwijk@ORA11GR1> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 count(decode(status,'A',1))
13 , count(decode(status,'B',1))
14 , count(decode(status,'C',1))
15 , count(decode(status,'D',1))
16 , count(decode(status,'E',1))
17 into
18 a
19 , b
20 , c
21 , d
22 , e
23 ;
24 print_processing_report(a,b,c,d,e);
25 end;
26 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 38 *
* Total number of B's processed: 42 *
* Total number of C's processed: 37 *
* Total number of D's processed: 39 *
* Total number of E's processed: 29 *
**************************************

PL/SQL procedure successfully completed.


Needless to say, this is the fastest solution.

The funny thing though is that this exact same code doesn't work in Oracle10 (tested on 10.2.0.1 and 10.2.0.4):

rwijk@ORA10GR2> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 count(decode(status,'A',1))
13 , count(decode(status,'B',1))
14 , count(decode(status,'C',1))
15 , count(decode(status,'D',1))
16 , count(decode(status,'E',1))
17 into
18 a
19 , b
20 , c
21 , d
22 , e
23 ;
24 print_processing_report(a,b,c,d,e);
25 end;
26 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 190 *
* Total number of B's processed: 190 *
* Total number of C's processed: 190 *
* Total number of D's processed: 190 *
* Total number of E's processed: 190 *
**************************************

PL/SQL-procedure is geslaagd.


And I don't know why the above fails to produce the right numbers, but it does work on Oracle10g when using the sum aggregate function:

rwijk@ORA10GR2> rollback
2 /

Rollback is voltooid.

rwijk@ORA10GR2> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 sum(case status when 'A' then 1 else 0 end)
13 , sum(case status when 'B' then 1 else 0 end)
14 , sum(case status when 'C' then 1 else 0 end)
15 , sum(case status when 'D' then 1 else 0 end)
16 , sum(case status when 'E' then 1 else 0 end)
17 into a, b, c, d, e
18 ;
19 print_processing_report(a,b,c,d,e);
20 end;
21 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 36 *
* Total number of B's processed: 35 *
* Total number of C's processed: 41 *
* Total number of D's processed: 43 *
* Total number of E's processed: 35 *
**************************************

PL/SQL-procedure is geslaagd.


So yet another excuse less to write cursor for loops with DML inside.

***

Two other random nice-things-to-know about the returning into clause:

1) You can use an object type to "return into":

rwijk@ORA11GR1> create type my_object is object
2 ( col1 number
3 , col2 varchar2(1)
4 );
5 /

Type created.

rwijk@ORA11GR1> declare
2 o my_object;
3 begin
4 update t
5 set col = 'z'
6 where id = 123
7 returning my_object(t.id,t.status)
8 into o
9 ;
10 dbms_output.put_line(o.col1);
11 dbms_output.put_line(o.col2);
12 end;
13 /
123
T

PL/SQL procedure successfully completed.


2) The returning into clause doesn't work together with the "where current of" clause:

rwijk@ORA11GR1> declare
2 cursor c is select ename from emp for update of sal;
3 l_ename emp.ename%type;
4 begin
5 for r in c
6 loop
7 update emp
8 set sal = sal * 1.1
9 where current of c
10 returning ename into l_ename
11 ;
12 dbms_output.put_line(l_ename);
13 end loop;
14 end;
15 /
returning ename into l_ename
*
ERROR at line 10:
ORA-06550: line 10, column 12:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored


So you'll have to mimic the current of clause by selecting and using the rowid:

rwijk@ORA11GR1> declare
2 cursor c is select rowid, ename from emp for update of sal;
3 l_ename emp.ename%type;
4 begin
5 for r in c
6 loop
7 update emp
8 set sal = sal * 1.1
9 where rowid = r.rowid
10 returning ename into l_ename
11 ;
12 dbms_output.put_line(l_ename);
13 end loop;
14 end;
15 /
SMITH
ALLEN
WARD
JONES
MARTIN
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

5 comments:

  1. Did you try a rturning clause on a column involved in a trigger ?
    In some case, it is returning NULL even if it is obviously not null.
    Bug #7530137 - I can send you the cod if you want.

    ReplyDelete
  2. Hi Nicolas,

    Of course I didn't use database triggers: they are evil ;-)

    I looked at the bug report, and it doesn't seem to apply here. But I'm interested in seeing the code to reproduce that bug, so please send it.

    Regards,
    Rob.

    ReplyDelete
  3. On XE I got an ORA-3114 error using

    sum(decode(status,'A',1))
    but the right answer using
    sum(decode(status,'A',1,0))

    so it looks like a bug in what the decode function is returning when not matched. It SHOULD return null, but gets confused.

    Returning
    min(decode(status,'A',1))
    , min(decode(status,'B',1))
    I get a NULL for the B/C/D/E entries, suggesting the confusion is (partly) due to the multiple returns.

    ReplyDelete
  4. Hi Gary,

    Thanks for the comment.

    I checked with the min function as well, and I too get four NULL values and one 1 value. The status with the value 1 differs each run. And I checked if it was the status of the row with the lowest ID, but that wasn't the case. So I have no explanation yet. Ah well, no point in trying to understand buggy behaviour, just upgrade to 11g ;-)

    Regards,
    Rob.

    ReplyDelete