Semester 2 Midterm Exam PL/SQL Questions With Complete Solutions.

Which one of the following can NOT be part of a Package ? Procedures Global variables Explicit cursors Triggers (****) Functions Which of the following can be included in a package? procedures variables PL/SQL types Exceptions All of the above (****) To be able to invoke a package subprogram from outside the package, it must be declared in the package: Body Specification Body and the specification (****) None of the above What is wrong with the following syntax for creating a package specification? CREATE OR REPLACE PACKAGE mypack IS g_constant1 NUMBER(6) := 100; FUNCTION func1 (p_param1 IN VARCHAR2); FUNCTION func2; END mypack; You cannot declare constants in the specification. The first line should be: CREATE OR REPLACE PACKAGE SPECIFICATION mypack IS Nothing is wrong, this code contains no errors. The RETURN datatype of the functions must be specified. (****) A package must contain at least one procedure. Examine the following package code: CREATE OR REPLACE PACKAGE over_pack IS PROCEDURE do_work1 (p1 IN VARCHAR2, p2 IN NUMBER); PROCEDURE do_work2 (p1 IN VARCHAR2, p2 IN NUMBER); PROCEDURE do_work1 (param1 IN CHAR, param2 IN NUMBER); FUNCTION do_work2 (param1 IN VARCHAR2, param2 IN NUMBER) RETURN DATE; END over_pack; Which of the following calls will be successful? (Choose three.) Mark for Review (1) Points (Choose all correct answers) v_date := over__work2('Smith',20); over__work1(p1='Smith',p2=20); (****) over__work1('Smith',20); over__work2('Smith',20); (***) over__work1(param1='Smith'); Which of the following are not allowed in a bodiless package? (Choose three) DML statements (***) User-defined exceptions Private variables (***) Global variables Subprograms (***) A bodiless package contains what? Public variables only (***) Private variables only Functions only Procedures only The following example package specification is valid to create a data type ed_type that can be used in other subprograms. True or False? CREATE OR REPLACE PACKAGE emp_dept_pkg IS TYPE ed_type IS RECORD (f_name _name%TYPE, l_name _name%TYPE, d_name tment_name%TYPE); PROCEDURE sel_emp_dept (p_emp_id IN yee_id%TYPE, p_emp_dept_rec OUT ed_type); END emp_dept_pkg; True (***) False How would you invoke the constant km_to_mile from the global_consts bodiless package at VARIABLE A? SELECT trail_name, distance_in_km * VARIABLE A FROM trails WHERE park_name = 'YOSEMITE'; km_to_mile (global_consts) global__to_mile (***) global_consts (km_to_mile) km_to_l_consts Examine the following package specification: CREATE OR REPLACE PACKAGE taxpack IS CURSOR empcurs IS SELECT * FROM employees; PROCEDURE taxproc; END mypack; The package body of TAXPACK also includes a function called TAXFUNC. Which one of the following statements is NOT true? TAXPROC is a public procedure and TAXFUNC is a private function. TAXPROC can invoke TAXFUNC if TAXPROC is coded before TAXFUNC. TAXPROC can open the cursor. The procedure can be invoked by: BEGIN oc; END; The packaage will not compile because you cannot declare a cursor in the specification. (***) Examine the following package specification: CREATE OR REPLACE PACKAGE mypack IS percent_tax NUMBER := 20; PROCEDURE proc1; END mypack; The package body of mypack also includes a function called func1. Which of the following statements are true? (Choose three.) he variable can be modified by: BEGIN nt_tax := 10; END; (***) proc1 is a public procedure and func1 is a private function. (***) The function can be invoked from outside the package. The package will not compile because you cannot declare variables in the specification, only procedures and functions. . The procedure can be invoked by: BEGIN 1; END; (***) When one component of a package is called, all the package's components are loaded into memory. True or False? True (***) False Your schema contains a package called EMP_PKG. You want to remove the package body but not the specification. The correct syntax to do this is: DROP BODY emp_pkg; True or False? True False (***) SCOTT's schema contains a package EMP_PKG which contains a public procedure EMP_SAL which accepts a NUMBER parameter. Which of the following will invoke the procedure successfully? emp__sal(101); __sal(101): (***) emp_sal(101); None of the above All of the above The DBMS_OUTPUT package is useful for which of the following activities? (Choose two) Display results to the developer during testing for debugging purposes (***) Write operating system text files to the user's screen Interact with a user during execution of a function or procedure Trace the code execution path for a function or procedure (***) Which DBMS_OUTPUT package subprogram places text into the buffer at Line 1? (Choose one) IF v_bool1 AND NOT v_bool2 AND v_number 25 THEN --Line 1 ELSE ... END IF; DBMS_OUTPUT.NEW_LINE; DBMS_OUTPUT.PUT('IF branch was executed'); (***) DBMS_OUTPUT.GET_LINE('IF branch was executed'); DBMS_OUTPUT.PUT_LINE('IF branch was executed'); DBMS_OUTPUT.NEW_LINE('IF branch was executed'); Which of the following exceptions can be raised ONLY when using the UTL_FILE package? (Choose two.) NO_DATA_FOUND E_MYEXCEP VALUE_ERROR READ_ERROR (***) INVALID_PATH (***) What will be displayed when the following code is executed? BEGIN DBMS_OUTPUT.PUT('I do like'); DBMS_OUTPUT.PUT_LINE('to be'); DBMS_OUTPUT.PUT('beside the seaside'); END; I do like to be I do liketo be (***) I do like to be beside the seaside I do like to be beside the seaside I do like to be beside the seaside Package CURSPACK declares a global cursor in the package specification. The package contains three public procedures: OPENPROC opens the cursor; FETCHPROC fetches 5 rows from the cursor's active set; CLOSEPROC closes the cursor. What will happen when a user session executes the following commands in the order shown? roc; -- line 1 proc; -- line 2 proc; -- line 3 roc; -- line 4 proc; -- line 5 proc; -- line 6 An error will occur at line 2. An error will occur at line 4. (***) The first 15 rows will be fetched. The first 10 rows will be fetched, then the first 5 rows will be fetched again. The first 5 rows will be fetched three times. A cursor's state is defined only by whether it is open or closed and, if open, how many rows it holds. True or False? True False (***) Package OLDPACK is in your schema. What will happen when the following statement is executed? DROP PACKAGE oldpack; The body will be dropped but the specification will be retained. The statement will fail because you must drop the body before you can drop the specification. Both the specification and the body will be dropped. (***) The specification will be dropped but the body will be retained. Which one of the following queries would you use to see the detailed code of a package called EMP_PKG? SELECT text FROM all_source WHERE name = 'EMP_PKG' AND type = 'PACKAGE' ORDER BY line; SELECT text FROM user_source WHERE name = 'EMP_PKG' AND type = 'PACKAGE BODY' ORDER BY line; (***) SELECT text FROM user_source WHERE name = 'EMP_PKG' AND type = 'PACKAGE' ORDER BY line; SELECT source FROM user_packages WHERE name = 'EMP_PKG' AND type = 'PACKAGE BODY' ORDER BY line; A local variable declared within a procedure in a package can be referenced by any other component of that package. True or False? True False (***) A package contains both public and private subprograms. Which one of the following statements is true? If three users invoke three different subprograms in the package, there will be three copies of the code in memory. The public subprograms are all loaded into memory at the same time, but the private subprograms are loaded into memory one at a time as they are invoked. The whole package is loaded into memory when the first call is made to any subprogram in the package. (***) Each subprogram is loaded into memory when it is first invoked A public component declared in the package specification can be referenced by a private component defined in the package body. True or False? True (***) False Which part of a package must be created first, the specification or the body? It does not matter which is created first. The body can be created first, but only if the package has no specification. The specification and body must be created at the same time. The specification (***) The body Package EMP_PACK contains two procedures, DEL_EMP and SHOW_EMP. You want to write an anonymous block which invokes these procedures but you have forgotten which parameters they use. Which of the following will give you this information? DESCRIBE del_emp DESCRIBE show_emp DESCRIBE emp_pack(del_emp, show_emp) DESCRIBE emp_pack(***) DESCRIBE emp__emp DESCRIBE emp__emp None of the above Which of the following are good reasons for creating and using Packages? A. Related procedures, functions, and variables can be grouped together as a single unit B. We can recompile the package body without having to recompile the specification C. We can create packages without needing any system privileges D. We can declare INDEX BY tables and use them as parameters A, B, and D (***) A and C A, B, C, and D A, B, and C A and B The two parts of a package are stored as separate objects in the database. True or False? True (***) False Which of the following best describes a package initialization block? Because it is an anonymous block, it cannot be invoked and therefore will never execute. It is treated as a set of comments. It is an anonymous block in the package specification. It is a private function within the package body. It is a named procedure in a package which must be invoked by a user before any other part of the package can be invoked. It is an anonymous block at the end of a package body which executes automatically the first time each user session invokes a subprogram in the package. (***) A package initialization block is executed automatically every time a user invokes any procedure or function in the package. True or False? True False (***) What is the correct format to declare a variable using the following emp_pkg package composite data type? TYPE emprec_type IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; emp_c_type; emprec__pkg; v_emp_table emprec__pkg; v_emp_table emp_c_type; (***) None of the above Which two of these declarations cannot be in the same package specification? 1. PROCEDURE myproc (p1 NUMBER, p2 VARCHAR2); 2. PROCEDURE myproc (p1 VARCHAR2, p2 NUMBER); 3. PROCEDURE myproc (p1 NUMBER, p2 CHAR); 4. PROCEDURE myproc (p1 NUMBER); 2 and 3 1 and 3 (***) 1 and 2 3 and 4 1 and 4 We never need to use a forward declaration when invoking a public subprogram. True or False? True (***) False The UTL_FILE package can be used to read and write binary files such as JPEGs as well as text files. True or False? True False (***) The UTL_FILE package can be used to create binary files such as JPEGs as well as text files. True or False? True False (***) The DBMS_OUTPUT gives programmers an easy-to-use interface to see, for instance, the current value of a loop counter, or whether or not a program reaches a particular branch of an IF statement. (True or False?) True (***) False