PL/SQL block abnormal termination

In OTN discussion forum it has been asked when a PL/SQL block got terminated abnormally what will happen?

couple of experiments have been done on this issue to understand it clearly.

C:\> sqlplus scott/tiger

SQL> select * from dept;


DEPTNO DNAME LOC
---------- -------------- -------------
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Table dept1 has the structure as that of dept.

SQL> ed
Wrote file afiedt.buf

1 declare
2 begin
3 insert into dept1 select * from dept;
4 dbms_lock.sleep(20); -- sleeping for 20 secs
5 insert into dept1 select * from dept;
6 dbms_lock.sleep(20); -- sleeping for 20 secs
7 insert into dept1 select * from dept;
8* end;
SQL>/

While the above code was running the session has been terminated abnormally. Immediately the v$session view was checked as a DBA. It has shown active session for user SCOTT. When the block execution finished v$session result in no session for SCOTT. Select * from dept1 result in no rows for the table dept1 when fetched from a new session for SCOTT.

Again the same experiment was done with the following PL/SQL block..

SQL> ed
Wrote file afiedt.buf

1 declare
2 begin
3 insert into dept1 select * from dept;
4 dbms_lock.sleep(20);
5 insert into dept1 select * from dept;
6 dbms_lock.sleep(20);
7 insert into dept1 select * from dept;
8 commit;
9* end;
SQL>/


when fetched from dept1 the following output was found.

SQL> select * from dept1;

DEPTNO DNAME LOC
---------- -------------- -------------
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT LONDON

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

15 rows selected.

SQL>

Conclusion: PL/SQL block will continue to execute upon abnormal termination. Only committed execution will be preserved and uncommitted execution will be purged out.

Behavior of Boolean Datatype in SQL and PL/SQL

Oracle provides boolean datatype for PL/SQL. Boolean datatype can house three literals i.e, TRUE, FALSE and NULL. The main rationale for coming with such datatype was to evaluate comparison expressions.

The value of boolean datatype variable cannot be printed using DBMS_OUTPUT.PUT_LINE


SQL> declare
2 b_variable boolean;
3 begin
4 b_variable:=true;
5 dbms_output.put_line(b_variable);
6 end;
7 /
dbms_output.put_line(b_variable);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

A simple if statement can act as a remedy for the above code.

SQL> declare
2 b_variable boolean;
3 begin
4 b_variable:=true;
5 if (b_variable) then
6 dbms_output.put_line('true');
7 else
8 dbms_output.put_line('false');
9 end if;
10 end;
11 /
true

PL/SQL procedure successfully completed.

SQL>


Functions returning boolean data cannot be used in select statements.

SQL> create or replace function is_it_equal_2_oracle(instring varchar2)
2 return boolean is
3 begin
4 if (instring='oracle') then
5 return true;
6 else
7 return false;
8 end if;
9 end;
10 /

Function created.

SQL> select is_it_equal_2_oracle('sqlserver') from dual;
select is_it_equal_2_oracle('sqlserver') from dual
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type


The same function can be used in PL/SQL as follows.


1 declare
2 database char(10) :='sqlserver';
3 begin
4 if is_it_equal_2_oracle(database) then
5 dbms_output.put_line('Yes it is oracle');
6 else
7 dbms_output.put_line('No it is not equal to oracle');
8 end if;
9* end;
SQL> /
No it is not equal to oracle

PL/SQL procedure successfully completed.

SQL>

People might confuse with the following SQL statement.

SQL> select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from dual;

As in SQL there is no boolean datatype, true will not be identified by SQL. Output is as follows.

SQL> select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from d
ual;
select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from dual
*
ERROR at line 1:
ORA-00904: "TRUE": invalid identifier


Conclusion: Boolean datatype is exclusive for PL/SQL. A care should be taken while doing implementation.

Power of Oracle's NVL and Decode function

Suppose I have the following data in a table as shown below. R_ID is related to ID.
A
.....B
.....C
.....D
E
.....F
.....G
.....H
I
.....J
.....K
.....L
.....M

SQL> select * from example order by ID;

ID D R_ID
---------- - ----------
1 A
2 E
3 I
4 B 1
5 C 1
6 D 1
7 F 2
8 G 2
9 H 2
10 J 3
11 K 3
12 L 3
13 M 3

It has been asked to order by both ID and R_ID. The following output is required.


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

There are may ways to achieve this from which two are as follows.

1) Decode function

SQL> select id,details D,decode(r,id,null,r) R_ID
2 from
3 (select id,details,decode(r_id,null,id,r_id) r from example)
4 order by r,id


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

In the inner query null value of R_ID is being replaced by corresponding ID column value. In the outer query reverse is being done. If ID and r are equal then null is assigned.

2) NVL function


SQL> select * from example
2 order by nvl(r_id,id),id


ID D R_ID
---------- - ----------
1 A
4 B 1
5 C 1
6 D 1
2 E
7 F 2
8 G 2
9 H 2
3 I
10 J 3
11 K 3

ID D R_ID
---------- - ----------
12 L 3
13 M 3

This method is very simple. The null values of R_ID are being replaced by ID value and then these are used for ordering purpose.


Second method is efficient than first one as nvl is being called only once while in the first method decode function is being called two times. I found this ordering to be interesting.

References:
1) Decode function
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions33a.htm#1017439
2) NVL function
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions88a.htm#91645