Mid Term 2 Plsql

1. The following example shows a valid record data type and variaable. True or False? TYPE DeptRecTyp IS RECORD (deptid NUMBER(4) NOT NULL := 99, dname departments.department_name%TYPE, loc departments.location_id%TYPE, region regions%ROWTYPE ); dept_rec DeptRecTyp; (1) Points True (*) False

Incorrect. Refer to Section 10 Lesson 3. 2. 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 IN INDEX BY BY BI BINARY_INTEGER; (1) Po Points emp_pkg.emprec_type; emprec_type.emp_pkg; v_emp_table emprec_type.emp_pkg; v_emp_table emp_pkg.emprec_type; (*) None of the above

Incorrect. Refer to Section 10 Lesson 3. 3. ckag ckage? e? (Cho (Choos ose e thr three ee) )

Which of the following are not allowed in a bodiless pa (1) (1) Poi Point nts s (Choose all correct answers)

Subprograms (*) Global variables Private variables (*)

Trusted by over 1 million members

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.











User-defined exceptions DML statements (*)

Incorrect. Refer to Section 10 Lesson 3. 4. Which of the following statements about a package initi alization block is true? (1) Points It cannot contain any SQL statements. It is an anonymous block at the end of a package body. (*) It is a procedure in a package that must be invoked before the rest of t he package can be used. It is an anonymous block in the package specification. It is executed automatically every time any global variable in the packa ge is referenced.

Correct 5. Functions called from a SQL query or DML statement must not end the current transaction, or create or roll back to a savepoint. True or False? (1) Points True (*) False

Incorrect. Refer to Section 10 Lesson 3. 6. Package MYPACK contains procedure MYPROC. You can see w hich parameters MYPROC uses by executing: DESCRIBE mypack.myproc. True or False? (1) Points











Correct

?

7. (1) Points

Which one of the following can NOT be part of a Package

Procedures Explicit cursors Triggers (*) Functions Global variables

Incorrect. Refer to Section 10 Lesson 1. 8. Which of the following are good reasons for creating an d using Packages? Related procedures, functions and variables can be grouped together as a single unit We can recompile the package body without having to recompile the specification We can create packages without needing any system privileges We can declare INDEX BY tables and use them as parameters (1) Points A and B A, B and C A and C A, B and D (*) A, B, C and D











All procedures and functions must be declared in the specification. (*) Cursors can be declared in the specification. The body contains the detailed code of the subprograms. Variables can be declared in the body. The specification must be created before the body.

Incorrect. Refer to Section 10 Lesson 1. 10. Package OLDPACK is in your schema. What will happen whe n the following statement is executed? DROP PACKAGE oldpack; (1) Points The body will be dropped but the specification will be retained. The specification will be dropped but the body will be retained. Both the specification and the body will be dropped. (*) The statement will fail because you must drop the body before you can dr op the specification.

Correct Section 10 11. Package NEWPACK contains several procedures and functio ns, including private function PRIVFUNC. From where can PRIVFUNC be invoked? (Ch oose two.) (1) Points (Choose all correct answers) From an anonymous block











From any function in NEWPACK (*) From any public procedure in another package

Incorrect. Refer to Section 10 Lesson 2. 12. In a package, public components are declared in the spe cification bu but pr private co components ar are no not. True or or Fa False? (1) Po Points True (*) False

Correct 13. We need to declare a package variable named MYVAR, whic h can be referenced by any subprogram in the package but can NOT be referenced f rom outside the package. In the following code, where should MYVAR be declared? CREATE OR REPLACE PACKAGE varpack IS -- Point A ... END varpack; CREATE OR REPLACE PACKAGE BODY varpack IS -- Point B PROCEDURE varproc IS -- Point C BEGIN ... END varproc; PROCEDURE ... ... -- Point D END varpack; (1) Points Point A Point B (*) Point C











Incorrect. Refer to Section 10 Lesson 2. 14. Your schema contains four packages, each having a speci fication and a body. You have also been granted privileges to access three packa ges (and their bodies) in other users' schemas. What will be displayed by the fo llowing query? SELECT COUNT(*) FROM ALL_OBJECTS WHERE object_type LIKE 'PACK%' AND owner <> USER; (1) Points 14 7 3 6 (*) 0

Incorrect. Refer to Section 10 Lesson 2.

Section 11 15. The UTL_FILE package can be used to create binary files such such as JPEG JPEGs s as as wel well l as as tex text t fil files es. . Tru True e or or Fal False se? ? (1) (1) Poi Point nts s True False (*)

Incorrect. Refer to Section 11 Lesson 2. 16. Which of the following exceptions can be raised ONLY whe n usi using ng the the UTL UTL_F _FIL ILE E pac packa kage ge? ? (Ch (Choo oose se two) two). . (1) (1) Poi Point nts s













NO_DATA_FOUND VALUE_ERROR READ_ERROR (*) E_MYEXCEP 17. Why is it better to use DBMS_OUTPUT only in anonymous b locks, not inside stored subprograms such as procedures? (1) Points Because DBMS_OUTPUT cannot be used inside procedures Because anonymous blocks display messages while the block is executing, while procedures do not display anything until their execution has finished Because DBMS_OUTPUT should be used only for testing and debugging PL/SQL code (*) Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a packaged procedure

Correct 18.

What will be displayed when the following code is execu

ted? BEGIN DBMS_OUTPUT.PUT('I do like'); DBMS_OUTPUT.PUT_LINE('to be'); DBMS_OUTPUT.PUT('beside the seaside'); END; (1) Points











I do like to be beside the seaside

Incorrect. Refer to Section 11 Lesson 2. 19. Package CURSPACK declares a global cursor in the packag e specification. The package contains three public procedures: OPENPROC opens th e cursor; FETCHPROC fetches 5 rows from the cursor's active set; CLOSEPROC close s the cursor. What will happen when a user session executes the following commands in the orde r shown? curspack.openproc; -- line 1 curspack.fetchproc; -- line 2 curspack.fetchproc; -- line 3 curspack.openproc; -- line 4 curspack.fetchproc; -- line 5 curspack.closeproc; -- line 6 (1) Points 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. An error will occur at line 2. An error will occur at line 4. (*)












Dick: 45, Hazel: 45 Dick: 45, Hazel: 0 Dick: 45, Hazel: null (*) Dick: 0, Hazel: 0 Both queries will fail because the syntax of DBMS_OUTPUT.PUT_LINE is inc orrect

Incorrect. Refer to Section 11 Lesson 1. Section 12 21.

Name two reasons for using Dynamic SQL.

(1) Poin

ts (Choose all correct answers) Provide the ability to execute SQL statements whose structure is unknown until execution time. (*) Provide the ability to handle mutating rows when executing a statement i nvolving the same table. Allow fetch of data for DML statements. Enables session-control statements to be written and executed from PL/SQ L. (*)











When the package is loaded into memory. Only the first time the procedure is executed.

Incorrect. Refer to Section 12 Lesson 1. 23. You want to create a function which drops a table. You write the following code: CREATE OR REPLACE FUNCTION droptab (p_tab_name IN VARCHAR2) RETURN BOOLEAN IS BEGIN DROP TABLE p_tab_name; RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END; Why will this procedure not compile successfully? (1) Points Because you can never drop a table from inside a function Because the PL/SQL compiler cannot check if the argument of p_tab_name i s a valid table-name (*) Because you do not have the privilege needed to drop a table Because you cannot use RETURN in the exception section












EXECUTE

Incorrect. Refer to Section 12 Lesson 1. 25. All but which of the following are benefits of using th e NOCOPY hint? (Choose two) (1) Points (Choose all correct answers) Safer because it uses passing by value. (*) Efficient since it uses less memory. Uses a larger block of server memory for faster access. (*) Faster because a single copy of the data is used. Eliminates extra processing.

Incorrect. Refer to Section 12 Lesson 2. 26. Where would you place the BULK COLLECT statement in the following example? DECLARE TYPE DeptRecTab IS TABLE OF departments%ROWTYPE; dept_recs DeptRecTab; CURSOR c1 IS SELECT department_id, department_name, manager_id, location_id -- Position A FROM departments













Position C (*)

Incorrect. Refer to Section 12 Lesson 2. 27. To create a list of the top 20 movies from a catalog of millions of titles, the following statement grabs those rows using a collection. True or False? ... TYPE nametab IS TABLE OF movies.title%TYPE; Title_tab nametab; ... SELECT title BULK COLLECT INTO title_tab FROM movies ORDER BY rental_count DESC; ... (1) Points True (*) False


e?

28. (1) Points

FORALL can be used with any DML statement. True or Fals











It prevents cascading triggers. It allows the trigger body code to be placed in a separate procedure or function. It allows both DML events and DDL events to be handled using a single tr igger.

Incorrect. Refer to Section 13 Lesson 4. 30. em? (Choose two.)

Which kinds of trigger can cause a mutating table probl (1) Points (Choose all correct answers)

BEFORE UPDATE row triggers (*) DDL triggers AFTER DELETE row triggers (*) Database Event triggers











Incorrect. Refer to Section 13 Lesson 4. 32. Examine this code: CREATE TRIGGER new_trigg AFTER CREATE ON reserved_word BEGIN ... Which of the following can be used in place of reserved_word? (Choose two.) (1) Points (Choose all correct answers) TABLE SCHEMA (*) USER DATABASE (*) TABLE employees











An attempt to add a row in the emp_details view (*) An attempt to add a row in the new_depts table.

Incorrect. Refer to Section 13 Lesson 3. 34. Examine the following code. To create a row trigger, wh at code should be included at Line A? CREATE TRIGGER dept_trigg AFTER UPDATE OR DELETE ON departments -- Line A BEGIN ... (1) Points AFTER EACH ROW FOR EVERY ROW FOR EACH ROW (*) ON EACH ROW ON EVERY ROW











CREATE OR REPLACE TRIGGER log_emps FOR UPDATE OF salary ON employees COMPOUND TRIGGER TYPE t_log_emp IS TABLE OF log_table%ROWTYPE INDEX BY BINARY_INTEGER; log_emp_tab t_log_emp; AFTER EACH ROW IS BEGIN -- some action END AFTER EACH ROW; AFTER STATEMENT IS BEGIN -- some action END AFTER STATEMENT; END log_emps; (1) Points The order of the timing statements is reversed. (*) The declaration section is missing the DECLARE keyword. The triggering event FOR UPDATE is not allowed. The COMPOUND TRIGGER statement is missing IS. There is nothing wrong with this example.













False

Correct 39. A business rule states that an employee's salary cannot be greater than 99,999.99 or less than 0. The best way to enforce this rule is by using: (1) Points A datatype of NUMBER(7,2) for the SALARY column A database trigger A check constraint (*) An application trigger











Where possible, use a trigger to enforce a foreign key constraint. Use triggers to override privilege checking and view other users' privat e tables. Do not use a trigger to replace or duplicate something which the Oracle Server does automatically. (*) Use triggers to prevent unauthorized users from SELECTing confidential d ata. Do not create a trigger that automatically fires another trigger. (*)












A PL/SQL subprogram that always returns exactly one value. A subprogram that is invoked explicitly by the calling application. A PL/SQL subprogram that inserts rows into a logging table.

Correct 44. What is wrong with the following code? CREATE TRIGGER dept_trigg BEFORE UPDATE OF department_name ON departments BEGIN DBMS_OUTPUT.PUT_LINE(:NEW.department_name); END; (1) Points You cannot use :NEW in a BEFORE trigger, only in an AFTER trigger.











You cannot use ROLLBACK inside a trigger. (*)

Nothing is wrong, this trigger will compile and execute successfully.

Incorrect. Refer to Section 13 Lesson 2. 46. A DML statement trigger fires only once for each trigge ring DML statement, while a row trigger fires once for each row processed by the trig trigge geri ring ng stat statem emen ent. t. True True or Fals False? e? (1) Poin Points ts True (*) False











ld you use?

(1) Points

ALTER TABLE departments DISABLE ALL TRIGGERS; (*) ALTER TRIGGER DISABLE ALL ON departments; ALTER TABLE departments DISABLE TRIGGERS; DISABLE ALL TRIGGERS ON departments; ALTER TABLE departments DROP ALL TRIGGERS;

Incorrect. Refer to Section 13 Lesson 5. 49.

User AYSEGUL successfully creates the following trigger













ALTER TRIGGER emp_trigg DISABLE; DROP TRIGGER emp_trigg; (*) REMOVE TRIGGER emp_trigg; None of the above

Mid Term 2 Plsql

Recommend Documents