A Winning Combination: Apex on VM

Leave a comment

I’m working with the following combination for an internal Oracle Apex deployment that will have a limited number of users.

  • Windows 2008 64 bit on VMWare
  • Oracle Database 11.2.0 R2 64 bit
  • Oracle Apex 3.2.1.00.12
  • Oracle HTTP Server 11.1.1.2

Response time thus far is fine. Deploying on VM saves money on hardware and should be able to meet performance requirements for the intended usage of this system.

Empty strings are null in Oracle

3 Comments

I didn’t realize that Oracle pl/sql and sql treat varchar2 empty strings as null values. Couldn’t figure out why an IF statement was returning a particular value. Apparently, this is common knowledge, see
Jeff Kemp and Paweł Barut on the topic.

Furthermore, within PL/SQL, you can assign an empty string to a variable, and then a test won’t recognize it.

Before I got to those sites, I had cooked up a similar test in SQL, see below. Pawel’s blog shows that PL/SQL will treat CHAR empty strings as NOT NULL, which appears to still be in the case in 11GR2 when I re-ran his snippet. So things are still inconsistent…

CHAR and VARCHAR2 in SQL

SQL> create table foo (f1 char(3), f2 varchar2(3));

Table created.

SQL> insert into foo values ('HEY', 'SAY');

1 row created.

SQL> insert into foo values (null, null);

1 row created.

SQL> insert into foo values ('', '');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo where f1 is not null;

F1  F2
--- ---
HEY SAY

1 row selected.

SQL> select * from foo where f2 is not null;

F1  F2
--- ---
HEY SAY

1 row selected.

SQL> select * from foo where f2 is null;

F1  F2
--- ---



2 rows selected.

SQL> select * from foo where f1 is null;

F1  F2
--- ---



2 rows selected.

SQL>

Here are the pl/sql examples I tried.

SQL> edit
Wrote file afiedt.buf

  1  declare
  2  my_var char(2);
  3  begin
  4  my_var := '';
  5  if my_var = '' then
  6  dbms_output.put_line ('An EMPTY STRING!');
  7  else
  8  dbms_output.put_line ('What is it?');
  9  end if;
 10* end;
SQL> /
What is it?

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

  1  declare
  2  my_var varchar2(2);
  3  begin
  4  my_var := '';
  5  if my_var = '' then
  6  dbms_output.put_line ('An EMPTY STRING!');
  7  else
  8  dbms_output.put_line ('What is it?');
  9  end if;
 10* end;
SQL> /
What is it?

PL/SQL procedure successfully completed.