Using `SELECT` to call a function

Question

I occasionally encounter examples where SELECT...INTO...FROM DUAL is used to call a function - e.g.:

SELECT some_function INTO a_variable FROM DUAL;

is used, instead of

a_variable := some_function;

My take on this is that it's not good practice because A) it makes it unclear that a function is being invoked, and B) it's inefficient in that it forces a transition from the PL/SQL engine to the SQL engine (perhaps less of an issue today).

Can anyone explain why this might have been done, e.g. was this necessary in early PL/SQL coding in order to invoke a function? The code I'm looking at may date from as early as Oracle 8.

Any insights appreciated.

Answer 1

This practice dates from before PLSQL and Oracle 7. As already mentioned assignment was possible (and of course Best Practice) in Oracle7.

Before Oracle 7 there were two widely used Tools that needed the use of Select ... into var from dual;

On the one hand there used to be an Oracle Tool called RPT, some kind of report generator. RPT could be used to create batch processes. It had two kinds of macros, that could be combined to achieve what we use PLSQL for today. My first Oracle job involved debugging PLSQL that was generated by a program that took RPT batches and converted them automatically to PLSQL. I threw away my only RPT handbook sometime shortly after 2000.

On the other hand there was Oracle Forms 2.x and its Menu component. Context switching in Oracle Menu was often done with a Select ... from dual; I still remember how proud I was when I discovered that an untractable Bug was caused by a total of 6 records in table Dual.

I am sorry to say that I can not proof any of this, but it is the time of year to think back to the old times and really fun to have the answer.