NVARCHAR2 and NCHAR- Tables and Constraints

The NVARCHAR2 and NCHAR data types are useful if you have a database that was originally created with a single-byte, fixed-width character set, but sometime later you need to store multibyte character set data in the same database. You can use the NVARCHAR2 and NCHAR data types to support this requirement.
When the database is created you should plan for using a multibyte character set by default and be able to standardize with the use of VARCHAR2 and provide enough length to handle the multibyte characters.

Note VARCHAR2 by default can be 4,000 characters, and if you wanted to store more characters, the next choice is a CLOB.You can extend this to 32,767 characters in a VARCHAR2 or NVARCHAR2 data type by setting the MAX_STRING_ SIZE = EXTENDED.

Numeric

Use a numeric data type to store data that you will potentially need to use with mathematic functions, such as SUM, AVG, MAX, and MIN. You should never store numeric information in a character data type. When you use a VARCHAR2 to store data that is inherently numeric, you are introducing future failures into your system and inefficient queries. Eventually, you will want to report or run calculations on numeric data, and if they are not a numeric data type, you will get unpredictable results.
Oracle supports three numeric data types:

• NUMBER
• BINARY_DOUBLE
• BINARY_FLOAT

For most situations, you will use the NUMBER data type for any type of number data. Its syntax is NUMBER(scale, precision) where scale is the total number of digits, and precision is the number of digits to the right of the decimal point. So, with a number defined as NUMBER(5, 2), you can store values +/-999.99.

That is a total of five digit, with two used for precision to the right of the decimal point. If defined as NUMBER(5), the values can be to the right or left of the decimal with a total of five digits. This value will fit 2.4531, as would 55,555.

Tip Oracle allows a maximum of 38 digits for a NUMBER data type.This is almost always sufficient for any type of numeric application.

What sometime confuses DBAs is that you can create a table with columns defined as INT, INTEGER, REAL, DECIMAL, and so on. These data types are all implemented by Oracle with a NUMBER data type. For example, a column specified as INTEGER is implemented as a NUMBER(38).

The BINARY_DOUBLE and BINARY_FLOAT data types are used for scientific calculations. These map to the DOUBLE and FLOAT Java data types. Unless your application is performing rocket-science calculations, then use the NUMBER data type for all your numeric requirements.

JSON 

Previous versions of Oracle had procedures to be able to convert table data into JSON or read JSON into the database. The JSON can be put into the database tables with JSON columns. The schema or any other details about the JSON data does not need to be known, and it can be stored in the table with other data and queried using SQL. JSON is a new SQL and PL/SQL data type for JSON data and is optimized for query and DML processing.

The JSON data type uses a binary format, OSON. You can also store JSON in a different data type, VARCHAR2, with CLOB or BLOB. Then the data is textual and unparsed character data. There are performance reasons to use the right data type, including now using the JSON data type for JSON.

Here is an example to create a table with a JSON column:

You can also put a check constraint on JSON if inserting into a VARCHAR2 column:

With the support of the JSON data type, this means normal transactions, indexing, querying, and views are all simplified.