Advanced SQL Query Validation: Ensuring Integrity with PL/SQL

In today’s data-driven world, ensuring the accuracy and integrity of SQL queries is paramount. Invalid queries can lead to errors, performance issues, and data inconsistencies. This blog will deep into an advanced PL/SQL function designed to validate SQL queries, ensuring they meet specific structural and content criteria. This function is handy for developers and database administrators who must enforce standards and safeguard against erroneous queries.

Introduction to validate_sql_query

The validate_sql_query function is a sophisticated utility written in PL/SQL that validates SQL queries submitted as CLOB (Character Large Object). This function checks that the query adheres to predefined rules, ensuring it is well-structured and contains the correct number of columns with valid aliases. By doing so, it helps maintain the quality and reliability of SQL operations in Oracle databases.

Function Code

Here’s the full implementation of the validate_sql_query function:

 FUNCTION validate_sql_query(p_query IN CLOB)
 RETURN VARCHAR2
 IS
     v_query       VARCHAR2(32767);
     v_cursor      INTEGER;
     v_col_count   INTEGER := 0;
     v_desc_tab    DBMS_SQL.DESC_TAB;
     v_error_msg   VARCHAR2(4000);
     v_normalized_query VARCHAR2(32767);
     v_select_pos  INTEGER;
     v_from_pos    INTEGER;
     v_order_by_pos INTEGER;
     v_order_by_clause VARCHAR2(32767);
 BEGIN
     -- Convert CLOB to VARCHAR2 if it's not too large
     BEGIN
         IF DBMS_LOB.GETLENGTH(p_query) > 32767 THEN
             RETURN 'Error: Query is too large to process.';
         ELSE
             v_query := DBMS_LOB.SUBSTR(p_query, 32767, 1);
         END IF;
     EXCEPTION
         WHEN VALUE_ERROR THEN
             RETURN 'Error: Issue converting CLOB to VARCHAR2. Query might be too large.';
     END;
 
     -- Basic Check for SELECT statement structure
     v_select_pos := INSTR(UPPER(v_query), 'SELECT');
     v_from_pos := INSTR(UPPER(v_query), 'FROM');
 
     IF v_select_pos = 0 OR v_from_pos = 0 OR v_from_pos <= v_select_pos THEN
         RETURN 'Error: Query does not start with a valid SELECT statement or has an invalid structure.';
     END IF;
 
     -- Normalize query to handle Oracle-specific syntax
     v_normalized_query := REPLACE(v_query, ' (+) ', ' LEFT JOIN ');
 
     -- Output the original and normalized queries for debugging
     DBMS_OUTPUT.PUT_LINE('Original Query: ' || v_query);
     DBMS_OUTPUT.PUT_LINE('Normalized Query: ' || v_normalized_query);
 
     -- Remove ORDER BY clause for validation purposes
     v_order_by_pos := INSTR(UPPER(v_normalized_query), 'ORDER BY');
     IF v_order_by_pos > 0 THEN
         v_order_by_clause := SUBSTR(v_normalized_query, v_order_by_pos);
         v_normalized_query := SUBSTR(v_normalized_query, 1, v_order_by_pos - 1);
         
         -- Check if ORDER BY clause is properly ended
         IF TRIM(v_order_by_clause) = 'ORDER BY' THEN
             RETURN 'Error: ORDER BY clause is missing columns or expressions.';
         ELSIF NOT REGEXP_LIKE(v_order_by_clause, 'ORDER BY\s+\w+', 'i') THEN
             RETURN 'Error: ORDER BY clause does not contain valid column names or expressions.';
         END IF;
     END IF;
 
     -- Use DBMS_SQL to parse and describe the query
     v_cursor := DBMS_SQL.OPEN_CURSOR;
     BEGIN
         -- Parse the query
         DBMS_SQL.PARSE(v_cursor, v_normalized_query, DBMS_SQL.NATIVE);
 
         -- Describe the columns in the result set
         DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_count, v_desc_tab);
 
         -- Check if the number of columns is exactly 2
         IF v_col_count != 2 THEN
             RETURN 'Error: Query must return exactly 2 columns.';
         END IF;
 
         -- Validate column aliases
         FOR i IN 1 .. v_col_count LOOP
             IF v_desc_tab(i).col_name IS NULL OR v_desc_tab(i).col_name = '' THEN
                 RETURN 'Error: Columns must have valid aliases.';
             END IF;
         END LOOP;
 
     EXCEPTION
         WHEN OTHERS THEN
             v_error_msg := 'SQL Query Parsing Error: ' || SQLERRM || ' Query: ' || v_normalized_query;
             RETURN v_error_msg;
     END;
 
     BEGIN
         DBMS_SQL.CLOSE_CURSOR(v_cursor);
     EXCEPTION
         WHEN OTHERS THEN
             RETURN 'Error: Issue closing the cursor. ' || SQLERRM;
     END;
 
     RETURN 'VALID';
 END;

Function Breakdown

1. Handling Large Queries:

• The function starts by verifying if the CLOB query is within the manageable size for VARCHAR2. If the query is too large, it returns an error message. Otherwise, it converts the CLOB to VARCHAR2 for easier processing.

2. Structural Validation:

• It checks that the query includes both SELECT and FROM keywords and that FROM appears after SELECT. This ensures that the query starts with a valid SELECT statement.

3. Query Normalization:

• The function normalizes the query by replacing Oracle-specific join syntax with standard SQL syntax. This step ensures compatibility and standardization.

4. ORDER BY Clause Validation:

• Any ORDER BY clause is removed before further validation. The function ensures that if an ORDER BY clause is present, it is correctly formatted and contains valid column names.

5. Column Validation:

• The function uses DBMS_SQL to parse the query and describe its columns. It checks that the query returns exactly two columns, each with a valid alias.

6. Error Handling:

• Robust error handling is included to catch and report issues during query parsing and cursor operations. This ensures that any errors encountered are communicated clearly.

Practical Applications

1. Query Validation in Applications:

• Integrate this function into applications to validate user-generated queries before execution. This helps avoid runtime errors and ensures that only well-formed queries are processed.

2. Enforcing Standards:

• Use the function to enforce standards for SQL queries, such as requiring a specific number of columns and valid column aliases. This ensures consistency and adherence to query standards.

3. Debugging and Diagnostics:

• The function provides the debugging output of the original and normalized queries, which is invaluable for diagnosing and resolving issues with complex queries.

1. Calling the Function from SQL Command or SQL Developer

You can use SQL*Plus or SQL Developer to execute the validate_sql_query function. This method is useful for quick validations or debugging.

Example:

--Define and validate an SQL query
DECLARE
    l_query   CLOB := 'SELECT col1, col2 FROM my_table ORDER BY col1';  -- Replace with your SQL query
    l_result  VARCHAR2(4000);
BEGIN
    -- Call the validate_sql_query function
    l_result := validate_sql_query(l_query);
    
    -- Output the validation result
    DBMS_OUTPUT.PUT_LINE('Validation Result: ' || l_result);
END;
/

Steps:

1. Open SQL Command or SQL Developer.
2. Copy and paste the above PL/SQL block.
3. Execute the block to see the validation result in the output pane.

2. Calling the Function from a PL/SQL Procedure

Integrating the validate_sql_query function into a PL/SQL procedure allows for reusable query validation within larger PL/SQL programs.

Example:


CREATE OR REPLACE PROCEDURE validate_query(p_query IN CLOB) AS
    l_result VARCHAR2(4000);
BEGIN
    -- Call the validate_sql_query function
    l_result := validate_sql_query(p_query);
    
    -- Handle the result (e.g., log it, raise an exception, etc.)
    DBMS_OUTPUT.PUT_LINE('Validation Result: ' || l_result);
END;
/
--------------------------------------
BEGIN
    -- Call the procedure with a specific SQL query
    validate_query('SELECT col1, col2 FROM my_table ORDER BY col1');
END;

Steps:

1. Create the procedure using the provided code.
2. Execute the procedure block with your SQL query to see the result.

3. Calling the Function from Oracle APEX

In Oracle APEX, you can call the validate_sql_query function in processes or dynamic actions to validate queries submitted by users or generated by applications.

In a Process:

1. Navigate to the desired page in Oracle APEX.
2. Create a new PL/SQL process.
3. Use the following PL/SQL code

DECLARE l_query CLOB := :P1_SQL_QUERY;
 l_result VARCHAR2(4000);
 BEGIN
 l_result := validate_sql_query(l_query);   -- Store the result in an APEX item for display or further processing
 :P1_VALIDATION_RESULT := l_result;     -- Replace with your page item
 END;

In a Dynamic Action:

1. Create a new Dynamic Action triggered by an event (e.g., button click).
2. Set the action type to “Execute PL/SQL Code.”
3. Use the following code:

DECLARE 
l_query CLOB := :P1_SQL_QUERY; 
 l_result VARCHAR2(4000);
 BEGIN 
 l_result := validate_sql_query(l_query);
apex_application.g_print_success_message := l_result;
 END;

Steps:

1. Add or edit a process or dynamic action in Oracle APEX.
2. Insert the provided PL/SQL code.
3. Set appropriate page items to capture and display the validation result.

Conclusion

The validate_sql_query function is a powerful tool for ensuring SQL queries are correctly structured and meet predefined standards. By validating queries before execution, you can enhance the reliability, accuracy, and performance of SQL operations in Oracle databases. This function is especially useful for developers and database administrators who need to maintain high standards for query quality and integrity.

For more insights into advanced PL/SQL techniques and best practices, continue following our blog. Feel free to reach out if you have questions or need additional support with SQL query management.

Happy coding!