PLSQL MULTIPLE CHOICE QUESTION Check your Knowledge Press

PL/SQL MULTIPLE CHOICE QUESTION

Check your Knowledge Press to Start www. prolearninghub. com

1. What is wrong in the following code snippet? DECLARE x number : = 1; BEGIN LOOP dbms_output. put_line(x); x : = x + 1; IF x > 10 THEN exit; END IF; dbms_output. put_line('After Exit x is: ' || x); END; There is nothing wrong. The IF statement is not required. There should be an END LOOP statement. The exit statement should be in capital letters. www. prolearninghub. com

2. Which of the following is the correct syntax for creating a VARRAY named grades, which can hold 100 integers, in a PL/SQL block? TYPE grades IS VARRAY(100) OF INTEGERS; VARRAY grades IS VARRAY(100) OF INTEGER; TYPE grades IS VARRAY(100) OF INTEGER; www. prolearninghub. com

3. What would be the output of the following code? DECLARE number; fn number; FUNCTION fx(x number)RETURN number IS f number; BEGIN IF x=0 THEN f : = 1; ELSE f : = x * fx(x-1); END IF; RETURN f; END; BEGIN num: = 5; fn : = fx(num); dbms_output. put_line(fn); END; 1 5 25 125 www. prolearninghub. com

4. Consider the exception declared as emp_exception 1 EXCEPTION; Which of the following statement will correctly call the exception in a PL/SQL block? IF c_id <= 0 THEN ex_invalid_id; IF c_id <= 0 THEN RAISE ex_invalid_id; IF c_id <= 0 THEN CALL ex_invalid_id; IF c_id <= 0 THEN EXCEPTION ex_invalid_id; www. prolearninghub. com

5. If an UNIQUE KEY constraint on DATE column is created, will it accept the rows that are inserted with SYSDATE? Will Wont May be None of these www. prolearninghub. com

6. Which of the following statement will create the specification for a package named cust_sal: CREATE PACKAGE BODY cust_sal AS PROCEDURE find_sal(c_id customers. id%type); END cust_sal; CREATE PACKAGE SPECIFICATION cust_sal AS PROCEDURE find_sal(c_id customers. id%type); END cust_sal; PACKAGE cust_sal AS PROCEDURE find_sal(c_id customers. id%type); www. prolearninghub. com

7. The collection method COUNT Returns the last (largest) index numbers in a collection that uses integer subscripts. Returns the number of elements that a collection currently contains. Checks the Maximum Size of a Collection. None of the above. www. prolearninghub. com

8. What will be the output of the following code? DECLARE lines dbms_output. chararr; num_lines number; BEGIN dbms_output. enable; dbms_output. put_line('Hello!'); dbms_output. put_line('Hope you are doing well!'); num_lines : = 2; dbms_output. get_lines(lines, num_lines); FOR i IN 1. . num_lines LOOP dbms_output. put_line(lines(i)); END LOOP; END; Hello! Hope you are doing well! Hello! Hope you He Ho Hello ! www. prolearninghub. com

9. Which of the following is true about data types in PL/SQL? Large Object or LOB data types are pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms. The composite data types have data items that have internal components that can be accessed individually. For example, collections and records. References are pointers to other data items. All of the above www. prolearninghub. com

10. What is wrong in the following code? DECLARE c_id : = 1; c_name customers. name%type; c_addr customers. address%type; BEGIN SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; END; You cannot use the SELECT INTO statement of SQL to assign values to PL/SQL variables. The SELECT INTO statement here is wrong. It should be: SELECT c_name, c_address INTO name, addr The WHERE statement is wrong. It should be: WHERE id : = c_id; The variable c_id should be declared as a type-compatible variable as − c_id customers. id%type : = 1; www. prolearninghub. co

11. What is wrong in the following code snippet? DECLARE x number : = 1; BEGIN LOOP dbms_output. put_line(x); x : = x + 1; IF x > 10 THEN exit; END IF; dbms_output. put_line('After Exit x is: ' || x); END; There is nothing wrong. The IF statement is not required. There should be an END LOOP statement. The exit statement should be in capital letters. www. prolearninghub. com

12. Which of the following is the correct syntax for creating a VARRAY named grades, which can hold 100 integers, in a PL/SQL block? TYPE grades IS VARRAY(100) OF INTEGERS; VARRAY grades IS VARRAY(100) OF INTEGER; TYPE grades IS VARRAY(100) OF INTEGER; www. prolearninghub. com

13. What would be the output of the following code? DECLARE number; fn number; FUNCTION fx(x number)RETURN number IS f number; BEGIN IF x=0 THEN f : = 1; ELSE f : = x * fx(x-1); END IF; RETURN f; END; BEGIN num: = 5; fn : = fx(num); dbms_output. put_line(fn); END; 1 5 25 125 www. prolearninghub. com

14. Which of the following code correctly create a record named book with two field title and author? TYPE book IS RECORD (title varchar(50), author varchar(50), ); RECORD book (title varchar(50), author varchar(50), ); CREATE TYPE book (title varchar(50), author varchar(50), ); www. prolearninghub. com

15. Which of the following holds true for the WHEN clause? Observe the syntax given below − CREATE [OR REPLACE ] TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements. EXCEPTION Exception-handling-statements END; This provides a condition for rows for which the trigger would fire and this clause is valid only for view based triggers. This provides a condition for rows for which the trigger would fire and this clause is valid only for row level triggers. This provides a condition for rows for which the trigger would fire and this clause is valid only for table level triggers. www. prolearninghub. com

16. Which of the following syntax will be used to access a package element? package_name element_name; element_name. package_na me; package_name. element_na me; None of these www. prolearninghub. com

17. The collection method LAST Returns the last (largest) index numbers in a collection that uses integer subscripts. Returns the number of elements that a collection currently contains. Checks the Maximum Size of a Collection. None of the above. www. prolearninghub. com

18. What would be printed when the following code is -10 10 executed? DECLARE x NUMBER; x : = 5; x : = 10; dbms_output. put_line(-x); dbms_output. put_line(+x); x : = -10; dbms_output. put_line(-x); dbms_output. put_line(+x); END; BEGIN 10 -10 -10 10 -01 10 -10 10 10 -10 www. prolearninghub. com

19. Which of the following is TRUE 1] Host variables are declared anywhere in the program 2] Host variables are declared in the DECLARE section Only 1 is TRUE Only 2 is TRUE Both 1 & 2 are TRUE Both are false www. prolearninghub. com

20. Which of the following is NOT VALID is PL/SQL Bool boolean; NUM 1, NUM 2 number; deptname dept. dname%type; date 1 date : = sysdate www. prolearninghub. com

21. What will be the value of svar after the execution ? Declare fvar number : = null; svar number : = 5 Begin goto << fproc>> if fvar is null then << fproc>> svar : = svar + 5 end if; End; Error 10 5 none www. prolearninghub. com

22. A Stored Procedure is an: Sequence of SQL or PL/SQL statements to perform specific function Stored in compiled form in the database Can be called from all client environments All of above www. prolearninghub. com

23. Which of the following statement is false Any procedure can raise an error and return a user message and error number Error number ranging from 20000 to 20999 are reserved for user defined messages Oracle checks Uniqueness of User defined errors Raise_Application_error is used for raising a user defined error. www. prolearninghub. com

24. Is it possible to open a cursor which is in a Package in another procedure? Yes No www. prolearninghub. com

25. Is it possible to use Transactional control statements in Database Triggers? Yes No www. prolearninghub. com

26. Is it possible to Enable or Disable a Database trigger? Yes No www. prolearninghub. com

27. PL/SQL supports datatype(s) Scalar data type Composite datatype All of these None of these www. prolearninghub. com

28. Find the ODD data type out VARCHAR 2 RECORD BOOLEAN RAW www. prolearninghub. com

29. Which of the following is not correct about the "TABLE" data type ? Can contain any no. of columns Simulates a Onedimensional array of unlimited size Column datatype of any Scalar type None of these www. prolearninghub. com

30. Find the ODD one out of the following OPEN CLOSE FETCH INSERT www. prolearninghub. com

31. Which of the following is not correct about Cursor? Cursor is a named Private SQL area Cursor holds temporary results Cursor is used for retrieving multiple rows SQL uses implicit Cursors to retrieve rows www. prolearninghub. com

32. Which of the following is NOT VALID in PL/SQL ? . Select. . . into Update CREATE Delete www. prolearninghub. com

33. What is the Result of the following 'VIK'||NULL||'RAM'? ERROR VIK RAM VIKRAM NULL www. prolearninghub. com

34. What will be the value of 'a' after execution ? Declare a number : = 5; b number : = null; c number : = 10; Begin if a > b AND a < c then a : = c * a; end if; End; 50 Null 5 None of these www. prolearninghub. com

35. Does the Database trigger will fire when the table is TRUNCATED? Yes No www. prolearninghub. com

36. SUBSTR(SQUARE ANS ALWAYS WORK HARD, 14, 6) will return ALWAYS S ALWAY WAYAL www. prolearninghub. com

37. REPLACE('JACK AND JUE', 'J', 'BL') will return JACK AND BLUE BLACK AND JACK BLACK AND BLUE None of the above www. prolearninghub. com

38. TRANSLATE ('333 SQD 234', '0123456789 ABC DPQRST', '0123456789') will return 333234 333334 334323 344344 www. prolearninghub. com

A 822 A 812 A 973 A 500 RAMASWAMY 3500 NARAYAN 5000 UMESH 2850 BALAJI 5750 39. Use these data for the following Questions Select SAL from EMP E 1 where 3 > ( Select count(*) from Emp E 2 where E 1. SAL > E 2. SAL ) will retrieve 3500, 5000, 2500 5000, 2850, 5750 5000, 5750 www. prolearninghub. com

40. Is it possible to modify a Data type of a column when column contains data? Yes No www. prolearninghub. com

41. Which of the following is not correct about a View ? To protect some of the columns of a table from other users Occupies data storage space To hide complexity of a query To hide complexity of a calculations www. prolearninghub. com

42. Which is not part of the Data Definition Language ? CREATE ALTER SESSION None of these www. prolearninghub. com

43. The Data Manipulation Language statements are INSERT UPDATE SELECT All of these www. prolearninghub. com

EMPNO ENAME SAL A 822 RAMASWAMY 3500 A 812 NARAYAN 5000 A 973 UMESH A 500 BALAJI 5750 44. Using the above data Select count(sal) from Emp will retrieve 1 0 3 None of these www. prolearninghub. com

45. What are the different events in Triggers? Define, create Drop, Comment Insert, Update, Delete All of the above www. prolearninghub. com

46. What built-in subprogram is used to manipulate images in image items? Zoom_out Zoom_in Image_zoom Zoom_image www. prolearninghub. com

47. Can we pass RECORD GROUP between FORMS? Yes No www. prolearninghub. com

48. SHOW_ALERT function returns Boolean Number Character None www. prolearninghub. com

49. What SYSTEM VARIABLE is used to refer DATABASE TIME ? $$dbtime$$ $$datetime$$ None of the above www. prolearninghub. com

50. Which of the following is not true about the exception handling section of a PL/SQL block? This section starts with the EXCEPTION keyword. It contains exception(s) that handle errors in the program. It is a mandatory section. None of the above www. prolearninghub. com

The Results