Data Types

Oracle Database MLE brings together two rather different worlds: Oracle SQL and JavaScript. Both worlds support different types of data: for example, JavaScript represents text as values of type String and supports booleans, whereas the Oracle Database represents text as VARCHAR2 values and does not support booleans. To communicate between these two worlds, data from one world has to be converted into suitable data for the other world. Such conversion occurs whenever the database passes some values to JavaScript or when a user-defined extension written in JavaScript passes data back to the database. Oracle Database MLE comes with a set of pre-defined conversions. A user can influence the chosen conversion based on a call specification written in TypeScript.

Database Types

Oracle Database MLE supports these DB types:

  • NUMBER
  • BINARY_DOUBLE
  • VARCHAR2

The NUMBER type is a precise arithmetic type that supports a wide range of values:

  • Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits
  • Negative numbers from -1 x 10-130 to -9.99...99 x 10125 with up to 38 significant digits
  • Zero

Note that the range is smaller than the range of BINARY_DOUBLE, but all numbers within this range are precisely represented.

The BINARY_DOUBLE type corresponds to the usual IEEE 754 floating point data type and comes with the usual imprecision. This datatype supports infinite values and NaN which cannot be represented as NUMBER.

The VARCHAR2 type corresponds to character data. At the moment, Oracle Database MLE only supports the US ASCII character set.

REMARK: The Oracle Database does not distinguish between NULL and the empty string. Thus, every empty string is NULL and vice versa.

JavaScript Types

JavaScript contains a small range of data types:

  • Undefined
  • Null
  • Boolean
  • String
  • Symbol
  • Object

Oracle Database MLE does not support the types Undefined, Symbol, and Object. The only element of the Null type (the value null) represents the SQL NULL value.

The Number type is defined as IEEE 754 type that supports infinite values and NaN values. It suffers from the usual imprecision in IEEE 754 representation and arithmetics.

DB to JS Conversions

When passing a value from the database to a user-defined extension, Oracle Database MLE converts the value to the appropriate JavaScript type according to the following table. The source type in the table is the type of the value in the database. The target type is the result type in JavaScript.

Source Target
VARCHAR2 String
NUMBER Number
BINARY_DOUBLE Number

An exception to this conversion specification is the NULL value of the database. This is converted into the JavaScript value null. Since the Oracle Database treats the empty string as NULL, the empty string value inside the database will also be converted into null.

JS to DB Conversions

When returning a value from a user-defined extension in JavaScript, a user has to specify a return type in the call specification used to create the extension. Oracle Database MLE then converts the return value from JavaScript into appropriate database value. Since JavaScript is not strongly typed, Oracle Database MLE provides a set of implicit conversions from JavaScript values to database values. See the table below for details on the conversions. Every row in the table specifies a JavaScript type returned by a user-defined extension. The columns specify the target types as specified in the call specification.

NUMBER BINARY_DOUBLE VARCHAR2
Number direct, may overflow direct toFixed()
Boolean 0 or 1 0 or 1 TRUE or FALSE
String error error direct
null NULL NULL NULL
undefined error error error
Object error error error
Symbol error error error

The table shows all implicit conversions provided. direct specifies no implicit conversion since the types are (mostly) compatible. "may overflow" denotes a possible numeric overflow (ORA-1426) if the JS value is too big to be represented as an Oracle Number, error indicates an unsupported conversion (ORA-1460), NULL the conversion to SQL NULL value, and the other cases specify the conversion applied. See below for details.

Number to NUMBER

The conversion from JavaScript Number to Oracle Number is mostly direct. The exception (specified by the ovl marker) occurs when the value of a JavaScript Number represents a value outside of the range of an Oracle Number (see above). In this case, a numeric overflow (ORA-1426) is raised.

Number to BINARY_DOUBLE

The conversion from JavaScript Number to Oracle BINARY_DOUBLE is direct. No implicit conversion is needed.

Number to VARCHAR2

To convert a JavaScript Number to a VARCHAR2, we implicitly convert the number to a string as if the toFixed() function had been called on the number. If the string is longer than the precision of the VARCHAR2 it gets silently truncated.

Boolean to NUMBER or BINARY_DOUBLE

To convert a JavaScript boolean to a numeric value in the database, we transform the boolean into the usual 0 or 1 representation. Thus, e.g., the return value true will be turned into 1.

Boolean to VARCHAR2

To convert a JavaScript boolean into a VARCHAR2, we convert the boolean value true into the VARCHAR2 TRUE and the boolean value false into FALSE. If the string is longer than the precision of the VARCHAR2 it gets silently truncated.

String into NUMBER or BINARY_DOUBLE

There is no implicit conversion from JavaScript strings to numeric database types. Attempts of such a conversion will raise an ORA-1460 error.

String into VARCHAR2

No conversion is necessary here. If the string is longer than the precision of the VARCHAR2 it is silently truncated.

REMARK: The oracle database does not distinguish between NULL and the empty string. Thus, returning an empty string from a user-defined extension will result in a NULL value in the database.

null

The JavaScript null value is converted into the SQL NULL value. This happens independently of the datatype.

undefined, Object, Symbol

These types are not supported and will raise an ORA-1460 error.

Conversion Errors

Errors that occur during conversion are reported directly to the database. They cannot be caught in JavaScript code using try ... catch.