Purchase Oracle : 1Z0-147 Exam Materials and then pass exam easily

Oracle 1Z0-147 Exam Overview:

Oracle 1Z0-147 Exam Syllabus Topics:

Oracle9i program with pl/sql Sample Questions:

1. Procedure PROCESS_EMP references the table EMP.
Procedure UPDATE_EMP updates rows if table EMP through procedure PROCESS_EMP.
There is a remote procedure QUERY_EMP that queries the EMP table through the local
procedure PROCESS_EMP.
The dependency mode is set to TIMESTAMP in this session.
Which two statements are true? (Choose two)

A) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the second time.
B) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.
C) If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated.
D) If the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.
E) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the first time.

2. Examine this procedure:
CREATE OR REPLACE PROCEDURE INSERT_TEAM
(V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT 'AUSTIN', V_NAME in VARCHAR2)
IS
BEGIN
INSERT INTO TEAM (id, city, name)
VALUES (v_id, v_city, v_name);
COMMIT;
END
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

A) EXECUTE INSERT_TEAM (3, 'LONGHORNS');
B) EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');
C) EXECUTE INSERT_TEAM;
D) EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');
E) EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');

3. 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 fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.
B) The trigger fails because it needs to be a row level AFTER UPDATE trigger.
C) The trigger fires successfully.
D) The trigger fails because a SELECT statement on the table being updated is not allowed.

4. Which three statements are true regarding database triggers? (Choose three)

A) A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.
B) With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user.
C) A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database.
D) A database trigger needs to be executed explicitly whenever a particular event takes place.
E) A database trigger executes implicitly whenever a particular event takes place.

5. 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

A) EMP_ID LOG_DATE NEW_SALARY ACTION
201 24-SEP-02 14000 NEW SAL 202 24-SEP-02 7000 NEW SAL
B) No rows are inserted.
C) EMP_ID LOG_DATE NEW_SALARY ACTION
201 24-SEP-02 NEW SAL 202 24-SEP-02 NEW SAL

Solutions: