General Oracle PL/Sql 10g MCQs

1. In Pl/Sql, if the where clause evaluates to a set of data, which lock is used?

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

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

4. Which of the following statements is true?

5. Which Section deals with handling of errors that arise during execution of the data manipulation statements, which makeup the PL/SQL Block?

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

7. Which precomplied word is called, which when encountered, immediately binds the numbered exception handler to a name?

8. 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 ?

9. If user defined error condition exists, Which of the following statements made a call to that exception?

10. Which table should be queried to determine when the procedure was last compiled?

11. 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?

12. A table has to be dropped from within a stored procedure. How can this be implemented?

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

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

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

16. Which two among the following programming constructs can be grouped within a package?

17. What is the maximum number of handlers processed before the PL/SQL block is exited, when an exception occurs?

18. All packages can be recompiled by using an Oracle utility called:

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

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

21. Which two statements, among the following, describe the state of a package variable after executing the package in which it is declared?

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

23. What type of trigger is created on the EMP table that monitors every row that is changed, and places this information into the AUDIT_TABLE?

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

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

26. When the procedure or function is invoked, the Oracle engine loads the compiled procedure or function in the memory area called:

27. 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?

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

29. The technique employed by the Oracle engine to protect table data, when several people are accessing it is called:

30. Which two statements out of the following regarding packages are true?

31. 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?

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

33. Which of the following are identified by the 'INSTEAD OF' clause in a trigger?

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

35. An internal LOB is _____.

36. Which of the following characteristics do classes in the System. Drawing namespace such as Brush, Font, Pen, and Icon share?

37. Which of the following characteristics is present in the DateTime type?

38. Which of the following are true about System.GC under version 3.5 of the Framework?

39. Which of the following do the advantages of Lambda Expressions over Anonymous methods include?

40. Which of the following statements do Expression Trees fit best?

41. Which of the following is not a legal declaration?

42. Examine the following trigger: CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Employee FOR EACH ROW DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM employee; DMBS_OUTPUT.PUT_LINE( 'There are now' || n || 'employees'); END; This trigger results in an error after this SQL statement is entered: DELETE FROM Employee WHERE Empno = 7499; How should the error be corrected?

43. Examine the following code: CREATE OR REPLACE FUNCTION gen_email (first_name VARCHAR2, last_name VARCHAR2, id NUMBER) RETURN VARCHAR2 IS email_name VARCHAR2(19); BEGIN email_name := SUBSTR(first_name, 1, 1) || SUBSTR(last_name, 1, 7) ||[email protected] .; UPDATE employees SET email = email_name WHERE employee_id = id; RETURN email_name; END; Which of the following statements removes the function?

44. CREATE OR REPLACE PACKAGE manage_emp IS tax_rate CONSTANT NUMBER(5,2) := .28; v_id NUMBER; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER); PROCEDURE delete_emp; PROCEDURE update_emp; FUNCTION cal_tax (p_sal NUMBER) RETURN NUMBER; END manage_emp; / CREATE OR REPLACE PACKAGE BODY manage_emp IS PROCEDURE update_sal (p_raise_amt NUMBER) IS BEGIN UPDATE emp SET sal = (sal * p_raise_emt) + sal WHERE empno = v_id; END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS BEGIN INSERT INTO emp(empno, deptno, sal) VALUES (v_id, p_depntno, p_sal); END insert_emp; PROCEDURE delete_emp IS BEGIN DELETE FROM emp WHERE empno = v_id; END delete_emp; PROCEDURE update_emp IS v_sal NUMBER(10,2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = v_id; IF v_sal < 500 THEN v_raise := .05; ELSIP v_sal < 1000 THEN v_raise := .07; ELSE v_raise := .04; END IF; update_sal(v_raise); END update_emp; FUNCTION cal_tax (p_sal NUMBER)RETURN NUMBER IS BEGIN RETURN p_sal * tax_rate; END cal_tax; END manage_emp; / What is the name of the private procedure in this package?

45. Which procedure is called after a row has been fetched to transfer the value, from the select list of the cursor into a local variable?

46. Which cursor dynamically allows passing values to a cursor while opening another cursor?

47. What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?

48. Examine the following package specification: CREATE OR REPLACE PACKAGE combine_all IS v_string VARCHAR2(100); PROCEDURE combine (p_num_val NUMBER); PROCEDURE combine (p_date_val DATE); PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER); END combine_all; / Which overloaded COMBINE procedure declaration can be added to this package specification?

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

50. 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?

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

52. What can be done with the DBMS_LOB package?

53. 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?

54. 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; User MILLER executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_package.g_comm := 20 Which of the following statement is true?

55. The oracle server implicitly opens a cursor to process: