Oracle9i program with pl/sql - 1Z0-147 Exam Questions
QUESTION NO: 1
Examine this code
CREATE OR REPLACE PROCEDURE load bfile (p_flle_loc IN VARCHAR2)
IS
V_file BFILE;
V_filename VARCHAR2 (16);
CURSOR emp_cursor IS
SELECT employee_id
FROM employees
WHERE Job_id = 'IT_PROG'
FOR UPDATE;
BEGIN
FOR emp_record IN emp_cursor LOOP
V_filename:= emp_record.employee_id || '.GIF';
V_file := BFILENAME(p_flle_loc, v_filename);
END LOOP;
END;
/
What does the BFILENAME function do?
Examine this code
CREATE OR REPLACE PROCEDURE load bfile (p_flle_loc IN VARCHAR2)
IS
V_file BFILE;
V_filename VARCHAR2 (16);
CURSOR emp_cursor IS
SELECT employee_id
FROM employees
WHERE Job_id = 'IT_PROG'
FOR UPDATE;
BEGIN
FOR emp_record IN emp_cursor LOOP
V_filename:= emp_record.employee_id || '.GIF';
V_file := BFILENAME(p_flle_loc, v_filename);
END LOOP;
END;
/
What does the BFILENAME function do?
Correct Answer: C
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 2
Examine the trigger heading:
CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OF sal, job ON emp FOR EACH ROW
Under which condition does this trigger fire?
Examine the trigger heading:
CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OF sal, job ON emp FOR EACH ROW
Under which condition does this trigger fire?
Correct Answer: A
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 3
Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?
Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?
Correct Answer: D
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 4
The creation of which database objects will cause a DDL trigger to fire? (Choose all that apply)
The creation of which database objects will cause a DDL trigger to fire? (Choose all that apply)
Correct Answer: A,C,E,F,G
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 5
Under which two circumstances do you design database triggers? (Choose two)
Under which two circumstances do you design database triggers? (Choose two)
Correct Answer: B,D
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 6
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?
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?
Correct Answer: C
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
