1Z0-184-25 Question 140
Single answerYou are designing a Red-Amber-Green (RAG) status application for a large retail company using PL/SQL on Oracle Autonomous Database in Oracle Cloud Infrastructure. The application must periodically evaluate the total sales in each region and assign a color code (Red, Amber, or Green) to each record based on custom threshold values stored in a configuration table. Which approach ensures maintainability and accurate RAG status updates?
- A
A. Use a table-level trigger on the sales table to automatically assign a RAG status for each INSERT or UPDATE statement in real time.
- B
B. Store threshold values in PL/SQL package constants and manually invoke an anonymous block to update statuses after each load.
- C
C. Create a dedicated configuration table for thresholds and a PL/SQL stored procedure to compute RAG statuses, then schedule it with DBMS_SCHEDULER.
- D
D. Maintain RAG logic in the front-end application code, calling individual UPDATE statements for each record during user sessions.
Show answer and explanation
Correct answer: C
Explanation
Option C follows best practices for modular, maintainable PL/SQL design in Oracle Cloud Infrastructure. By separating threshold values into a configuration table, you can adjust them without modifying your core PL/SQL code. Scheduling the RAG update with DBMS_SCHEDULER ensures consistent updates at defined intervals. For more details, consult Oracle documentation on DBMS_SCHEDULER and PL/SQL best practices at https://docs.oracle.com/en/database/ and related Oracle Cloud Infrastructure resources.
- A. Incorrect.
A. Using a trigger for RAG assignments on every insert or update can quickly become unwieldy, especially if thresholds or business logic change often. Triggers also add overhead whenever data is modified in large batches, reducing performance.
- B. Incorrect.
B. Hard-coding thresholds in package constants makes it difficult to adjust values over time. Recompiling PL/SQL objects for routine threshold changes is inefficient and error-prone.
- C. Correct.
C. Storing thresholds in a configuration table and calling a scheduled PL/SQL stored procedure to determine RAG statuses is a flexible, maintainable approach. This allows for easy updates of threshold data and efficient batch processing of records.
- D. Incorrect.
D. Embedding RAG logic in the front-end code leads to scattered logic and potential inconsistencies, especially if multiple clients or services interact with the database. It also places business logic outside the database tier, making centralized management harder.