Call vs Exec

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.

Advertisements

6 Responses to Call vs Exec

  1. “…So always use BEGIN and END; blocks in application code rather than CALL. …”

    I disagree, I like CALL.
    My conclusion here is rather:
    always think what you do !

  2. hourim says:

    Dom,

    I have been confronted to a real life situation where an external application developed with a language called Snapp was connecting to a 10gR2 oracle database and doing a begin package_name.procedure_name end without using bind variables. This was dramatically filling up the shared pool to death. When they asked me to find a quick work around, I suggested to set the cursor_sharing parameter to FORCE. Unfortunately, I realized that Oracle will not perform bind variables substitution (even when cursor sharing is set to force). In order for this substitution to occur we need to change the begin end by a call to the function CALL.

    I have summarized this issue here

    https://hourim.wordpress.com/2011/06/16/bind-variable-shared-pool-and-cursor-sharing-parameter/

    It is in fact the procedure parameters that can’t be replaced by a :SYS_B_00. Because a static SQL is always “auto binded” but not the procedure parameters

    This drawback is also pointed out in Jonathan Lewis CBO book

    I don’t know if this is still the case for the new release

    But of course, changing from a pl/sql call to a SQL call you will be threatened by those two errors no_data-found and two_many_rows.

    Best regards
    Mohamed
    http://www.hourim.wordpress.com

  3. Anonymous pl/sql block needs less memory:
    Test case: http://bit.ly/16DMCtx
    spool: http://bit.ly/1Cx4ZNG

    Also don’t forget that “call” can be used instead of trigger body and in this case you can get NO_DATA_FOUND: http://bit.ly/18PGz5R

    Best regards,
    Sayan Malakshinov
    http://orasql.org

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: