Examine the code and state which of the following options is correct? CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER - 7 - IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END

Question

Multiple choice technology databases

Examine the code and state which of the following options is correct? CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER - 7 - IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END

You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.
You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.
You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table.

Reveal answer Fill a bubble to check yourself

B Correct answer

Explanation

The BONUS.CALC_SALARY function can be called from a SELECT statement because packaged functions are permitted in queries despite containing DML operations internally. The function has purity violations (reads and writes database state), but Oracle allows this in SELECT contexts - INSERT, UPDATE, and DELETE operations would fail due to purity restrictions.

AI explanation

AI 1

To answer this question, let's examine the code provided.

The given code defines a package called bonus with two functions: calc_bonus and calc_salary. The package body then implements these functions.

Option A) You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table. This option is incorrect because the calc_salary function is not designed to be used in an INSERT command. It is meant to calculate and return the salary based on the employee ID.

Option B) You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table. This option is correct. The calc_salary function can be called in a SELECT command to retrieve the calculated salary for a specific employee ID.

Option C) You can call the BONUS.CALC_SALARY packaged function from a DELETE command against the EMPLOYEES table. This option is incorrect. The calc_salary function is not designed to be used in a DELETE command. It is meant to calculate and return the salary based on the employee ID.

Option D) You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table. This option is incorrect. The calc_salary function is not designed to be used in an UPDATE command. It is meant to calculate and return the salary based on the employee ID.

Therefore, the correct answer is B) You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.

Accepted Answer

You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.

Keep practicing — related questions