26. Which part of a database trigger determines the number of times the trigger
body executes?

Answers:• Trigger type

27. Which table should be queried to check the status of a function?

Answers:• USER_OBJECTS

28. Which of the following statements is true regarding stored procedures?

Answers:• A stored procedure must have at least one
executable statement in the procedure body

29. Examine the following 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?

Answers:• This is an invalid trigger

30. Which code is stored in the database when a procedure or function is created
in SQL*PLUS?

Answers:• Only P-CODE

31. Evaluate the following PL/SQL block:
DECLARE
v_low NUMBER:=2;
v_upp NUMBER:=100;
v_count NUMBER:=1;
BEGIN
FOR i IN v_low..v_low LOOP
INSERT INTO test(results)
VALUES (v_count)
v_count:=v_count+1;
END LOOP;
END;
How many times will the executable statements inside the FOR LOOP execute?

Answers:• 1

32. What can be done with the DBMS_LOB package?

Answers:• Use the DBMS_LOB.FILEEXISTS function to
find the location of a BFILE

33. Examine the following code:
CREATE OR REPLACE TRIGGER UPD_SALARY
FOR EACH ROW
BEGIN
UPDATE TEAM
SET SALARY=SALARY+:NEW.SALARY
WHERE ID=:NEW.TEAM_ID
END;
Which statement must be added to make this trigger executable after updating the
SALARY column of the PLAYER table?

Answers:• AFTER UPDATE ON PLAYER

34. How can migration be done from a LONG to a LOB data type for a column?

Answers:• Using ALTER TABLE statement

35. Examine the following code:

CREATE OR REPLACE PACKAGE comm_package IS
g_comm NUMBER := 10;
PROCEDURE reset_comm(p_comm IN NUMBER);
END comm_package;

36. The CHECK_SAL procedure calls the UPD_SAL procedure. Both procedures are
INVALID.Which command can be issued to recompile both procedures?

Answers:• ALTER PROCEDURE CHECK_SAL compile

37. Examine the following procedure:
PROCEDURE emp_salary
(v_bonus BOOLEAN,
V_raise BOOLEAN,
V_issue_check in out BOOEAN)
is
BEGIN
v_issue_check:=v_bonus or v_raise;
END;
If v_bonus=TRUE and v_raise=NULL,which value is assigned to v_issue_check?

Answers:• TRUE

38. Which package construct must be declared and defined within the packages
body?

Answers:• Private Procedure

39. What happens when rows are found using a FETCH statement?

Answers:• The current row values are loaded into
variables

40. Evaluate the following PL/SQL block:
DECLARE
result BOOLEAN;
BEGIN
DELETE FROM EMPloyee
WHERE dept_id IN (10,40,50);
result:=SQL%ISOPEN;
COMMIT:
END;

What will be the value of RESULT if three rows are deleted?

Answers:• FALSE

41. Which two statements among the following, regarding oracle database 10g
PL/SQL support for LOB migration, are true?

Answers:• Standard package functions accept LOBs as
parameters

42. Which command is used to disable all triggers on the EMPLOYEES table?

Answers:• ALTER TABLE employees DISABLE ALL TRIGGERS;

43. Which table and column can be queried to see all procedures and functions
that have been marked invalid?

Answers:• USER_OBJECTS table,STATUS column

44. SQL%ISOPEN always evaluates to false in case of a/an:

Answers:• Implicit Cursor

45. Which datatype does the cursor attribute '%ISOPEN' return?

Answers:• BOOLEAN

46. Which of the following is a benefit of using procedures and functions?

48. Which type of variable should be used to assign the value TRUE, FALSE?

Answers:• Scalar

49. In which type of trigger can the OLD and NEW qualifiers can be used?

Answers:• Row level DML trigger

50. Examine the following code:
CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE);
END;
/
An UPDATE command is issued in the EMP table that results in changing 10 rows
How many rows are inserted into the AUDIT_TABLE ?