In Oracle SQL, the reserved keyword CREATE can be used in conjunction with various database objects, such as TABLE, VIEW, INDEX, or FUNCTION, to make an SQL command that defines the specified object. Particularly, CREATE TABLE would be one of the most common usages of the CREATE keyword, often referred to as CREATE TABLE commands.
In this blog post, we will explore some "basic" table creations using the CREATE TABLE command and some available data types in Oracle SQL. Let's get started!
Create a Simple Table
By "basic," we mean defining table's name, column names, and their data types. For example:
Let's dissect the SQL command shown above. In the first line, we have the reserved SQL keyword CREATE followed by another reserved keyword TABLE. Combining these two, we have an SQL command to create a new TABLE object. Then, we have our chosen table name, my_table.
After specifying the table name, we have parentheses for the column definitions. Inside the parentheses, the CREATE TABLE command details the table's structure, specifying each column's name and data types. At the end, we need a semicolon to finish the statement.
Data Types in Oracle SQL
When creating a table object, it is required to assign specific data types to each column. The data types enforce which kind of data can be stored in each column. For example, in the SQL command lines shown above, column1 expects a numeric value to be entered, because its data type is specified as NUMBER. Data types also defines behavior when they are applied to a function or operations.
In Oracle SQL, data types can be broadly categorized into three main groups: numeric, character, and date. Let's explore each of these three categories.
Numeric Data Types
When dealing with numeric data in Oracle SQL, your machine wants to know how big the number is going to be. This is because a numeric data can occupy anywhere from 1 to 22 bytes of storage per value, significantly impacting overall database storage requirements. For example, let's say that you're storing a billion numbers requiring only 1 byte each. Then the total storage consumption would be a billion bytes. However, if each value required 22 bytes, the storage requirement would balloon to 22 billion bytes - a considerable difference.
To manage this, Oracle SQL employes the concepts of precision and scale. The precision determines the maximum number of significant digits a number can contain, including those on either side of the decimal point. On the other hand, scale specifically designates the number of digits allowed to reside after the decimal point.
NUMBER(p, s)
The NUMBER data type serves as the primary means of storing numeric data in Oracle Database. It is a versatile type that can handle both whole numbers and decimals, including zero, negative, and positive values. When defining a column with a NUMBER data type, you can specify the precision p and the scales s. For example:
| Data Type | Value Entered | Value Stored As |
|---|---|---|
| NUMBER | 1.23 | 1.23 |
| NUMBER(2) | 1.23 | 1 |
| NUMBER(5,2) | 1.45 | 1.45 |
| NUMBER(5,2) | 1.4567 | 1.46 |
| NUMBER(4,2) | 11.23 | 11.23 |
| NUMBER(4,1) | 11.2 | 11.2 |
| NUMBER(3,2) | 11.23 | Error ORA-01438 |
| NUMBER(5,-2) | 1059.34 | 1100 |
What happens if we attempt to input a value with a precision greater than the specification? In the table above, we see a case where we're trying to enter 11.23 into a column whose data type is NUMBER(3,2). By its definition, the maximum allowed number of digits for NUMBER(3,2) is 3 on either side of the decimal point, while it can store a value with a scale up to 2. Thus, when it encounters a value of 11.23, Oracle throws an error code ORA-01438, saying "Value larger than specified precision allowed for this column." This is because the value has a precision of 4 (1, 1, 2, 3 - four digits), but the NUMBER here is declared with a precision of 3.
On the other hand, if a value is entered that exceeds the declared scale, the entered value will be rounded off and accepted. For example, when we're trying to enter 1.4567 to a column defined by NUMBER(5,2), the database accepts the value since it meets the precision requirement. However, any scale beyond the second will be rounded off, resulting in a stored value of 1.46.
The NUMBER data type is inherently flexible. It adjusts its precision and scale based on the actual value stored. So, none of p or s are required; if omitted, SQL defaults to their maximum values. If only s is specified and p is omitted, s defaults to zero, indicating storage of whole numbers. The value for p can range from 1 to 38, while the value for s can range from -84 to 127.
Note: The acceptable value range for p and m does not represent the largest values the database can store; rather, they define the largest and smallest specifications for the values you can have.
Other Numeric Data Types
While the NUMBER(p, s) serves as the primary data type for representing numbers in Oracle, there are additional data types available for numerical values. Oracle supports alternatives such as INTEGER, INT, or SMALLINT, to maintain compatibility with other RDBMS and aligning with ANSI SQL conventions. For example, you might observe that in some contexts, the term DECIMAL is used interchangeably. DECIMAL has exactly the same meaning and syntax as NUMERIC; the only distinction is that NUMERIC is the official Oracle SQL data type, while DECIMAL is not.
FLOAT is another data type for numeric representation. Internally, Oracle represents FLOAT values as NUMBER values, but it does not resolve them in binary, meaning it cannot store the exact value of fractional numbers. While I personally do not recommend using these alternatives, having awareness of them can be beneficial when encountering various queries.
Character Data Types
CHAR(n) and NCHAR(n)
The CHAR data type represents fixed-length alphanumeric character values. It accommodates any character string as an input, where n indicates the length of the value. When declared as CHAR(n), data type ensures that the entered value is padded with blanks to match the specified length n. For example, if a column is declared with CHAR(10), a value like 'Andrew' would be stored and retrieved as 'Andrew ' - with 'Andrew' followed by four blank spaces:
Attempting to input a value longer than n results in an error, preventing input of the value. For example:
When declaring a CHAR data type, the specification of n is optional; if omitted, it defaults to 1. The maximum allowed value that you can specify for n is 2000 bytes or characters. Similar to CHAR, NCHAR is also a fixed length character data type. It stands out for Unicode encoding, ensuring accurate character representation across many languages.
VARCHAR2(n) and NVARCHAR(n)
The term VARCHAR2 is a shorthand for "variable character," signifying a variable-length alphanumeric value. The n denotes the maximum allowable length of the stored value. Unlike CHAR, VARCHAR2 does not pad its values with blanks, and its length dynamically adjusts based on the actual data it contains. For example:
Unlike CHAR, VARCHAR2 requires the specification of n, where the minimum value is 1 and the maximum allowable length is 4000.
The relationship between VARCHAR2 and NVARCHAR2 is exactly the same as CHAR and NCHAR. NVARCHAR2 is designed to accommodate Unicode character values, ensuring compatibility with non-English characters.
Date Data Types
In Oracle, there are several date and time-related data types available for storing and manipulating temporal information. Each date data type consists of fields, and each field is a component of a date or time, such as hours, minutes, or the month value, and so on.
| Datetime Field | Range of Valid Values |
|---|---|
| YEAR | -4712 to 9999 (excluding the year 0) |
| MONTH | 01 to 12 |
| DAY | 01 to 31 |
| HOUR | 00 to 23 |
| MINUTE | 00 to 59 |
| SECOND | 00 to 59.9(n) |
| TIMEZONE_HOUR | -12 to 14 |
| TIMEZONE_MINUTE | 00 to 59 |
| TIMEZONE_REGION | See TZNAME column of V$TIMEZONE_NAMES |
| TIMEZONE_ABBR | See TZABBREV column of V$TIMEZONE_NAMES |
DATE
As its name suggests, the DATE type in Oracle can store data values ranging from 4712 B.C. to 9999 A.D. Within a DATE data type, the stored fields encompass year, month, date, hour, minute, and seconds. Date literals are enclosed in a pair of single quotation marks and may be specified in many different formats.
To insert a value to a DATE type column, it should be formatted as 'Day-Month-Year' by default. If you try other formats, Oracle will throw an error:
In the United States, time strings are typically represented as 'Month/Day/Year', while in the United Kingdom and Europe, the format is 'Day/Month/Year'. The clearest representation of the time string is, in my opinion, is the ISO, which goes from the biggest to the smallest: 'Year-Month-Day'. To insert a date string value in a different format, we have to tell the computer that the string is an ISO date. For example:
TIMESTAMP(n)
TIMESTAMP is an extension of DATE value that adds fractional second precision. The value for n specifies the precision for fractional seconds, with a valid range from 0 to 9. In cases where n is omitted, it defaults to a value of 6. However, despite it only stores fractional seconds up to six decimal places, Oracle SQL displays all 9 decimals. For example:
Large Objects
In addition to the major three categories of the data types described above, Oracle SQL also have large objects, or LOBs. LOBs work very similar to other data types, allowing tables to contain multiple columns with LOB data. However, there are specific constraints and characteristics associated with LOBs: They cannot serve as primary keys and cannot be used in operations involving DISTINCT, GROUP BY, ORDER BY, or joins.
BLOB: Binary Large Object
BLOB is designed for storing large binary objects, such as image or video files. Declaration of BLOB does not require any precision or scale. The maximum size calculation involves several factors such as a starting size of 4GB, a parameter known as CHUNK, and the database block size setting.
CLOB: Character Large Object
CLOB is used for large text data elements. Similar to BLOB, its declaration doesn't involve precision or scale. The maximum size is calculated using a formular similar to that of the BLOB data type.
NCLOB
NCLOB is designed for storing CLOB data in Unicode. The maximum size calculation aligns with that of the BLOB data type. Unicode is a character set offering a more universal standard than ASCII, supporting major languages more comprehensively. Given its widespread adoption in databases and web technologies, its usage is recommended, especially in applications with global and multilingual requirements.
0 Comments