Retrieving Column Data Types in Oracle with PL/SQL under Low Privileges

Introduction

In Oracle database management practice, developers and analysts frequently need to obtain detailed table structure information, particularly column data types and lengths. However, in privilege-restricted environments, traditional DDL query methods often fail to execute. Based on actual Q&A data, this article provides an in-depth analysis of best practices for retrieving column metadata using PL/SQL in low-privilege environments.

Data Dictionary Views Overview

Oracle database provides comprehensive data dictionary views, among which the ALL_TAB_COLUMNS view serves as the core tool for obtaining table column information. This view contains critical information including table owner, table name, column name, data type, data length, and is typically accessible to users with table read privileges.

Unlike the DESC command in SQL*Plus, ALL_TAB_COLUMNS can be accessed through standard SQL queries, making it the ideal choice in PL/SQL environments. The DESC command is a SQL*Plus-specific client tool command that cannot be directly used in PL/SQL blocks.

ALL_TAB_COLUMNS View Structure Analysis

The ALL_TAB_COLUMNS view contains multiple important fields, with key fields related to data types including:

• DATA_TYPE: Column data type, such as VARCHAR2, NUMBER, DATE, etc.
• DATA_LENGTH: Column data length, representing bytes for character types and precision for numeric types
• DATA_PRECISION: Precision (total digits) for numeric types
• DATA_SCALE: Scale (decimal digits) for numeric types
• CHAR_LENGTH: Character length for character types
• CHAR_USED: Indicates whether length is calculated in bytes or characters

Basic Query Methods

The fundamental query for retrieving specific table and column data types is as follows:

SELECT column_name, data_type, data_length, data_precision, data_scale, char_length, char_used
FROM all_tab_columns 
WHERE table_name = 'MY_TABLE' 
AND column_name = 'MY_COLUMN';

In practical applications, it is recommended to use uppercase table and column names in queries, as object names in Oracle data dictionary are typically stored in uppercase.

Complete Data Type Format Generation

To generate complete data type descriptions similar to the DESC command, the following complex query can be used:

SELECT column_name, 
       data_type ||
       CASE
         WHEN data_precision IS NOT NULL AND NVL(data_scale, 0) > 0 
           THEN '(' || data_precision || ',' || data_scale || ')'
         WHEN data_precision IS NOT NULL AND NVL(data_scale, 0) = 0 
           THEN '(' || data_precision || ')'
         WHEN data_precision IS NULL AND data_scale IS NOT NULL 
           THEN '(*,' || data_scale || ')'
         WHEN char_length > 0 
           THEN '(' || char_length || 
                CASE char_used 
                  WHEN 'B' THEN ' Byte'
                  WHEN 'C' THEN ' Char'
                  ELSE '' 
                END || ')'
         ELSE ''
       END ||
       DECODE(nullable, 'N', ' NOT NULL', '') AS full_data_type
FROM all_tab_columns
WHERE table_name = 'TABLE_NAME'
AND column_name = 'COLUMN_NAME';

This query can generate complete Oracle data type descriptions, including precision, scale, character length constraints, and nullability information.

Cross-Schema Query Considerations

When querying tables from other schemas, the table owner must be specified in the query conditions:

SELECT data_type, data_length 
FROM all_tab_columns 
WHERE owner = 'SCHEMA_OWNER'
AND table_name = 'MY_TABLE_NAME' 
AND column_name = 'MY_COL_NAME';

This type of cross-schema query is particularly important in distributed applications and multi-tenant environments.

Impact of Data Redaction on Query Results

In environments with high security requirements, database administrators may configure data redaction policies. Oracle's DBMS_REDACT package provides real-time data masking functionality, which may affect the display of query results.

Data redaction policies are dynamically applied based on user privileges and session context. If a user has the EXEMPT REDACTION POLICY system privilege, redaction will not be performed. Otherwise, the system will determine whether to mask query results based on policy expressions.

Common redaction types include:

• Full redaction: Returns fixed values or null values
• Partial redaction: Masks specific portions of data
• Random redaction: Returns different random values for each query
• Regular expression redaction: Pattern-based masking

In environments with data redaction policies, the data type information returned by querying ALL_TAB_COLUMNS remains accurate, but actual table data queries may display masked values.

PL/SQL Implementation Solutions

In PL/SQL stored procedures or functions, dynamic SQL can be used to encapsulate data type query logic:

CREATE OR REPLACE FUNCTION get_column_info(
    p_table_name IN VARCHAR2,
    p_column_name IN VARCHAR2
) RETURN VARCHAR2
IS
    v_data_type VARCHAR2(106);
    v_data_length NUMBER;
BEGIN
    SELECT data_type, data_length
    INTO v_data_type, v_data_length
    FROM all_tab_columns
    WHERE UPPER(table_name) = UPPER(p_table_name)
    AND UPPER(column_name) = UPPER(p_column_name);
    
    RETURN v_data_type || '(' || v_data_length || ')';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Column not found';
    WHEN TOO_MANY_ROWS THEN
        RETURN 'Multiple columns found';
END get_column_info;
/

This function provides a reusable interface that can be called from various PL/SQL programs.

Best Practice Recommendations

Based on Q&A data analysis and practical application experience, the following best practices are recommended:

1. Privilege Management: Ensure users have at least SELECT privileges on target tables and query privileges on the ALL_TAB_COLUMNS view
2. Error Handling: Properly handle NO_DATA_FOUND and TOO_MANY_ROWS exceptions in PL/SQL code
3. Performance Optimization: Consider caching results or using materialized views for frequent metadata queries
4. Case Handling: Use UPPER() function to ensure accurate table and column name matching
5. Security Considerations: Understand potential redaction policies in the environment to avoid incorrect assumptions based on masked data

Conclusion

In Oracle low-privilege environments, retrieving column data type information through the ALL_TAB_COLUMNS data dictionary view is the most reliable and efficient method. The query solutions and PL/SQL implementations provided in this article can meet the requirements of most application scenarios, while incorporating best practices for data security and performance optimization. Developers should choose appropriate implementation solutions based on specific privilege constraints and environmental configurations.