1Z0-149 Free Sample Questions

A financial services application uses a PL/SQL package to calculate loan eligibility. To improve performance for frequently called scenarios, the lead developer decides to use a result-cached function. The function takes a customer ID and loan amount as input. During testing, it's discovered that the function returns stale data if the customer's credit score, stored in a separate `CREDIT_SCORES` table, is updated. Which clause must be added to the function definition to ensure the cache is invalidated when the `CREDIT_SCORES` table changes?

A DBA is reviewing a large PL/SQL package and notices several procedures pass a large record type (defined with %ROWTYPE from a table with 50 columns) as an `IN OUT` parameter. The DBA suspects this is causing performance degradation due to excessive copying of the record's data. Which compiler hint should be used in the procedure definition to request pass-by-reference semantics and potentially improve performance?

A developer needs to create a PL/SQL package that will be deployed across different customer environments running Oracle Database 12c, 18c, and 19c. The package should use a new feature available only in 19c when possible, but fall back to an older implementation on earlier database versions. Which PL/SQL feature allows for this version-specific code implementation within a single source file?

A security audit requires that a specific sensitive procedure, `process_payroll`, within the `HR_PKG` package can ONLY be called by the `BATCH_JOB_PKG` and the `FINANCE_REPORTS_PKG`. No other database object or user should be able to execute this procedure directly. Which is the most effective and secure method to enforce this access restriction?

A data processing pipeline needs to load configuration settings from a key-value table into a PL/SQL collection for fast lookups. The keys are `VARCHAR2` strings (e.g., 'TIMEOUT', 'LOG_LEVEL') and the values are also `VARCHAR2`. The number of settings is unknown and can change. Which composite data type is the most appropriate choice for this requirement?

A developer is writing a procedure to archive old orders. The procedure must perform two main tasks: copy order data to an archive table and then delete the original orders. It is critical that the logging of the archive operation succeeds and is committed, even if the subsequent deletion of the original orders fails and is rolled back. Which two PL/SQL features should be combined to achieve this? (Select TWO)

**Case Study:** A logistics company, ShipFast Inc., is developing a new package, `TRACKING_PKG`, to manage shipment statuses. The package needs to provide a procedure, `UPDATE_STATUS`, that takes a tracking number and a new status. A key requirement is that every status update attempt, whether successful or not, must be recorded in an `AUDIT_LOG` table for compliance reasons. The audit record must be saved permanently, even if the main transaction that called `UPDATE_STATUS` is later rolled back by the calling application. Furthermore, the `UPDATE_STATUS` procedure will be part of a large, complex transaction and must not issue its own `COMMIT` or `ROLLBACK`, as this would interfere with the calling application's transaction control. The audit logging, however, must be self-contained. The development team has decided to use a private procedure within the package body, `LOG_AUDIT_ATTEMPT`, to handle the insertion into the `AUDIT_LOG` table. To optimize performance, another procedure, `BULK_UPDATE_STATUSES`, is required. This procedure will accept a collection of tracking numbers and statuses and update them all. The team wants to minimize context switching between the PL/SQL and SQL engines during this bulk operation. Which package body implementation correctly satisfies all the requirements for both transactional integrity and performance optimization?

True or False: An `INDEX BY` table (associative array) defined within a PL/SQL package specification persists for the duration of the database session.

A developer needs to process a result set of employee records. The number of employees is large but manageable within session memory. For each employee, multiple DML operations are required. To improve performance, the developer wants to fetch all employee records from the `EMPLOYEES` table into a PL/SQL collection in a single database round-trip. Which SQL statement clause should be used?

Examine the following code: ```sql DECLARE TYPE t_emp_rec IS RECORD ( employee_id employees.employee_id%TYPE, salary employees.salary%TYPE ); v_emp_rec t_emp_rec; BEGIN SELECT employee_id, salary INTO v_emp_rec FROM employees WHERE department_id = 90; DBMS_OUTPUT.PUT_LINE('Employees found: ' || SQL%ROWCOUNT); END; ``` The `employees` table has three employees in department 90. What is the result when this block is executed?