Oracle9i program with pl/sql (1Z0-147) Free Practice Test
Question 1
Which two does the INSTEAD OF clause in a trigger identify? (Choose two)
Correct Answer: B,C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 2
You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE.What type of trigger do you create?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 3
Examine this code:
CREATE OR REPLACE PROCEDURE add_dept
( p_name departments.department_name%TYPE DEFAULT 'unknown',
p_loc departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO departments(department_id, department_name,
loclation_id)
VALUES(dept_seq.NEXTVAL,p_name, p_loc);
END add_dept;
/
You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus.
Which four are valid invocations? (Choose four)
CREATE OR REPLACE PROCEDURE add_dept
( p_name departments.department_name%TYPE DEFAULT 'unknown',
p_loc departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO departments(department_id, department_name,
loclation_id)
VALUES(dept_seq.NEXTVAL,p_name, p_loc);
END add_dept;
/
You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus.
Which four are valid invocations? (Choose four)
Correct Answer: A,B,C,E
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 4
How can you migrate from a LONG to a LOB data type for a column?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 5
Which three are valid ways to minimize dependency failure? (Choose three)
Correct Answer: B,C,E
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 6
Which two tables or views track object dependencies? (Choose two)
Correct Answer: A,D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 7
You need to create a trigger to ensure that information in the EMP table is only modified during business hours, Monday to Friday from 9:00am to 500pm Which types of trigger do you create? (Choose two.)
Correct Answer: B,C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 8
You have the following table:
CREATE TABLE Emp_log ( Emp_id NUMBER Log_date DATE, New_salary NUMBER, Action VARCHAR(20));
You have the following data in the EMPLOYEES table:
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
100 King 24000 90 101 Kochhar 17000 90 102 De Haan 17000 90 103 Hunold 9000 60 104 Ernst 6000 60 105 Austin 4800 60 106 Pataballa 4800 60 107 Lorentz 4200 60 108 Greenberg 12000 100 201 Hartstein 13000 20 202 Fay 6000 20
You create this trigger:
CREATE OR REPLACE TRIGGER Log_salary_increase AFTER UPDATE ON employees FOR EACH ROW WHEN (:new.Salary > 1000) BEGIN INSERT INTO Emp_log (Emp_id, Log_date, New_Salary, Action) VALUES (new.Employee_id, SYSDATE, :new.SALary, 'NEW SAL'); END /
Then, you enter the following SQL statement:
UPDATE Employee SET Salary = Salary + 1000.0 Where Department_id = 20M
What are the result in the EMP_LOG table?
A EMP_ID LOG_DATE NEW_SALARY ACTION
201 24-SEP-02 13000 NEW SAL 202 24-SEP-02 600 NEW SAL
CREATE TABLE Emp_log ( Emp_id NUMBER Log_date DATE, New_salary NUMBER, Action VARCHAR(20));
You have the following data in the EMPLOYEES table:
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
100 King 24000 90 101 Kochhar 17000 90 102 De Haan 17000 90 103 Hunold 9000 60 104 Ernst 6000 60 105 Austin 4800 60 106 Pataballa 4800 60 107 Lorentz 4200 60 108 Greenberg 12000 100 201 Hartstein 13000 20 202 Fay 6000 20
You create this trigger:
CREATE OR REPLACE TRIGGER Log_salary_increase AFTER UPDATE ON employees FOR EACH ROW WHEN (:new.Salary > 1000) BEGIN INSERT INTO Emp_log (Emp_id, Log_date, New_Salary, Action) VALUES (new.Employee_id, SYSDATE, :new.SALary, 'NEW SAL'); END /
Then, you enter the following SQL statement:
UPDATE Employee SET Salary = Salary + 1000.0 Where Department_id = 20M
What are the result in the EMP_LOG table?
A EMP_ID LOG_DATE NEW_SALARY ACTION
201 24-SEP-02 13000 NEW SAL 202 24-SEP-02 600 NEW SAL
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 9
Examine this code:
CREATE OR REPLACE PACKAGE metric_converter
IS
c_height CONSTRAINT NUMBER := 2.54;
c_weight CONSTRAINT NUMBER := .454;
FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER;
FUNCTION calc_weight (p_weight_in_pounds NUMBER)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY metric_converter
IS
FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_height_in_inches * c_height;
END calc_height;
FUNCTION calc_weight (p_weight_in_pounds NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_weight_in_pounds * c_weight
END calc_weight
END metric_converter;
/
CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_height_in_inches * metric_converter.c_height;
END calc_height;
/
Which statement is true?
CREATE OR REPLACE PACKAGE metric_converter
IS
c_height CONSTRAINT NUMBER := 2.54;
c_weight CONSTRAINT NUMBER := .454;
FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER;
FUNCTION calc_weight (p_weight_in_pounds NUMBER)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY metric_converter
IS
FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_height_in_inches * c_height;
END calc_height;
FUNCTION calc_weight (p_weight_in_pounds NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_weight_in_pounds * c_weight
END calc_weight
END metric_converter;
/
CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_height_in_inches * metric_converter.c_height;
END calc_height;
/
Which statement is true?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 10
You create a DML trigger. For the timing information, which are valid with a DML trigger?
Correct Answer: E
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).