QUESTION 1
The creation of which four database objects will cause a DDL trigger to fire?
A. Index
B. Cluster
C. Package
D. Function
E. Synonyms
F. Dimensions
G. Database links
Answer: A,,D,C,E
QUESTION 2
Which two program declarations are correct for a stored program unit? (Choose two)
A. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER
B. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER
C. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER)
D. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2)
E. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2))
Answer: A,C
QUESTION 3
You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. What type of trigger needs to be created?
A. DML trigger
B. System event trigger
C. INSTEAD OF trigger
D. Application trigger
Answer: B
QUESTION 4
You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table?
A. The trigger fires successfully.
B. The trigger fails because it needs to be a row level AFTER UPDATE trigger.
C. The trigger fails because a SELECT statement on the table being updated is not allowed.
D. The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.
Answer: C
QUESTION 5 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 creates a stored procedure named get_sal.
B. This statement returns a raise amount based on an employee id.
C. This statement creates a stored function named get_sal with a status of invalid.
D. This statement creates a stored function named get_sal.
E. This statement fails.
Answer: E
QUESTION 6 You need to disable all triggers on the EMPLOYEES table. Which command accomplishes this?
A. None of these commands; you cannot disable multiple triggers on a table in one command. B. ALTER TRIGGERS ON TABLE employees DISABLE;
C. ALTER employees DISABLE ALL TRIGGERS;
D. ALTER TABLE employees DISABLE ALL TRIGGERS;
Answer: D
QUESTION 7 An internal LOB is _____.
A. A table. B. A column that is a primary key. C. Stored in the database. D. A file stored outside of the database, with an internal pointer to it from a database column.
Answer: C
QUESTION 8 Examine this code:
CREATE OR REPLACE FUNCTION calc_sal (p_salary NUMBER)
RETURN NUMBER
IS
v_raise NUMBER (4, 2) DEFAULT 1.08 ;
BEGIN
RETURN v_raise * p_salary;
END calc_sal;/
Which statement accurately call the stored function CALC_SAL? (Choose two)
A. UPDATE employees (calc_sal(salary)) SET salary = salary * calc_sal(salary);
B. INSERT calc_sal(salary) INTO employees WHERE department_id = 60;
C. DELETE FROM employees(calc_sal(salary)) WHERE calc_sal(salary) > 1000;
D. SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60;
E. SELECT last_name, salary, calc_sal(salary) FROM employees ORDER BY calc_sal(salary);
Answer: D,E
QUESTION 9 This statement fails when executed:
CREATE OR REPLACE TRIGGER CALC_TEAM_AVG
AFTER INSERT
ON PLAYER
BEGIN
INSERT INTO PLAYER_BATSTAT (PLAYER_ID,
SEASON_YEAR,
AT_BATS,
HITS)
VALUES (:NEW.ID,
1997,
0,
0);
END;To which type must you convert the trigger to correct the error?
A. Row
B. Statement
C. ORACLE FORM trigger
D. Before
Answer: A
QUESTION 10 Examine this code: CREATE OR REPLACE PROCEDURE audit_emp (p_id IN emp_empno%TYPE) IS v_id NUMBER;
PROCEDURE log_exec IS
BEGIN
INSERT
INTO log_table (user_id, log_delete)
VALUES (USER, SYSDATE);
END log_exec;
v_name VARCHAR2(20);
BEGIN
DELETE FROM emp WHERE empno = p_id;
log_exec;
SELECT ename, empno INTO v_name, v_id FROM emp WHERE empno = p_id; END audit_emp; Why does this code cause an error when compiled?
A. An insert statement is not allowed in a subprogram declaration.
B. Procedure LOG_EXEC should be declared before any identifiers.
C. Variable v_name should be declared before declaring the LOG_EXEC procedure.
D. The LOG_EXEC procedure should be invoked as EXECUTE log_exec with the AUDIT_EMP procedure.
Answer: C
The creation of which four database objects will cause a DDL trigger to fire?
A. Index
B. Cluster
C. Package
D. Function
E. Synonyms
F. Dimensions
G. Database links
Answer: A,,D,C,E
QUESTION 2
Which two program declarations are correct for a stored program unit? (Choose two)
A. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER
B. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER
C. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER)
D. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2)
E. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2))
Answer: A,C
QUESTION 3
You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. What type of trigger needs to be created?
A. DML trigger
B. System event trigger
C. INSTEAD OF trigger
D. Application trigger
Answer: B
QUESTION 4
You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table?
A. The trigger fires successfully.
B. The trigger fails because it needs to be a row level AFTER UPDATE trigger.
C. The trigger fails because a SELECT statement on the table being updated is not allowed.
D. The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.
Answer: C
QUESTION 5 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 creates a stored procedure named get_sal.
B. This statement returns a raise amount based on an employee id.
C. This statement creates a stored function named get_sal with a status of invalid.
D. This statement creates a stored function named get_sal.
E. This statement fails.
Answer: E
QUESTION 6 You need to disable all triggers on the EMPLOYEES table. Which command accomplishes this?
A. None of these commands; you cannot disable multiple triggers on a table in one command. B. ALTER TRIGGERS ON TABLE employees DISABLE;
C. ALTER employees DISABLE ALL TRIGGERS;
D. ALTER TABLE employees DISABLE ALL TRIGGERS;
Answer: D
QUESTION 7 An internal LOB is _____.
A. A table. B. A column that is a primary key. C. Stored in the database. D. A file stored outside of the database, with an internal pointer to it from a database column.
Answer: C
QUESTION 8 Examine this code:
CREATE OR REPLACE FUNCTION calc_sal (p_salary NUMBER)
RETURN NUMBER
IS
v_raise NUMBER (4, 2) DEFAULT 1.08 ;
BEGIN
RETURN v_raise * p_salary;
END calc_sal;/
Which statement accurately call the stored function CALC_SAL? (Choose two)
A. UPDATE employees (calc_sal(salary)) SET salary = salary * calc_sal(salary);
B. INSERT calc_sal(salary) INTO employees WHERE department_id = 60;
C. DELETE FROM employees(calc_sal(salary)) WHERE calc_sal(salary) > 1000;
D. SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60;
E. SELECT last_name, salary, calc_sal(salary) FROM employees ORDER BY calc_sal(salary);
Answer: D,E
QUESTION 9 This statement fails when executed:
CREATE OR REPLACE TRIGGER CALC_TEAM_AVG
AFTER INSERT
ON PLAYER
BEGIN
INSERT INTO PLAYER_BATSTAT (PLAYER_ID,
SEASON_YEAR,
AT_BATS,
HITS)
VALUES (:NEW.ID,
1997,
0,
0);
END;To which type must you convert the trigger to correct the error?
A. Row
B. Statement
C. ORACLE FORM trigger
D. Before
Answer: A
QUESTION 10 Examine this code: CREATE OR REPLACE PROCEDURE audit_emp (p_id IN emp_empno%TYPE) IS v_id NUMBER;
PROCEDURE log_exec IS
BEGIN
INSERT
INTO log_table (user_id, log_delete)
VALUES (USER, SYSDATE);
END log_exec;
v_name VARCHAR2(20);
BEGIN
DELETE FROM emp WHERE empno = p_id;
log_exec;
SELECT ename, empno INTO v_name, v_id FROM emp WHERE empno = p_id; END audit_emp; Why does this code cause an error when compiled?
A. An insert statement is not allowed in a subprogram declaration.
B. Procedure LOG_EXEC should be declared before any identifiers.
C. Variable v_name should be declared before declaring the LOG_EXEC procedure.
D. The LOG_EXEC procedure should be invoked as EXECUTE log_exec with the AUDIT_EMP procedure.
Answer: C
