SQLDeveloper VAR NUMBER

Just been tearing my hair out on some unexpected SQL behaviour in SQL Developer.
Eventually I managed to get to the root cause of the problem as illustrated below.
There is a silent failure which can cause unexpected results from your SQL.

SQL*Plus:

SQL> var ps number
SQL> exec :ps := 4001644945;

PL/SQL procedure successfully completed.

SQL> select :ps from dual;

       :PS
----------
4001644945

SQL>

SQL Developer v3.2 and v4.0 via “Run As Script(F5)”:

var ps number
exec :ps := 4001644945;
select :ps from dual;

anonymous block completed
       :PS
----------
         0 

Looks like SQL Developer is running into an integer problem and silently “dealing” with it.

var ps number
exec :ps := 2147483647
select :ps from dual;
exec :ps := 2147483648
select :ps from dual;

anonymous block completed
       :PS
----------
2147483647 

anonymous block completed
       :PS
----------
         0 

No such problem when you do a Run (F9) and use the bind dialog.
One to watch out for if you do a lot of sql execution via var like me.
Think I’ve seen it before. Think I might even have blogged about it before!

Advertisements

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: