Oracle – How to call a pl/sql anonymous block from an pl/sql anonymous block


I have the following PL/SQL block and it's working fine. I would like to call this function (TimeToFrame) but from another PL/SQL block.

I cannot declare this function in a procedure or package that is stored in the DB. In other words how can I call a pl/sql from another pl/sql where both pl/sql are anonymous blocks??

What if I put that function in a separate .sql file. Can't I call that .sql file from my anonymous block and pass it some IN parameters and have that fct return OUT params?

nTime Number;

FUNCTION TimeToFrame(pTime IN Varchar2)
return NUMBER IS
select (SUBSTR(pTime, 1, 2) * 108000)+(SUBSTR(pTime, 4, 2) * 1800)+(SUBSTR(pTime, 7, 2)     * 30)+SUBSTR(pTime, 10, 2)
 into nTime
 from dual;
 return nTime;
END TimeToFrame;


Best Solution

Unfortunately, there is no way to achieve that. A nested subprogram declared in an anonymous block isn't even persisted after the anonymous block has been processed, so even when we ignore the scope limits, it doesn't exist when the other anonymous block is processed.

If you can't make it a stored function because the evil DBA doesn't allow that, you will have to copy-paste the function.

Related Question