Pete Finnigan's Oracle Security Weblog

I like PL/SQL and I am always playing around with it or writing tools for use in security audits in PL/SQL or trying to do things that are not normal with PL/SQL such as writing an interpreter. One thing I found in some testing of PL/SQL recently is that the shorter the PL/SQL the faster it is parsed and compiled. This is common sense really; it should take 'x' seconds to compile a PL/SQL of 'y' length but if the length is 20% shorter it stands to reason it should parse and compile faster; because its shorter. Less code to read in and process, parse, less DIANA, less AST, less P-Code.

I was also thinking about the C language ternary operator where there is no IF statement but a short cut as follows:

(x==y)?"true":"false";

This is a single line IF/ELSE; without the IF/ELSE keywords. The condition is tested and if true the first part after the "?" is returned as the result of the statement or if the condition is false then the part after the ":"

Can we do similar in PL, no ternary operator in PL/SQL BUT we can as it happens use the condition in other places than an IF statement. So if I have a simple function to test if a character is ALPHA i.e. a-z or A-Z then it looks like this:

    function IsAlpha(pv_c in varchar2) return boolean is
        lv_ret boolean;
    begin    
        if(((ascii(pv_c)>=65) and (ascii(pv_c)<=90)) 
            or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122))) then
            lv_ret:=true;
        else
            lv_ret:=false;
        end if;       
        return(lv_ret);
    exception
        when others then
            dbms_output.put_line('Error');
    end;  

As you can see the simple function uses the ascii function to test the passed in character and then return TRUE or FALSE. I realised that we do not need the IF/ELSE/END IF as we can simply put the test condition in the return() directly and the function will then return TRUE or FALSE:

    function IsAlpha2(pv_c in varchar2) return boolean is
    begin    
        return(((ascii(pv_c)>=65) and (ascii(pv_c)<=90)) 
            or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122)));
    exception
        when others then
            dbms_output.put_line('Error');
    end;

This is now a much shorter version of the function producing the same result BUT without the IF/ELSE/END IF now. Great, we reduced the code, increased complexity slightly but made the code succinct.

Obviously we cannot do this change everywhere in all PL/SQL code. It works because the IF/ELSE/END IF returns just true or false. But it will also work not just in return but also in other places that take a condition.

The whole thing is here to prove it works:

set serveroutput on

declare
    lv_ret boolean;
    --
    function IsAlpha(pv_c in varchar2) return boolean is
        lv_ret boolean;
    begin    
        if(((ascii(pv_c)>=65) and (ascii(pv_c)<=90)) 
            or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122))) then
            lv_ret:=true;
        else
            lv_ret:=false;
        end if;       
        return(lv_ret);
    exception
        when others then
            dbms_output.put_line('Error');
    end;  
    --
    function IsAlpha2(pv_c in varchar2) return boolean is
    begin    
        return(((ascii(pv_c)>=65) and (ascii(pv_c)<=90)) 
            or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122)));
    exception
        when others then
            dbms_output.put_line('Error');
    end;
    --
begin
    lv_ret:=IsAlpha('A');
    dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
    lv_ret:=IsAlpha('0');
    dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
    lv_ret:=IsAlpha2('A');
    dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
    lv_ret:=IsAlpha2('0');
    dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));    
end;
/

And running gives:

SQL> @if
1
0
1
0

PL/SQL procedure successfully completed.

SQL>

The use of diutil.bool_to_int() is just to show the result. In the original use of this function it was embedded in another call so the IF/ELSE/END IF is truly removed

#oracleace #sym42 #plsql #securecode #oracle #security #extremeplsql #23ai #23c