How many Datatypes are in Oracle?

Datatypes are in Oracle

Each value that can be changed in an Oracle database has a datatype, which gives the value a fixed set of properties and affects how Oracle handles it. A datatype, which defines the acceptable values, must be specified for each column or argument when creating tables or methods. Oracle provides a number of built-in datatypes, including the most popular ones: VARCHAR2, NUMBER, DATE, and TIMESTAMP.

VARCHAR2

This datatype is a variable-length character string. Almost every character that can be entered on a computer keyboard, including numeric data, may be stored in this popular device.
Oracle Database 11g and below have a maximum length of 4000 bytes or characters for a VARCHAR2 column. However, a length of 32,767 bytes can be supported starting with Oracle Database 12c and beyond.
One important characteristic is that Oracle does not keep trailing blank spaces where VARCHAR2 columns terminate. If you define a VARCHAR2(50) column and store ‘SAN FRANCISCO’, for instance, it will use 13 spaces instead of 50. Nothing, not even a blank space, is stored by Oracle when a column is NULL (empty).
Syntax example: name VARCHAR2(30).
Recommendation: Oracle strongly recommends using VARCHAR2 over VARCHAR, as VARCHAR might be redefined in future versions.

NUMBER

The NUMBER datatype holds zero, infinity, “not a number” (NAN), and positive and negative fixed and floating-point numbers.
Its scale (s), or the number of digits to the right of the decimal point, and precision (p), or the total number of significant decimal digits, can be entered.
Scale s can vary from -84 to 127, and precision p can range from 1 to 38.
Syntax example: NUMBER(precision, scale). For instance, NUMBER(10,2) allows for up to 10 digits in total, with 2 digits after the decimal point (e.g., 99999999.99).

DATE

Date and time information is stored in the DATE datatype, resolved to the second. Hour, minute, second, date, year, month, and century are all included.
From January 1, 4712 BC to December 31, 9999 AD, the date range is valid.
It requires 7 bytes of storage and is a fixed-size datatype.
It is possible to modify the standard display format, which is usually DD-MON-YY (e.g., 01-MAY-04). The default time is midnight (00:00:00) if no time is entered during insertion.
Arithmetic with DATE: You can find the next day by adding 1 to a date. The number of days between two dates can be calculated by subtracting one from the other.

TIMESTAMP

An extension of the DATE datatype, TIMESTAMP stores values for the year, month, day, hour, minute, and second.
Its primary benefit is that it can store time with a far higher degree of accuracy—down to the billionth of a second, or fractional seconds.
Time zone-aware versions such as TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE are supported; upon retrieval, the time value is adjusted to the user’s session time zone.
Syntax example: TIMESTAMP(fractional_seconds_precision) (default precision is 6, valid values 0-9).

SQL*Plus Introduction

Oracle offers SQLPlus, a command-line tool that lets you work directly with the database. It functions as a client that communicates with the Oracle database via PL/SQL blocks and SQL statements, then shows the output. SQLPlus is used frequently by database administrators and developers since it is a feature that is always included in Oracle Database installations.

Despite the growing popularity of graphical user interfaces such as Oracle SQL Developer, SQL*Plus is still a useful tool for:

Running programs in PL/SQL and SQL statements.
Providing control over formatting, totals, column headings, and titles when creating reports.
Scripts for task automation.

Starting SQL*Plus and Logging In

At the command prompt on your operating system, you usually type sqlplus to launch SQL*Plus.

Example:

OS> sqlplus

SQL*Plus will then prompt you for your username and password to connect to the Oracle database.

Example of login:

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Nov 7 10:28:26 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Enter user-name: practice
Enter password: <your_password>
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
SQL>

The SQL> prompt will appear when SQL*Plus verifies that you are “Connected to Oracle” if you enter a valid login and password. Sysdba is one example of a privileged user that you may connect as for administrative tasks.

The Prompt and Basic Commands

The SQL> prompt means that SQLPlus is prepared to take your commands. SQL statements or commands unique to SQLPlus can be entered. As the SQL terminator, a semicolon (;) usually marks the end of a SQL query.

The following are some basic commands and how to use them:

SELECT: This function is used to obtain data from the database.
Frequently used for fast computations or function testing, the dual table is a compact, practical Oracle table with one row and one column.
DESCRIBE: The DESCRIBE command in SQL*Plus is used to show the column names and datatypes of a database object, like a table.
Take note of how SQL*Plus automatically sets a default width if it isn’t specified and formats the column heads to uppercase.
SET: This command lets you modify different environmental parameters by instructing SQLPlus on how to behave.
These commands change the width of the output line and the number of lines per page, respectively.
SHOW: This SQLPlus command shows the current value of a certain SQLPlus parameter.
The PAGESIZE setting you previously applied is confirmed.
COMMIT: A SQL command that makes permanent any changes (like INSERT, UPDATE, DELETE) made to the database since the last COMMIT or ROLLBACK.

SQL*Plus is a flexible tool that connects your instructions to the Oracle database, enabling efficient data management and querying. It’s similar to having a committed helper who efficiently completes tasks and comprehends your directions in a very systematic manner.

Page Content

Tutorials