Thursday, February 07, 2008

Scoping with SQL Types

The scoping rules for function calls are quite clear. Given a package with a function which has the same name as a standalone function, another function in that package will call the packaged function not the standalone one:

SQL> create or replace function toto
2 return varchar2
3 as
4 begin
5 return 'TOOTING';
6 end toto;
7 /

Function created.

SQL> create or replace package a as
2 function toto return varchar2;
3 function tata return varchar2;
4 end a;
5 /

Package created.

SQL> create or replace package body a as
2 function toto return varchar2
3 as
4 begin
5 return 'KANSAS';
6 end toto;
7 function tata return varchar2
8 as
9 begin
10 return 'We''re not in '||toto||' anymore';
11 end tata;
12 end a;
13 /

Package body created.

SQL> select a.tata from dual
2 /
TATA
-----------------------------------
We're not in KANSAS anymore

SQL>

The rules apply the same way if we're working with an object rather than a package ....

SQL> drop package a
2 /

Package dropped.

SQL> create or replace type a as object (
2 attr1 varchar2(20)
3 , member function toto return varchar2
4 , member function tata return varchar2
5 ) NOT FINAL;
6 /

Type created.

SQL> create or replace type body a as
2 member function toto return varchar2
3 as
4 begin
5 return attr1;
6 end toto;
7 member function tata return varchar2
8 as
9 begin
10 return 'We''re not in '||toto||' anymore';
11 end tata;
12 end;
13 /

Type body created.

SQL> set serveroutput on
SQL> declare
2 my_a a := new a('KANSAS');
3 begin
4 dbms_output.put_line(my_a.tata);
5 end;
6 /
We're not in KANSAS anymore

PL/SQL procedure successfully completed.

SQL>

However, there is a gotcha: the scoping rules do not work that way when our type inherits from a super-type....
 
SQL> create or replace type b under a (
2 overriding member function tata return varchar2
3 );
4 /

Type created.

SQL> create or replace type body b as
2 overriding member function tata return varchar2
3 as
4 begin
5 return 'We''re not in '||toto||' anymore!!!';
6 end tata;
7 end;
8 /

Type body created.

SQL> declare
2 my_b b := new b('KANSAS');
3 begin
4 dbms_output.put_line(my_b.tata);
5 end;
6 /
We're not in TOOTING anymore!!!

PL/SQL procedure successfully completed.

SQL>

The solution is quite straightforward: use the SELF keyword to make the scope explicit.
 
SQL> create or replace type body b as
2 overriding member function tata return varchar2
3 as
4 begin
5 return 'We''re not in '||SELF.toto||' anymore!!!';
6 end tata;
7 end;
8 /

Type body created.

SQL>
SQL> declare
2 my_b b := new b('KANSAS');
3 begin
4 dbms_output.put_line(my_b.tata);
5 end;
6 /
We're not in KANSAS anymore!!!

PL/SQL procedure successfully completed.

SQL>

I admit I am not clear about the rules for using SELF. Sometimes it is compulsory, sometimes it is optional. So it's just easier to always include it whenever we reference anything inside a type body.

NB: I ran these tests on 9.2.0.6, if that makes any difference.

No comments: