This PL/SQL function identifies the index of the first character in a string that appears only once. It's useful in text processing tasks, such as data validation or finding unique identifiers within log entries. For exa...

The function first handles edge cases by returning -1 for null or empty input strings. It then uses a helper function, `initialize_char_counts`, to create a frequency map (simulated using `DBMS_UTILITY.uncl_array`) of all characters in the input string. This map stores each unique character as a key and its occurrence count as the value. The main function then iterates through the input string again, character by character. For each character, it checks its count in the frequency map. If a character's count is exactly 1, its current index (1-based) is immediately returned, as this signifies the first non-repeating character. If the loop completes without finding such a character, it means all characters repeat, and -1 is returned. The time complexity is O(N) because we iterate through the string twice, and the space complexity is O(K), where K is the number of unique characters in the string (at most 256 for ASCII, or more for Unicode, but bounded).

FUNCTION find_first_non_repeating_idx(input_string): IF input_string is NULL or empty THEN RETURN -1 END IF char_counts = initialize_char_counts(input_string) FOR each character at index i in input_string: IF char_counts...

This PL/SQL function finds a peak element in a bitonic array (an array that first increases and then decreases). A peak element is defined as an element that is strictly greater than its adjacent elements. This function...

The function employs a binary search algorithm to efficiently locate a peak element. It initializes `low` to the first index and `high` to the last index of the array. In each iteration, it calculates the middle index `mid`. It then checks if the element at `mid` is a peak by comparing it with its neighbors. If the element to the left (`mid-1`) is greater, the peak must lie in the left half, so `high` is updated to `mid-1`. If the element to the right (`mid+1`) is greater, the peak must lie in the right half, so `low` is updated to `mid+1`. If neither neighbor is greater, then the element at `mid` is a peak, and its index is returned. Edge cases such as an empty array or an array with a single element are handled explicitly. The time complexity is O(log N) due to the binary search, and the space complexity is O(1) as it uses a constant amount of extra space.

FUNCTION find_peak_element(nums): IF nums is NULL or empty THEN RETURN -1 END IF low = 1 high = length(nums) IF length(nums) == 1 THEN RETURN 1 END IF WHILE low <= high: mid = floor((low + high) / 2) IF mid > 1 AND nums[...

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...

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.

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,...