General Oracle PL/SQL 9i MCQs

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

2. Which two programming constructs can be grouped within a package?

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

4. A CALL statement inside the trigger body enables you to call:

5. An internal LOB is _____.

6. In Explicit lock, two database operations wait for each other to release a lock.

7. Examine the following function: CREATE OR REPLACE FUNCTION PLAYER_AVG (V_ID in PLAYER.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS INTO V_AVG FROM PLAYER WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which of the following statements will successfully invoke this function in SQL *Plus?

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

9. Which one is not a legal declaration?

10. A function CALTAX is given below: CREATE OR REPLACE FUNCTION caltax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END CALTAX; Which of the following statements is correct in case you want to run this function from the SQL *Plus prompt?

11. You need to drop a table from within a stored procedure. How would you do this?

12. Examine the following code: CREATE OR REPLACE PROCEDURE add_dept ( p_name dept.dname%TYPE DEFAULT 'unknown', p_loc dept.locE%TYPE DEFAULT 1700) IS BEGIN INSERT INTO dept VALUES (dept_seq.NEXTVAL,p_name, p_loc); END add_dept; / You created the add_dept procedure above. Now you want to invoke the procedure in SQL *Plus. Which of the following are the valid invocations?

13. Which lock is used in Pl/Sql if the where clause evaluates to a set of data?

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

15. Examine the code given below: 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 this?

16. You need to create a trigger on the EMP table, which monitors every row that is changed and places this information in the AUDIT_TABLE. What type of trigger would you create?

17. Which type of argument passes a value from a procedure to the calling environment?

18. Which of the following Triggers is fired each time a row in the table is affected by the triggering statement?

19. Which exception is raised when the data type or data size is invalid?

20. The Technique employed by the Oracle engine to protect table data when several people are accessing is called:

21. The OLD and NEW qualifiers can be used in Row level system trigger.

22. Examine the Block given below: declare v_no number:=2; v_msg varchar2(20):='Goodbye'; begin case When v_no=1 then dbms_output.put_line('One!'); dbms_output.put_line('Another One!'); When v_no>1 then dbms_output.put_line('>1!'); dbms_output.put_line('Still>1!'); when v_msg='Goodbye' then dbms_output.put_line('Goodbye'); dbms_output.put_line('Adios'); else dbms_output.put_line('No Match'); end case; end; / What is the output of this block?

23. Which cursor allows passing values dynamically to a cursor?

24. Examine the code given below: 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) ||.@Oracle.com .; UPDATE employees SET email = email_name WHERE employee_id = id; RETURN email_name; END; Which of the following statements removes the function?

25. __________ is present in Oralce9i only.

26. Which statements is correct with regard to stored procedures?

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

28. Which program declarations are correct for a stored program unit?

29. Examine the code given below: Declare cursor emps is select Empno,ename,sal,deptno,job from emp; begin for rec in emps loop open emps; dbms_output.put_line(rec.empno||rec.ename||rec.sal||rec.deptno||rec.job); end loop; end; / What is wrong in above declaration?

30. Which statement is correct?

31. Examine the code given below: CREATE OR REPLACE TRIGGER update_emp AFTER UPDATE ON emp BEGIN INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE); END; / You issue an UPDATE command in the EMP table, which results in changing 10 rows. How many rows are inserted in the AUDIT_TABLE ?

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

33. Which statements are correct with regard to packages?

34. Which command is used to disable all the triggers on an EMP table?

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

36. Examine the package given below: CREATE OR REPLACE PACKAGE discounts IS g_id NUMBER := 7829; discount_rate NUMBER := 0.00; PROCEDURE display_price (p_price NUMBER); END discounts; / CREATE OR REPLACE PACKAGE BODY discounts IS PROCEDURE display_price (p_price NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE( .Discounted .|| TO_CHAR(p_price*NVL(discount_rate, 1))); END display_price; BEGIN discount_rate :=0.10; END discounts; / Which of the following statements is correct?

37. Examine the code given below: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(g_comm IN NUMBER); END comm_package; User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which of the following statements is correct?

38. Which table should be queried to determine when a procedure was last compiled?

39. Consider the following code: declare v_id number(3):=50; V_message(30):='Product 10012'; begin declare v_id number(3):=40; V_message(30):='welcome to world'; v_locn:='Europe; begin v_id:=v_id+20; end; end; Determine the value of V_ID variable in the outer block.

40. The process of Breaking Sql sentence into words and then checking them for syntax and object privileges is called:

41. SQL%ISOPEN always evaluates to false in case of:

42. Which cursor attribute returns the number of rows fetched from the active set in the case of an explicit cursor?

43. Database triggers are designed:

44. How can you migrate from a LONG to a LOB data type for a column?