ℹ️ Select 'Choose Exercise', or randomize 'Next Random Exercise' in selected language.

Choose Exercise:
Timer 00:00
WPM --
Score --
Acc --
Correct chars --

Dynamic SQL Execution: Conditional Query Building

PL/SQL

Goal -- WPM

Ready
Exercise Algorithm Area
1CREATE OR REPLACE PACKAGE dynamic_query_pkg AS

2

3-- Define a record type for report results

4TYPE report_row_t IS RECORD (

5order_id NUMBER,

6customer_name VARCHAR2(100),

7order_date DATE,

8order_status VARCHAR2(50),

9total_amount NUMBER

10);

11

12-- Define a collection type for report results

13TYPE report_data_t IS TABLE OF report_row_t INDEX BY PLS_INTEGER;

14

15PROCEDURE dynamic_report_generator (

16p_status IN VARCHAR2 DEFAULT NULL,

17p_start_date IN DATE DEFAULT NULL,

18p_end_date IN DATE DEFAULT NULL,

19p_customer_id IN NUMBER DEFAULT NULL

20);

21

22END dynamic_query_pkg;

23/

24

25CREATE OR REPLACE PACKAGE BODY dynamic_query_pkg AS

26

27PROCEDURE log_info (p_message IN VARCHAR2) IS

28BEGIN

29DBMS_OUTPUT.PUT_LINE('INFO: ' || p_message);

30END log_info;

31

32PROCEDURE log_error (p_message IN VARCHAR2) IS

33BEGIN

34DBMS_OUTPUT.PUT_LINE('ERROR: ' || p_message);

35END log_error;

36

37PROCEDURE dynamic_report_generator (

38p_status IN VARCHAR2 DEFAULT NULL,

39p_start_date IN DATE DEFAULT NULL,

40p_end_date IN DATE DEFAULT NULL,

41p_customer_id IN NUMBER DEFAULT NULL

42) IS

43v_sql_query CLOB;

44v_where_clause VARCHAR2(2000) := '';

45v_bind_vars DBMS_SQL.VARCHAR2_TABLE;

46v_bind_count PLS_INTEGER := 0;

47l_report_data report_data_t;

48v_start_time TIMESTAMP;

49v_end_time TIMESTAMP;

50

51-- Helper function to add conditions to WHERE clause safely

52FUNCTION add_condition (

53p_condition IN VARCHAR2,

54p_bind_var_name IN VARCHAR2

55) RETURN VARCHAR2 IS

56BEGIN

57v_bind_count := v_bind_count + 1;

58v_bind_vars(v_bind_count) := p_bind_var_name;

59IF v_where_clause IS NOT NULL THEN

60RETURN ' AND ' || p_condition;

61ELSE

62RETURN p_condition;

63END IF;

64END add_condition;

65

66BEGIN

67v_start_time := SYSTIMESTAMP;

68log_info('Starting dynamic report generation...');

69

70-- Base SQL query

71v_sql_query := 'SELECT o.order_id, c.customer_name, o.order_date, o.order_status, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE 1 = 1';

72

73-- Dynamically build WHERE clause

74IF p_status IS NOT NULL THEN

75v_where_clause := v_where_clause || add_condition('o.order_status = :status', 'p_status');

76END IF;

77

78IF p_start_date IS NOT NULL THEN

79v_where_clause := v_where_clause || add_condition('o.order_date >= :start_date', 'p_start_date');

80END IF;

81

82IF p_end_date IS NOT NULL THEN

83v_where_clause := v_where_clause || add_condition('o.order_date <= :end_date', 'p_end_date');

84END IF;

85

86IF p_customer_id IS NOT NULL THEN

87v_where_clause := v_where_clause || add_condition('o.customer_id = :customer_id', 'p_customer_id');

88END IF;

89

90-- Append the constructed WHERE clause to the base query

91IF v_where_clause IS NOT NULL THEN

92v_sql_query := v_sql_query || v_where_clause;

93END IF;

94

95-- Add ORDER BY clause for consistent results

96v_sql_query := v_sql_query || ' ORDER BY o.order_date, o.order_id';

97

98log_info('Generated SQL: ' || v_sql_query);

99log_info('Bind variables count: ' || v_bind_count);

100

101-- Execute the dynamic SQL query using EXECUTE IMMEDIATE

102BEGIN

103IF v_bind_count > 0 THEN

104EXECUTE IMMEDIATE v_sql_query

105BULK COLLECT INTO l_report_data

106USING p_status, p_start_date, p_end_date, p_customer_id; -- Order matters for USING clause

107ELSE

108-- If no filters, still execute the base query

109EXECUTE IMMEDIATE v_sql_query

110BULK COLLECT INTO l_report_data;

111END IF;

112

113v_end_time := SYSTIMESTAMP;

114log_info('Query executed successfully in ' || (v_end_time - v_start_time) || ' seconds.');

115log_info('Fetched ' || l_report_data.COUNT || ' records.');

116

117-- Process the fetched report data (e.g., display or further aggregation)

118IF l_report_data.COUNT > 0 THEN

119FOR i IN 1 .. l_report_data.COUNT LOOP

120-- Example: Displaying some details

121log_info(

122'Order ID: ' || l_report_data(i).order_id ||

123', Customer: ' || l_report_data(i).customer_name ||

124', Date: ' || TO_CHAR(l_report_data(i).order_date, 'YYYY-MM-DD') ||

125', Status: ' || l_report_data(i).order_status ||

126', Amount: ' || l_report_data(i).total_amount

127);

128END LOOP;

129ELSE

130log_info('No orders found matching the specified criteria.');

131END IF;

132

133EXCEPTION

134WHEN OTHERS THEN

135v_end_time := SYSTIMESTAMP;

136log_error('Error executing dynamic SQL query: ' || SQLERRM);

137log_error('Query: ' || v_sql_query);

138RAISE_APPLICATION_ERROR(-20301, 'Failed to generate report due to SQL execution error.');

139END;

140

141EXCEPTION

142WHEN OTHERS THEN

143log_error('An unexpected error occurred in dynamic_report_generator: ' || SQLERRM);

144RAISE;

145END dynamic_report_generator;

146

147END dynamic_query_pkg;

148/
Algorithm description viewbox

Dynamic SQL Execution: Conditional Query Building

Algorithm description:

This PL/SQL package `dynamic_query_pkg` features a `dynamic_report_generator` procedure that constructs and executes SQL queries on-the-fly. It accepts optional filtering parameters (status, date range, customer ID) and dynamically builds a `WHERE` clause. The procedure uses `EXECUTE IMMEDIATE` with `BULK COLLECT INTO` for efficient data retrieval and employs bind variables (`USING` clause) to prevent SQL injection. This is a powerful technique for creating flexible reporting tools and data access layers in PL/SQL applications.

Algorithm explanation:

The `dynamic_report_generator` procedure demonstrates secure dynamic SQL execution. It starts with a base `SELECT` statement and conditionally appends `AND` clauses to the `WHERE` clause based on provided parameters (`p_status`, `p_start_date`, etc.). The `add_condition` function helps manage the construction of the `WHERE` clause and tracks the bind variables. Crucially, `EXECUTE IMMEDIATE` is used to run the dynamically built SQL. The `USING` clause is essential for passing parameter values safely, preventing SQL injection by treating input as data, not executable code. `BULK COLLECT INTO` efficiently fetches the results into the `l_report_data` collection. The procedure includes comprehensive logging for generated SQL, execution time, and fetched records. Error handling is implemented for both the dynamic SQL execution and the overall procedure. The time complexity depends on the complexity of the generated query and the number of rows returned, typically O(N) where N is the number of rows fetched. Space complexity is O(N) due to the `BULK COLLECT` into the `l_report_data` collection.

Pseudocode:

PACKAGE dynamic_query_pkg:
  DEFINE record_type report_row_t(order_id, customer_name, order_date, order_status, total_amount)
  DEFINE collection_type report_data_t OF report_row_t

  PROCEDURE dynamic_report_generator(status, start_date, end_date, customer_id):
    DECLARE sql_query CLOB = 'SELECT ... FROM orders o JOIN customers c ON ... WHERE 1 = 1'
    DECLARE where_clause = ''
    DECLARE bind_vars list of bind variable names
    DECLARE bind_count = 0
    DECLARE report_data of type report_data_t

    FUNCTION add_condition(condition, bind_var_name):
      increment bind_count
      add bind_var_name to bind_vars list
      IF where_clause is not empty THEN
        RETURN ' AND ' || condition
      ELSE
        RETURN condition
      END IF
    END add_condition

    IF status IS NOT NULL THEN
      where_clause = where_clause + add_condition('o.order_status = :status', 'p_status')
    END IF
    IF start_date IS NOT NULL THEN
      where_clause = where_clause + add_condition('o.order_date >= :start_date', 'p_start_date')
    END IF
    IF end_date IS NOT NULL THEN
      where_clause = where_clause + add_condition('o.order_date <= :end_date', 'p_end_date')
    END IF
    IF customer_id IS NOT NULL THEN
      where_clause = where_clause + add_condition('o.customer_id = :customer_id', 'p_customer_id')
    END IF

    IF where_clause is not empty THEN
      sql_query = sql_query + where_clause
    END IF
    sql_query = sql_query + ' ORDER BY ...'

    LOG 'Generated SQL: ' || sql_query

    BEGIN
      IF bind_count > 0 THEN
        EXECUTE IMMEDIATE sql_query BULK COLLECT INTO report_data USING status, start_date, end_date, customer_id
      ELSE
        EXECUTE IMMEDIATE sql_query BULK COLLECT INTO report_data
      END IF
      LOG 'Fetched ' || report_data.COUNT || ' records'
      FOR EACH row IN report_data:
        LOG row details
      END LOOP
    EXCEPTION
      WHEN OTHERS THEN
        LOG 'Error executing dynamic SQL'
        RAISE error
    END
  END dynamic_report_generator
END dynamic_query_pkg