Zero-length character strings

The OpenEdge Oracle DataServer exhibits varying mappings for the zero-length character string when migrated from the OpenEdge database to the Oracle database. The following table summarizes these mappings:
Table 1. Zero-length string and its Oracle equivalents
ABL value Oracle equivalent
"" (zero-length string) Default behavior If -Dsrv PRGRS_MAP_EMPTY_NULL is used
" " (single space) NULL

Store zero-length character string as single-space character

In addition to accepting the unknown operator, Oracle assumes that all zero-length character strings are unknown and stores them as NULL. However, the OpenEdge Oracle DataServer inserts a zero-length character string as a single-space character when migrating data from an OpenEdge database to an Oracle database. Therefore, the Oracle database stores zero-length string values ("") as single-space characters (" ") and unknown values (?) as NULL. This default behavior of the DataServer allows OpenEdge applications to distinguish between the unknown value (?) and zero-length character strings.

When you use the Unknown value (?) in a WHERE clause with the DataServer, the Unknown value (?) satisfies only the equals (=) operator. Both of the following statements find the first customer record with the Unknown value (?) in the address2 field. Notice the space between the quotation marks in the first statement:

FIND FIRST customer WHERE customer.address2 = " ".
FIND FIRST customer WHERE customer.address2 = "".

Although "" and " " evaluate the same way in a WHERE clause, they have different results when you use them with the BEGINS function. For example, the following statement retrieves all customer names except those that have the Unknown value (?):

FOR EACH customer WHERE customer.name BEGINS "":

The following statement uses " " to retrieve customer records that have an empty, single, or multi-space string in their names:

FOR EACH customer WHERE customer.name BEGINS " ":

Because Unknown value (?), or values, satisfy only the equals condition, the following code does not retrieve customers with an Unknown value (?) in the address2 field:

FOR EACH customer WHERE customer.address2 <> "foo":
  DISPLAY customer.name.
END.

The following statement is not meaningful to Oracle. It generates the error, "Illegal operator for Unknown value (?) or zero-length character string":

FIND FIRST customer WHERE customer.address2 < ?.

This restriction has been relaxed for columns of the DATE data type, as shown in the following statement:

FIND FIRST order WHERE order.orderdate < ?.

Store zero-length character string as NULL

The PRGRS_MAP_EMPTY_NULL option of the DataServer (-Dsrv) startup parameter instructs the DataServer to map a zero-length string in an OpenEdge database to a NULL value in an Oracle database. The syntax for the PRGRS_MAP_EMPTY_NULL option is:
CONNECT <dbname> -U <username> -P <password> -Dsrv [PRGRS_MAP_EMPTY_NULL], 
qt_debug,<option>[,qt_debug,<option>...]
Note: To preserve backward compatibility, the default configuration does not include the PRGRS_MAP_EMPTY_NULL option, because it may cause problems with the existing Oracle DataServer applications. To utilize this functionality, you do not need to recompile your applications with ABL virtual machine (AVM) that is OpenEdge release 12.8 or later. You need to provide the PRGRS_MAP_EMPTY_NULL option only. The absence of this option activates the default behavior.
When using the PRGRS_MAP_EMPTY_NULL option, the following statement sets the value of the char field of the tab table to a zero-length character string. The Oracle database then converts this zero-length character string to NULL. 
ASSIGN tab.char=""
When retrieving the zero-length character string from the Oracle database, the following code fetches and displays all the null values for the char field:
FOR EACH tab WHERE char="": 
   DISPLAY char. 
 END.
However, the single-space character strings are stored unmodified into the Oracle database after executing the following statement:
ASSIGN tab.char=" "
When retrieving the single-space character strings from the Oracle database, the following code retrieves and displays all the single-space character strings for the char field:
FOR EACH tab WHERE char=" ": 
   DISPLAY char. 
 END.
Note: When using the PRGRS_MAP_EMPTY_NULL option, running a statement with WHERE clause to retrieve all the single-space character strings fetches only the single-space character strings. However, in the absence of this option, the same statement retrieves both, zero-length character strings and single-space character strings. This is also the default behavior of the DataServer.

The BEGINS operator functions differently when you utilize the PRGRS_MAP_EMPTY_NULL option.

For example, using BEGINS with zero-length character strings results in an error: 
FOR EACH customer WHERE customer.NAME BEGINS "": 
   DISPLAY customer.NAME. 
 END.
However, the following statement uses BEGINS with single-space character strings and retrieves only the records where customer names begin with single-space strings:
FOR EACH customer WHERE customer.NAME BEGINS " ": 
   DISPLAY customer.NAME. 
 END.

For more information on the differences in DataServer functionality with and without the PRGRS_MAP_EMPTY_NULL option, see the DataServer default vs. the PRGRS_MAP_EMPTY_NULL option table .

DataServer default vs. the PRGRS_MAP_EMPTY_NULL option

The following table compares the varying mappings of a zero-length character string to either a single-space character string (default behavior) or NULL, depending on whether the PRGRS_MAP_EMPTY_NULL option is utilized:
Table 2. DataServer default vs. the PRGRS_MAP_EMPTY_NULL option
Use case ABL query example Default behavior If -Dsrv PRGRS_MAP_EMPTY_NULL is used
Insert a zero-length character into the char field of the tab table. 
ASSIGN tab.char="".
 The Oracle DataServer inserts a single-space character value of the char field into the Oracle database.  Then displays a zero-length character string when retrieving this record. The Oracle DataServer converts the zero-length character value of the char field to NULL. Then displays an unknown value (?) when retrieving this record.
Insert a single-space character into the char field of the tab table 
ASSIGN tab.char=" ".
The Oracle DataServer inserts a single-space character value of the char field into the Oracle database. The Oracle DataServer inserts a single-space character value of the char field into the Oracle database.
Retrieve and display records with zero-length characters in the char field of the tab table using the WHERE clause 
FOR EACH tab WHERE char="": 
  DISPLAY char. 
END.
The Oracle DataServer retrieves all single-space character values and converts them into zero-length characters. Then displays the converted values. The Oracle DataServer retrieves all records with null values and displays them as unknown values (?).
Retrieve and display records with single-space character values in the char field of the tab table using the WHERE clause 
FOR EACH tab WHERE char=" ": 
 DISPLAY char. 
END.
The Oracle DataServer displays all single-space and zero-length character values. The Oracle DataServer retrieves and displays all single-space character values.
Retrieve and display records with zero-length character strings in the address field of the customer table using the MATCHES operator and the WHERE clause 
DEF var nullch as CHAR.
nullch = ''.
FOR EACH customer 
WHERE address MATCHES nullch:
  DISP cust-num 
  NAME 
  LENGTH(address).
END.
The Oracle DataServer displays the customer numbers and names of all customers whose address field contains zero-length, single-space, or multi-space character strings.

The Length() function returns the exact length of multi-space character strings but an empty result set for single-space and zero-length character strings in the address field.

 The Oracle DataServer displays the following error:

Illegal operator for unknown value or zero-length character string.(1457)
Retrieve and display records with single-space character strings in the address field of the customer table using the MATCHES operator and the WHERE clause 
DEF var nullch as CHAR.
nullch = ' '.
FOR EACH customer 
WHERE address MATCHES nullch:
  DISP cust-num 
  NAME 
  LENGTH(address).
END.
The Oracle DataServer displays the customer numbers and names of all customers whose address field contains zero-length and single-space character strings.

The Length() function returns an empty result set for single-space and zero-length character strings in the address field.

 The Oracle DataServer displays the customer numbers, names, and address lengths of all customers whose address field contains single-space character strings.
Retrieve and display records with multi-space character strings in the address field of the customer table using the MATCHES operator and the WHERE clause 
DEF var nullch as CHAR.
nullch = '   '.
FOR EACH customer 
WHERE address MATCHES nullch:
  DISP cust-num 
  NAME 
  LENGTH(address).
END.
The Oracle DataServer displays the customer number, names, and address length for all customers whose address field contains multi-space character strings. The Oracle DataServer displays customer numbers, names, and address lengths of all customers whose address field contains multi-space character strings.
Retrieve and display records with NULL (?) character strings in the address field of the customer table using the MATCHES operator and the WHERE clause 
DEF var nullch as CHAR.
nullch = ?.
FOR EACH customer 
WHERE address MATCHES nullch:
  DISP cust-num 
  NAME 
  LENGTH(address).
END.
The Oracle DataServer displays the following error:

Illegal operator for unknown value or zero-length character string.(1457)

 The Oracle DataServer displays the following error:

Illegal operator for unknown value or zero-length character string.(1457)
Retrieve and display records with zero-length character strings in the address field of the customer table using the BEGINS operator and the WHERE clause 
DEF var nullch as CHAR.
nullch = ''.
FOR EACH customer 
WHERE address BEGINS nullch:
  DISP cust-num 
  NAME 
  LENGTH(address).
END.
The Oracle DataServer displays the customer numbers, names, and address lengths of all customers. The Oracle DataServer displays the following error:

Illegal operator for unknown value or zero-length character string.(1457)
Retrieve and display records with single-space character strings in the address field of the customer table using the BEGINS operator and the WHERE clause 
DEF var nullch as CHAR.
nullch = ' '.
FOR EACH customer 
WHERE address BEGINS nullch:
  DISP cust-num 
  NAME 
  LENGTH(address).
END.
The Oracle DataServer displays the customer numbers and names of all customers whose address field contains zero-length, single-space, or multi-space character strings.

The Length() function returns the exact length of multi-space character strings but an empty result set for single-space and zero-length character strings in the address field.

 The Oracle DataServer displays the customer numbers, names, and address lengths of all customers whose address field contains single-space character strings.
Retrieve and display records with multi-space character strings in the address field of the customer table using the BEGIN operator and the WHERE clause 
DEF var nullch as CHAR.
nullch = '   '.
FOR EACH customer 
WHERE address BEGINS nullch:
  DISP cust-num 
  NAME 
  LENGTH(address).
END.
The Oracle DataServer displays the customer number, names, and address lengths of all customers whose address field contains multi-space character strings. The Oracle DataServer displays the customer numbers, names, and address lengths of all customers whose address field contains multi-space character strings.
Retrieve and display records with NULL (?) character strings in the address field of the customer table using the BEGIN operator and WHERE clause 
DEF var nullch as CHAR.
nullch = ?.
FOR EACH customer 
WHERE address BEGINS nullch:
  DISP cust-num 
  NAME 
  LENGTH(address).
END.
The Oracle DataServer displays the following error:

Illegal operator for unknown value or zero-length character string.(1457)

 The Oracle DataServer displays the following error:

Illegal operator for unknown value or zero-length character string.(1457)
Perform a table join between tab1 and tab2 using the WHERE clause 
FOR EACH tab1, 
EACH tab2 
WHERE tab2.char1="" 
BY tab2.char1:  
  DISPLAY tab1.char1 
  tab2.char1. 
END.
The Oracle DataServer retrieves all single-space character values for the tab2.char1 field and converts them into zero-length characters. Then performs an inner join with the tab1.char1 field using the retrieved result set. The Oracle DataServer retrieves all null values of tab2.char1 values and then performs an inner join with the tab1.char1 column using the retrieved result set.