SQL avoid calling same function twice in select statement

Question

I have a select statement calling same function two times for returning two different columns, Since the same function is called twice, it's creating a performance issue.

I want to call function only once and copy its value to another column. Is it possible in oracle ?

SELECT ID
    ,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "BLOCK" 
    ,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "MASK"
    from table 
    where ID=condition;

Answer 1

You can use with clause to get data once

with block as (
select PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) as block 
from table
 where ID=condition;
) select ID, block.block as "BLOCK" , block.block as "MASK" from block 

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.