Skip to main content

Data types in oracle SQL with examples

Data Types in Oracle SQL with examples

Data Types in Oracle SQL is used to define the operations that can be used to any variable or any other parameters or any columns.

Data type in oracle Sql and PLSQL


List of Data Types in oracle SQL

The below list of important data types in oracle sql are given below :-
  1. Character Data types
  2. Numerical data types
  3. long objects
  4. Date and time data types

Character Data Types

Character Data type is used for string based objects are variables, alphanumeric characters. And this Character Data type can be used in different data type.
  1. CHAR
  2. VARCHAR
  3. VARCHAR2

CHAR

  • Char means Character, it is a data type that used in string of any variables or objects.
  • It is a static data type, so the size of the data type is fixed
  • Performance : Char data type is speed when compared to the VARCHAR2 data type, but memory wise it will consume more.

VARCHAR

  • Varchar is also a Character data type used for string of any variables or objects.
  • It is a variable data type, so the size of the data type is not fixed. So this data type is dynamic.
  • Performance : Varchar data type is less speed when compared to the CHAR data type, but memory wise it will consume less.

VARCHAR2

  • VARCHAR2 is also a Character data type used for string of any variable or objects.
  • It is very similar to VARCHAR.

Difference between CHAR and VARCHAR 2 in oracle SQL

CHAR

VARCHAR2

  • It is static data type

  • It is Dynamic Data type

  • The size of the character is fixed

  • The size of the character is variable

  • Performance wise CHAR is faster

  • Performance of VARCHAR2 is slower

  • Size of Char is 2000 and default size is 1

  • In VARCHAR2 the size is mandatory, so it can be maximum of 4000


Difference between VARCHAR and VARCHAR2 in oracle SQL


VARCHAR

VARCHAR2

  • It is ANSI standard

  • It is Oracle Standard

  • The size of the character is variable

  • The size of the character is variable

  • Maximum size can be 2000

  • Maximum size can be 4000



The word 'eduflee' is the string which is used in both char and varchar2

CHAR data type

E

D

U

F

L

E

E





Length of the Character is 10, even the string length is 7.

VARCHAR data type

E

D

U

F

L

E

E





char vs varchar2 in oracle

Numerical Data type

Numerical Data type is used for Number data types that which contains in the variables.

  • In Number Data type, we no need to mention the size.
  • we can't able to insert decimal values in the number data type instead use VARCHAR2
  • The value which contains only 0 - 9 only.
  • Other than that NULL can be inserted in the NUMBER data type. While Inserting NULL value no need to use literal (' ') in any of the data's.

Long Objects Data Type

The long objects data type is used to store a particular file, it can be store by using this data type only, it can't able to store using character or numerical data types.

Before ORACLE Database 8

RAW : The size of this RAW data type is maximum of 2000 bytes.
LONG : It will stores the character data type which can be around 2 gigabytes.
LONG RAW : It is an binary data type similar to RAW but it can able to handle of 2 gigabytes maximum.

After ORACLE Database 8

BLOB

CLOB

Binary Large Object

Character Large Object

Memory: 2GB of BLOB string can be used

Memory: 2GB of CLOB character can be used.

Values of this data type stores in the form of binary streams

Here it will be in the form of character streams

Storage: PDF files, documents etc.

Storage: Videos, audio files, Images, GIF etc.,

Data stored in binary form

It is stored in textual form

Chunks can be returned in RAW format

Chunks can be returned in 


Some Important Data type used in SQL and PL/SQL

1, Scalar Data type

  • NUMBER, PLS_INTEGER, BINARY_FLOAT

  • CHAR, VARCHAR2, NCHAR, NVARCHAR2

  • RAW

  • BOOLEAN

  • BLOB, CLOB, NCLOB

  • DATE, TIMESTAMP


2, Composite Data type

  • RECORD

  • %ROWTYPE

  • %TYPE

  • COLLECTION

  • VARRAY

  • NESTED TABLE

  • Associative Array.

3, Reference Data type

  • Refcursor data type.


%ROWTYPE

%TYPE

%ROWTYPE is used during record data type format, when we are using any particular table for assigning the datatype for the entire fields in the table means there this %ROWTYPE is used.

% TYPE is used to particular fields in the table, whenever we are changing the data type of any column in the base table means here this %type used table no need to change it, it will takes automatically whatever changes happens

Variable_name table_name%ROWTYPE

Variable_name table_name.column_name%TYPE


EXAMPLE

TABLE: customers

COLUMNS: id, name, age, 

V_custom_Detail customers%ROWTYPE;

V_name customers.name%type;


DATE

Date is a datatype which is used whenever we a column contains any date format means it will be comes under this data type it is like a string, we need to use literals (‘) while creating a column for the particular table.

Timestamp

It is similar like a date data type it contains current time that is running in the server time or system time in the time format is called timestamp Ex: HH:MM: SS

COLLECTION DATA TYPE

We can be able to store only single values in a variable in order to store multiple values in a variable we will go for collection data type so with the help of that we can able to retrieve many values using single variable and so the performance wise also it will be good to use it.

And this collection data type can be used in three different ways such as

  • Associative array

  • Nested Table

  • Varray

This collection is covered in separate session in PLSQL.

Example:

CREATE TABLE customers (id NUMBER

, Name VARCHAR2(50)

, Address CHAR (200)

, due_date DATE

, due_time TIMESTAMP

);


INSERT INTO customers VALUES (1, ‘Kavi’, ‘Chennai’, ’23-JUL-21’, ’11:24:30’) ;

CONCLUSION

I hope you got some clear understanding in using data type related to Oracle SQL and PLSQL.

So, if you having any doubts or queries means kindly comment below or contact directly to the official mail.


Labels:

Comments

Post a Comment

Thanks for commenting, we will always respond your comment.

Popular posts from this blog

Oracle SQL Interview Questions Series 1 [Basic]

Oracle SQL Interview Questions Series - 1 Oracle SQL Interview Questions covered in Eduflee, so complete all the series to get placed into the IT company. At present Structured Query Language is very important to enter into the Software company. After completing all the Interview series, you can crack many interviews with 100% assurance from my experience. Basic Questions for SQL interview Schema => User Records => Rows Fields => Columns Result set => Output 1, What are the Schema Objects in Oracle SQL ?     Table, View, Materialized view, Sequence, Synonyms, Index are the schema objects in SQL.     Table => Basic Unit of Storage [Rows and Columns]     View => Logical Representation of subject of records, It does not contains any data. So here data is logical.     Materialized View =>   It contains the data, it is also called as Snapshot. It contains data physically.     Sequence => It is a Numeric Value Generator     Synonyms => used to provide alternate nam
Post a Comment
Read more

Class 3 Functions in Oracle SQL

Functions in Oracle SQL Functions is one of the important features in Oracle SQL, with the help of this functions we can able to get the result as per our requirement. Functions in Oracle SQL contains Single Row Functions, Multiple Row Functions. Mainly those functions are used to returns value based on the input that which we given. And practicing this functions in oracle SQL, one can able to master in query writing. Functions is used to perform some actions like doing any calculations, grouping the data, conversion in type, date conversion etc. Types of Functions in Oracle SQL Functions in oracle is classified on the basis of the input which we are giving and getting the output, and they are: - Single Row Functions - Multiple Row Functions Single Row Functions in Oracle SQL Single Row Functions works on the single rows which will shows the result for that single rows only. If "n" number of inputs to give "n" number of outputs. From the above figure explains about
Post a Comment
Read more

Learn SQL From Scratch to Advanced topics

Learn SQL from Scratch to Advanced  Nowadays, many IT company moreover depend on SQL  only. So this is one of the basic query language to start for new developers. So track or to be master in SQL, the below topics should be covered. List of Basic Topics :- 1, Should know what is frontend and backend developer  2, What is DBMS, RDBMS ? 3, What is SQL ? 4, What are the database are there ? Beginner Level SQL topics  This are some beginner level SQL topics, if you are new to SQL this are the topics that should be covered first. 1, Oracle Basics  2, List of Objects in SQL   3 , Clause and its usage   4, Functions  5, Sub Query  6, Joins  7, Decode and Case 8, Constraints 9, Important Data Dictionary Table like user_source, all_source, user_tables etc.., Intermediate Level SQL topics (Creating Objects)  When we are entering into objects this below topics should be covered. In  this topics that we are creating database objects like tables, views, sequences, synonyms etc. 1, Data Definitio
Post a Comment
Read more