I just noticed that os_command.zip from old oracle white paper was lost, so i decided to post link to my old package, which is like os_command but with timeout parameter: http://github.com/xtender/xt_shell
I was hoping that if inline “with” functions are in the query, so their results will be consistent with it (as operators), but unfortunately such functions returns also inconsistent results as standalone pl/sql functions.
SQL> create table t as select 1 a from dual; Table created. SQL> declare 2 j binary_integer; 3 begin 4 dbms_job.submit( j 5 ,'begin 6 for i in 1..10 loop 7 dbms_lock.sleep(1); 8 update t set a=a+1; 9 commit; 10 end loop; 11 end;' 12 ); 13 commit; 14 end; 15 / PL/SQL procedure successfully completed. SQL> with 2 function f return int is 3 res int; 4 begin 5 dbms_lock.sleep(1); 6 select a into res from t; 7 return res; 8 end; 9 select 10 f 11 from dual 12 connect by level<=10; 13 / F ---------- 1 1 1 2 3 4 5 6 7 8 10 rows selected.
Interesting: Jonathan Lewis wrote that inline “deterministic” functions doesn’t use caching mechanism as standalone deterministic functions.
1. Don’t forget always add NO_DATA_FOUND exception handling, when you doing “select into” in code which can be called as from PL/SQL, as from SQL.
A little example:
Suppose we need to create a function, which would call some procedure:
create or replace procedure p_nested as a int; begin select 1 into a from dual where 1=0; end; / create or replace function f_no_data_found return varchar2 as begin p_nested; return 'ok'; end; /
When we call this function in PL/SQL, it will raise NO_DATA_FOUND and we will see it:
SQL> exec dbms_output.put_line(f_no_data_found); BEGIN dbms_output.put_line(f_no_data_found); END; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "XTENDER.P_NESTED", line 4 ORA-06512: at "XTENDER.F_NO_DATA_FOUND", line 3 ORA-06512: at line 1
But it doesn’t when we call it in SQL, because it’s normal for SQL: it’s just like a result of scalar subquery that returns nothing – NULL:
SQL> set null "NUL" SQL> col ndf format a10 SQL> select f_no_data_found ndf from dual; NDF ---------- NUL 1 row selected.
So if you want the function to behave the same way in PL/SQL and SQL, just add exception handling with reraising another exception or just return null.
It must be at the level of reflexes – “select into” → “exception when no_data_found”
Otherwise, later, when code become a big and difficult, you can get unstable hidden error.
2. Exceptions raised in a declaration section or in default parameters assigning will never be handled in exception section of the same level
Let’s take a look at a very simple example:
As you can see, there are two problems:
1. an exception was handled at higher level
2. the error backtrace does not show the call of the function “F”.
If the exception was caused in the declaration, we would see the “correct” backtrace, but exception would be still handled at higher level only:
Sometimes it’s not so dangerous, but last week I was investigating a complex case for this reason: one function when called in SQL throws strange exception, but in PL/SQL it works fine.
The exception was:
SQL> select PKG1.F(1,0,0,1275) from dual; select PKG1.F(1,0,0,1275) from dual * ERROR at line 1: ORA-06553: PLS-801: internal error 
And the function has many functions calls in default parameters initialization, so I couldn’t even find out which one contains a root problem.