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 onSQL> 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.
