Call vs Exec
February 2, 2015 6 Comments
Just a reference to a really simple difference between CALL and EXEC.
I thought I had mentioned this before but couldn’t find it so…
EXEC/EXECUTE is a SQL*Plus command which wraps the proc call in an anonymous BEGIN … END; block.
CALL is a SQL command hence it is limited to SQL data types and there are other restrictions which the documentation sums up pretty well.
Because CALL is SQL, there is on key behavioural difference which caused a bug on a project a few years ago when Java code was calling a stored proc not with BEGIN … END; but with CALL and ended up swallowing certain exceptions:
SQL> l 1 create or replace procedure p1 2 as 3 begin 4 raise no_data_found; 5* end; SQL> / Procedure created. SQL> exec p1; BEGIN p1; END; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "PGPS_UAT1.P1", line 4 ORA-06512: at line 1 SQL> call p1(); Call completed. SQL>
SQL expects and handles certain exceptions.
So always use BEGIN and END; blocks in application code rather than CALL.