Highest Quality of 1z0-147 exam answers materials and practice for Oracle certification for examinee, Real Success Guaranteed with Updated 1z0-147 pdf dumps vce Materials. 100% PASS oracle9i program with pl/sql exam Today!
2021 May 1z0-147 Study Guide Questions:
Q11. Examine the trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DELCARE
n INTEGER;
BEGIN
SELECT COUNT(*)
INTO n
FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || a ||
' employees,');
END;
This trigger results in an error after this SQL statement is entered:
DELETE FROM Emp_tab WHERE Empno = 7499;
How do you correct the error?
A. Change the trigger type to a BEFORE DELETE.
B. Take out the COUNT function because it is not allowed in a trigger.
C. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.
D. Change the trigger to a statement-level trigger by removing FOR EACH ROW.
Answer: D
Q12. Examine this procedure:
CREATE OR REPLACE PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2)
IS
BEGIN
INSERT INTO PLAYER (ID,LAST_NAME)
VALUES (V_ID, V_LAST_NAME);
COMMIT;
END;
This procedure must invoke the APD_BAT_STAT procedure and pass a parameter.
Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?
A. EXECUTE UPD_BAT_STAT(V_ID);
B. UPD_BAT_STAT(V_ID);
C. RUN UPD_BAT_STAT(V_ID);
D. START UPD_BAT_STAT(V_ID);
Answer: B
Q13. All users currently have the INSERT privilege on the PLAYER table. You only want your users to insert into this table using the ADD_PLAYTER procedure. Which two actions must you take? (Choose two)
A. GRANT SELECT ON ADD_PLAYER TO PUBLIC;
B. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;
C. GRANT INSERT ON PLAYER TO PUBLIC;
D. GRANT EXECUTE,INSERT ON ADD_PLAYER TO PUBLIC;
E. REVOKE INSERT ON PLAYER FROM PUBLIC;
Answer: BE
Updated 1z0-147 real exam:
Q14. What can you do with the DBMS_LOB package?
A. Use the DBMS_LOB.WRITE procedure to write data to a BFILE.
B. Use the DBMS_LOB.BFILENAME function to locate an external BFILE.
C. Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE.
D. Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.
Answer: D
Q15. Examine this code
CREATE OR REPLACE FUNCTION change_dept
(p_old_id NUMBER, p_deptname VARCHAR2)
RETURN NUMBER
IS
V_new_id NUMBER;
BEGIN
SELECT departments_seq.nextval
INTO v_new_id
FROM dual;
UPDATE departments
SET departmenet_id = v_new_id,
Department_name = p_deptname
WHERE department_id = p_old_id;
Return v_new_id;
END;
/
There are no foreign key integrity constraints on the EMPLOYEES and DEPARTMENTS tables.
Which statement performs a successful update to the EMPLOYEES table?
A. UPDATE departments
SET department_id = change_dept(10, 'Finance')
Where department_id = 10;
B. UPDATE employees
SET department_id = change_dept(10, 'Finance')
Where department_id = 10;
C. UPDATE departments
change_dept(270, 'Outsource')
Where department_name = 'payroll'
D. UPDATE employees
SET department_id = change_dept(10, 'Finance')
Where department_id = DEPARTMENTS:CURRVAL;
Answer: B
Q16. What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?
A. The rows are selected and ordered.
B. The validity of the SQL statement is established.
C. An area of memory is established to process the SQL statement.
D. The SQL statement is run and the number of rows processed is returned.
E. The area of memory established to process the SQL statement is released.
Answer: D
Guaranteed 1z0-147 prep:
Q17. You have an AFTER UPDATE row-level on the table EMP. The trigger queries the EMP table and inserts the updating user's information into the AUDIT_TABLE.
What happens when the user updates rows on the EMP table?
A. A compile time error occurs.
B. A runtime error occurs. The effect of trigger body and the triggering statement are rolled back.
C. A runtime error occurs. The effect of trigger body is rolled back, but the update on the EMP table takes place.
D. The trigger fires successfully. The update on the EMP table occurs, and data is inserted into theAUDIT_TABLE table.
E. A runtime error occurs. The update on the EMP table does not take place, but the insert into the AUDIT_TABLE occurs.
Answer: B
Q18. Which four triggering events can cause a trigger to fire? (Choose four)
A. A specific error or any errors occurs.
B. A database is shut down or started up.
C. A specific user or any user logs on or off.
D. A user executes a CREATE or an ALTER table statement.
E. A user executes a SELECT statement with an ORDER BY clause.
F. A user executes a JOIN statement that uses four or more tables.
Answer: ABCD
Q19. Examine this code:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE LOGON ON employees
BEGIN
IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR
(TO_CHAR(SYSDATE, 'HH24:MI')
NOT BETWEEN '08:00' AND '18:00')
THEN RAISE_APPLICATION_ERROR (-20500, 'You may
insert into the EMPLOYEES table only during
business hours.');
END IF;
END;
/
What type of trigger is it?
A. DML trigger
B. INSTEAD OF trigger
C. Application trigger
D. System event trigger
E. This is an invalid trigger.
Answer: E
Q20. To be callable from a SQL expression, a user-defined function must do what?
A. Be stored only in the database.
B. Have both IN and OUT parameters.
C. Use the positional notation for parameters.
D. Return a BOOLEAN or VARCHAR2 data type.
Answer: C