Problem
I'm trying to understand the difference between Oracle SQL commands CALL
and EXECUTE
.
I've been using CALL
to kick off stored procedures but in talking with another developer I found that he almost exclusively uses EXECUTE
. I did some research online to see if I was doing something incorrectly but I'm not seeing the clear distinction between the two commands and people seem to use them interchangeably.
Based on the documentation, they seem remarkably similar (at least in terms of interacting with stored procedures).
It does look like CALL
is a universal SQL command while EXECUTE
seems to be proprietary so I would be inclined to use CALL
over EXECUTE
but then again I don't know what that means in regards to performance.
Questions
Both EXEC[ute] SP()
and CALL SP()
could be used in SQL*Plus to execute an SP. BTW, you can also use BEGIN SP(); END;
But there are some differences.
CALL
is Oracle SQL and should work everywhere. Other DB clients that can talk to Oracle may or may not support SQL*Plus EXEC. Many do (for example, Oracle SQL Developer, SQLWorkbench/J), but some don't (Liquibase).
The data types of the parameters passed by the CALL
statement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN.
EXEC
could be used to execute not only an SP, but an arbitrary statement.
If an SP does not have parameters, you can use EXEC SP;
syntax, but CALL
requires empty parentheses: CALL SP();