CODEWITHSUNDEEP
sqloracle-databasestored-procedurescallexecute
user2858650
user2858650

Reputation:

Oracle SQL Stored Procedures Call vs. Execute

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

Upvotes: 26

Views: 38460

Answers (2)

Dima Korobskiy
Dima Korobskiy

Reputation: 1556

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();

Upvotes: 31

Jefferstone
Jefferstone

Reputation: 41

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

Upvotes: 1

Related Questions