How to Retrieve 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 key to retrieving column data types in Oracle lies in querying system views that store metadata about the database schema. One of the most commonly used views for this purpose is USER_TAB_COLUMNS, which contains information about columns in tables accessible to the current user.

Key System Views for Retrieving Column Data Types

• USER_TAB_COLUMNS: Contains column information for tables owned by the current user.
• ALL_TAB_COLUMNS: Provides column details for all accessible tables in the database.
• DBA_TAB_COLUMNS: Contains column information for all tables in the database, requiring DBA privileges to access.

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:

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:

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:

Explanation:

• This query retrieves the data_type for the name column in the clubMembers table.
• If the output is VARCHAR2, it indicates that the column can store variable-length character data with a specified length (50 characters in this case).

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:

Explanation:

• The query retrieves the data_type of the join_date column from the clubMembers table.
• If the output is DATE, it confirms that the column is used to store date values.

Conclusion

Retrieving column data types in Oracle using PL/SQL is a very useful skill for database developers and administrators. column data types helps in designing efficient databases, writing optimized queries, and ensuring data integrity. Through this tutorial, we learn how to retrieve data type of columns in Oracle using PL/SQL.