Oracle 1Z0-147 : Oracle9i program with pl/sql

Examine this package specification:
CREATE OR REPLACE PACKAGE concat_all
IS
v_string VARCHAR2(100);
PROCEDURE combine (p_num_val NUMBER);
PROCEDURE combine (p_date_val DATE);
PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);
END concat_all; /
Which overloaded COMBINE procedure declaration can be added to this package specification?

• A. PROCEDURE concat_all
  (p_num_val VARCHAR2, p_char_val NUMBER);
• B. PROCEDURE combine (p_no NUMBER);
• C. PROCEDURE combine;
• D. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER;

Examine this code:
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 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 bonus; / Which statement is true?

• A. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.
• B. You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.
• C. You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.
• D. You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table.

Examine this package
CREATE OR REPLACE PACKAGE discounts
IS
g_id NUMBER := 7839;
discount _rate NUMBER := 0.00;
PROCEDURE display_price (p_price NUMBER);
END discounts;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS
PROCEDURE display_price (p_price NUMBERI)
IS
BEGIN
DBMS_OUTPUT.PUT LINE ( 'Discounted '||
TO_CHAR(p_price*NVL(dlscount_rate, 1)));
END display_price;
BEGIN
Discount_rate = 0.10;
END discounts;
/
The SQL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure
DISPLAY_PRICE from SQL*Plus with the command EXECUTE discounts. display_price (100);
What is the result?

• A. Discounted 0.10
• B. Discounted 100
• C. Discounted NULL
• D. Discounted 0.00
• E. Discounted 10

Given a function CALCTAX:
CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER
IS
BEGIN
RETURN (sal * 0.05);
END;
If you want to run the above function from the SQL *Plus prompt, which statement is true?

• A. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);
• B. You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000);.
• C. You need to execute the command EXECUTE FUNCTION calctax;.
• D. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;
• E. You need to execute the command CALCTAX(1000);.

Examine this code:
CREATE OR REPLACE STORED FUNCTION get_sal
(p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE)
RETURN NUMBER
IS
v_salary NUMBER;
v_raise NUMBER(8,2);
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = p_employee_id;
v_raise := p_raise_amt * v_salary;
RETURN v_raise;
END;
Which statement is true?

• A. This statement fails.
• B. This statement creates a stored function named get_sal with a status of invalid.
• C. This statement creates a stored function named get_sal.
• D. This statement returns a raise amount based on an employee id.
• E. This statement creates a stored procedure named get_sal.