26

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

  • Is one preferable over the other in terms of kicking off a stored procedure? Does it matter?
  • If it does matter, what is a situation where either is appropriate?
  • Are there any performance differences between the two? What's best practice?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

2 Answers2

30

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.

  1. 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).

  2. 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.

  3. EXEC could be used to execute not only an SP, but an arbitrary statement.

  4. If an SP does not have parameters, you can use EXEC SP; syntax, but CALL requires empty parentheses: CALL SP();

Dima Korobskiy
  • 1,479
  • 16
  • 26
  • I think the syntax where you can give the parameters of a proc as "named parameters" in arbitrary order is also available only with EXEC(UTE) and SQL*Plus. – Mirko Klemm Feb 23 '16 at 21:12
  • @MirkoKLemm Actually, it worked for me in `CALL` as well: `CALL r_mv (method => 'C', mv => 'core_project_appls');` for `CREATE OR REPLACE PROCEDURE r_mv( mv IN VARCHAR2, method IN VARCHAR2 := '', skip_index IN VARCHAR2 := '*NONE*')` – Dima Korobskiy Feb 24 '16 at 15:26
1

If you are calling a proc that returns a sys_refcursor using Toad, there is a difference between CALL and EXEC.

create procedure foo(i in number,o out sys_refcursor) as begin open o for select i from dual; end;

exec foo(1,:r); -- outputs 1 row

call foo(1,:r); -- outputs 0 rows

-- Note: when you prefix a parameter with a colon, Toad will prompt you for the type (which in this case is a cursor).

Jefferstone
  • 41
  • 1
  • 1
  • 3