How to Retrieving Column Data Type in Oracle Using PL-SQL

In Oracle databases, understanding column data types is essential for effective database management. PL/SQL provides a straightforward way to retrieve these data types, aiding in database design and query formulation.

By querying system views like USER_TAB_COLUMNS, users can obtain valuable insights into column properties, facilitating accurate data manipulation. This article explores the process of retrieving column data types in Oracle using PL/SQL, offering syntax, examples, and explanations.

Retrieving Column Data Types in Oracle Using PL/SQL

The main idea behind retrieving column data types in Oracle is to query system views that contain metadata about the database schema. Specifically, we’ll use the USER_TAB_COLUMNS view, which stores information about columns accessible to the current user.

Syntax:

SELECT data_type
FROM user_tab_columns
WHERE table_name = '<table_name>'
AND column_name = '<column_name>';

• SELECT data_type: This part selects the data type of the specified column.
• FROM user_tab_columns: This specifies the source of the data, the user_tab_columns view.
• WHERE table_name = ‘<table_name>‘ AND column_name = ‘<column_name>’: These conditions filter the rows based on the specified table and column names.

Example of Retrieving Column Data Types in Oracle Using PL/SQL

Let’s create a sample table called clubMembers and insert some data into it. Then, we’ll demonstrate how to retrieve column data types using PL/SQL with examples, queries, outputs, and explanations.

Creating the clubMembers Table:

CREATE TABLE clubMembers (
    member_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    membership_type VARCHAR2(20),
    join_date DATE
);

INSERT INTO clubMembers (member_id, name, membership_type, join_date) VALUES (6, 'Mohammad Khan', 'Gold', TO_DATE('2022-01-06', 'YYYY-MM-DD'));
INSERT INTO clubMembers (member_id, name, membership_type, join_date) VALUES (8, 'Ali Ahmed', 'Gold', TO_DATE('2022-01-08', 'YYYY-MM-DD'));
INSERT INTO clubMembers (member_id, name, membership_type, join_date) VALUES (4, 'Priya Patel', 'Silver', TO_DATE('2022-01-04', 'YYYY-MM-DD'));
INSERT INTO clubMembers (member_id, name, membership_type, join_date) VALUES (5, 'Neha Gupta', 'Gold', TO_DATE('2022-01-05', 'YYYY-MM-DD'));
INSERT INTO clubMembers (member_id, name, membership_type, join_date) VALUES (10, 'Zoya Ahmed', 'Gold', TO_DATE('2022-01-10', 'YYYY-MM-DD'));

Output:

insert values into table

Now let’s, see the Examples to understand the concept.

Example 1: Retrieving Data Type of member_id

In this example,, we will retrieve the data type of the column named ‘member_id‘ in the table clubMembers.

Query:

SELECT data_type 
FROM user_tab_columns 
WHERE table_name = 'clubMembers' 
AND column_name = 'member_id';

Output:

Retrieving Data Type of member_id

Explanation:

• This query selects the data_type column from the user_tab_columns view, which provides information about columns in tables accessible to the current user.
• The WHERE clause filters the rows where the table_name is ‘clubMembers‘ and the column_name is ‘member_id‘.
• It retrieves the data type of the member_id column in the clubMembers table.

Example 2: Retrieving Data Type of name

In this example,, we will retrieve the data type of the column named ‘name‘ in the table clubMembers.

Query:

SELECT data_type 
FROM user_tab_columns 
WHERE table_name = 'clubMembers' 
AND column_name = 'name';

Output:

Retrieving Data Type of name:

Explanation: The query selects the data type of the ‘let’s‘ column from the ‘clubMembers‘ table. If the output is VARCHAR2(50), it means the data type of the ‘name’ column is VARCHAR2 with a maximum length of 50 characters.

Example 3: Retrieving Data Type of join_date

In this example we will retrieve the data type of the column named ‘join_date‘ in the table clubMembers.

Query:

SELECT data_type 
FROM user_tab_columns 
WHERE table_name = 'clubMembers' 
AND column_name = 'join_date';

Output:

Retrieving Data Type of join_date

Explanation: This query retrieves the data type of the ‘join_date‘ column from the ‘clubMembers‘ table. If the output is DATE, it indicates that the data type of the ‘join_date‘ column is DATE, which stores date values in Oracle databases.

Conclusion

Retrieving column data types in Oracle using PL/SQL is a very useful skill for database developers and administrators. By understanding the types of data stored in each column, you can design efficient databases and write accurate queries. Through this tutorial, we learn how to retrieve data type of columns in Oracle using PL/SQL.