• Author / Uploaded
• Ovie Widiyastuti

Citation preview

Test: Section 10 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 10 Quiz (Answer all questions in this section) 1.Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?

Mark for Review (1) Points

True (*) False Correct 2.Which of the following statements about packages is NOT true ?

Mark for Review (1) Points

Variables can be declared in the body. The specification must be created before the body. All procedures and functions must be declared in the specification. (*) The body contains the detailed code of the subprograms. Cursors can be declared in the specification. Correct 3.Which part of a package must be created first, the specification or the body?

Mark for Review (1) Points

The body The specification and body must be created at the same time. The body can be created first, but only if the package has no specification. It does not matter which is created first. The specification (*) Correct 4.Which one of the following can NOT be part of a Package ?

Explicit cursors Procedures Global variables Triggers (*) Functions Correct

Mark for Review (1) Points

5.Which of the following can be included in a package?

Mark for Review (1) Points

procedures variables PL/SQL types Exceptions All of these. (*) Correct Section 10 Quiz (Answer all questions in this section) 6. Which of the following best describes a package initialization block?

Mark for Review (1) Points

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 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 in the package specification. 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. (*) It is a private function within the package body. Correct 7. 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?

Mark for Review (1) Points

True (*) False Correct 8. The following example shows a valid record data type and variable. 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.region_id%TYPE ); dept_rec DeptRecTyp;

Mark for Review (1) Points

True (*) False Correct 9. Which two of these functions could not be in the same package? 1. FUNCTION get_emp (p1 DATE) RETURN VARCHAR2;

Mark for Review (1) Points

2. FUNCTION get_emp (p1 DATE, p2 NUMBER) RETURN VARCHAR2; 3. FUNCTION get_emp (p1 DATE, p2 NUMBER) RETURN NUMBER; 4. FUNCTION get_emp (p1 NUMBER, p2 DATE) RETURN VARCHAR2; 2 and 4 2 and 3 (*) 1 and 4 1 and 2 3 and 4 Correct 10.A package initialization block is executed automatically every time a user invokes any procedure or function in the package. True or False?

Mark for Review (1) Points

True False (*) Correct Section 10 Quiz (Answer all questions in this section) 11.A package contains both public and private subprograms. Which one of the following statements is true?

Mark for Review (1) Points

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. 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. Correct 12.We want to remove both the specification and the body of package CO_PACK from the database. Which of the following commands will do this?

Mark for Review (1) Points

DROP PACKAGE SPECIFICATION co_pack; DROP PACKAGE BODY co_pack; DROP BOTH co_pack; DROP PACKAGE co_pack; (*) None of these. Correct 13.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? scott.emp_pkg.emp_sal(101): (*)

Mark for Review (0) Points

All of these. emp_pkg.emp_sal(101); emp_sal(101); None of these. Correct 14.When a change is made to the detailed code of a public procedure in a package (but not to the procedure's name or parameters), both the specification and the body must be recompiled. True or False?

Mark for Review (1) Points

True False (*) Correct 15.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.) (Choose all correct answers) The procedure can be invoked by: BEGIN mypack.proc1; END; (*) The variable can be modified by: BEGIN mypack.percent_tax := 10; END; (*) 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. . proc1 is a public procedure and func1 is a private function. (*) Correct

Mark for Review (1) Points

Test: Section 11 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 11 Quiz (Answer all questions in this section) 1.Package MULTIPACK declares the following global variable: g_myvar NUMBER;

Mark for Review (1) Points

User DICK executes the following: multipack.g_myvar := 45; User HAZEL now connects to the database. Both users immediately execute: BEGIN DBMS_OUTPUT.PUT_LINE(multipack.g_myvar); END; What values will Dick and Hazel see? Dick: 45, Hazel: 0 Dick: 45, Hazel: 45 Dick: 0, Hazel: 0 Dick: 45, Hazel: null (*) Both queries will fail because the syntax of DBMS_OUTPUT.PUT_LINE is incorrect Correct 2.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.

Mark for Review (1) Points

What will happen when a user session executes the following commands in the order 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 The first 15 rows will be fetched. The first 5 rows will be fetched three times. An error will occur at line 2. The first 10 rows will be fetched, then the first 5 rows will be fetched again. An error will occur at line 4. (*) Correct 3.When a user session changes the value of a package variable, the new value can immediately be seen by other sessions. True or False?

Mark for Review (1) Points

True False (*) Correct 4.Users A and B call the same procedure in a package to initialize a global variable my_pkg.g_var. What will be the value of my_pkg.g_var for User A at Point A? User A: my_pkg.g_var User B: my_pkg.g_var User A: my_pkg.g_var User B: my_pkg.g_var Point A

Mark for Review (1) Points

is 10 is 10 is 50 is 25

50 (*) 25 10 Correct 5.In the following example, which statement best fits in Line 1? (Choose 1) DECLARE v_more_rows_exist BOOLEAN := TRUE; BEGIN -- Line 1 LOOP v_more_rows_exist := curs_pkg.fetch_n_rows(3); DBMS_OUTPUT.PUT_LINE('-------'); EXIT WHEN NOT v_more_rows_exist; END LOOP; curs_pkg.close_curs; END;

Mark for Review (1) Points

curs_pkg.close_curs; EXIT WHEN curs_pkg.emp_curs%NOTFOUND; curs_pkg.open_curs; (*) curs_pkg.emp_curs%ISOPEN; Correct Section 11 Quiz (Answer all questions in this section) 6. Which general exceptions may be handled by the UTL_FILE package? (Choose 2)

(Choose all correct answers) VALUE_ERROR (*) ZERO_DIVIDE TOO_MANY_ROWS

Mark for Review (1) Points

NO_DATA_FOUND (*) Correct 7. Which of the following procedures is not valid for the UTL_MAIL package

Mark for Review (1) Points

All are valid. SEND SEND_ATTACH_BOOLEAN (*) SEND_ATTACH_VARCHAR2 SEND_ATTACH_RAW Correct 8. 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?)

Mark for Review (1) Points

True (*) False Correct 9. Which of the following exceptions can be raised ONLY when using the UTL_FILE package? (Choose two.)

Mark for Review (1) Points

(Choose all correct answers) READ_ERROR (*) INVALID_PATH (*) VALUE_ERROR E_MYEXCEP NO_DATA_FOUND Correct 10.Using the FOPEN function, you can do which actions with the UTL_FILE package? (Choose 2) (Choose all correct answers) It is used to manipulate large object data type items in columns. It is used to append to a file until processing is complete. (*) It is used to find out how much free space is left on an operating system disk. It is used to read and write text files stored outside the database. (*) Correct Section 11 Quiz (Answer all questions in this section)

Mark for Review (1) Points

11.The DBMS_OUTPUT.PUT procedure places text in a buffer but does not display the contents of the buffer. True or False?

Mark for Review (1) Points

True (*) False Correct 12.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;

Mark for Review (1) Points

I do like to be beside the seaside I do like to be beside the seaside I do like to be I do like to be beside the seaside I do liketo be (*) Correct 13.Why is it better to use DBMS_OUTPUT only in anonymous blocks, not inside stored subprograms such as procedures?

Mark for Review (1) Points

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 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 Correct 14.The UTL_FILE package contains several exceptions exclusively used in this package. Which are they? (Choose 3) (Choose all correct answers) INVALID_PATH (*) NO_DATA_FOUND INVALID_OPERATION (*) WRITE_ERROR (*) ZERO_DIVIDE

Mark for Review (1) Points

Correct 15.The DBMS_OUTPUT package is useful for which of the following activities? (Choose two)

(Choose all correct answers) Write operating system text files to the user's screen Trace the code execution path for a function or procedure (*) Interact with a user during execution of a function or procedure Display results to the developer during testing for debugging purposes (*) Correct

Mark for Review (1) Points

Test: Section 12 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section) 1.In the following example, where do you place the phrase BULK COLLECT? DECLARE TYPE NameList IS TABLE OF emp.ename%TYPE; names NameList; CURSOR c1 IS SELECT ename -- Position A FROM emp WHERE job = 'CLERK'; BEGIN OPEN c1; FETCH c1 -- Position B INTO -- Position C names; ... CLOSE c1; END;

Mark for Review (1) Points

Position A Position B (*) Position C Correct 2.What are benefits of using the NOCOPY hint? (Choose two)

Mark for Review (1) Points

(Choose all correct answers) Uses a larger block of server memory for faster access Safer because it uses passing by value Efficient since it uses less memory (*) Faster because a single copy of the data is used (*) Correct 3.FORALL can only be used with the INSERT statement. True or False?

Mark for Review (1) Points

True False (*) Correct 4.In the following example, where do you place the phrase DETERMINISTIC? CREATE OR REPLACE FUNCTION total_sal (p_dept_id IN -- Position A employees.department_id%TYPE)

Mark for Review (1) Points

RETURN NUMBER -- Position B IS v_total_sal NUMBER; BEGIN SELECT SUM(salary) INTO v_total_sal FROM employees WHERE department_id = p_dept_in; RETURN v_total_sal -- Position C; END total_sal; Position A Position B (*) Position C Correct 5.The following example code will compile successfully. True or False? CREATE OR REPLACE PROCEDURE dept_proc IS TYPE t_dept IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER; BEGIN (p_small_arg IN NUMBER, p_big_arg OUT NOCOPY t_dept); -- remaining code END dept_proc;

Mark for Review (1) Points

True (*) False Correct Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 12 Quiz (Answer all questions in this section) 6. A function-based index may be made using your own functions, but only if the function is created using the DETERMINISTIC clause. True or False?

Mark for Review (1) Points

True (*) False Correct 7. The following example code will compile successfully. True or False? CREATE OR REPLACE PROCEDURE dept_proc IS TYPE t_dept IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER; BEGIN (p_small_arg IN NUMBER, p_big_arg OUT NOCOPY t_dept); -- remaining code END dept_proc; True (*) False Correct

Mark for Review (1) Points

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

Mark for Review (1) Points

... 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; ... True (*) False Correct 9. A SQL statement can pass through several stages. Which of the following is NOT one of these stages?

Mark for Review (1) Points

EXECUTE BIND RETURN (*) FETCH PARSE Correct 10.A public packaged procedure contains the following SQL statement: UPDATE employees SET salary = salary * 1.1; When is this SQL statement parsed?

Mark for Review (1) Points

When the package body is created (*) When the package specification is created Only the first time the procedure is executed When the package is loaded into memory When the package header is loaded into memory Correct Section 12 Quiz (Answer all questions in this section) 11.Name two reasons for using Dynamic SQL.

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

Mark for Review (1) Points

Correct 12.A programmer wants to code a procedure which will create a table with a single column. The datatype of the column will be chosen by the user who invokes the procedure. The programmer writes the following code:

Mark for Review (1) Points

CREATE OR REPLACE PROCEDURE create_tab (p_col_datatype IN VARCHAR2) IS BEGIN CREATE TABLE newtab (only_col p_col_datatype); END; Why will this procedure not compile successfully? Because you cannot create a table inside a procedure Because the invoking user may not have CREATE TABLE privilege Because when the procedure is compiled, Oracle cannot check if the parameter value passed into the procedure is a valid column datatype (*) Because table NEWTAB may already exist None of the above; the procedure will compile successfully. Correct 13.Name two reasons for using Dynamic SQL.

Mark for Review (1) Points

(Choose all correct answers) Creates a SQL statement with varying column data, or different conditions (*) Enables system control statements to be written and executed from PL/SQL Enables data-definition statements to be written and executed from PL/SQL (*) Avoids errrors at compile time of DML statements Correct 14.The following procedure adds a column of datatype DATE to the EMPLOYEES table. The name of the new column is passed to the procedure as a parameter. CREATE OR REPLACE PROCEDURE addcol (p_col_name IN VARCHAR2) IS v_first_string VARCHAR2(100) := 'ALTER TABLE EMPLOYEES ADD ('; v_second_string VARCHAR2(6) := ' DATE)'; BEGIN ... Line A END; Which of the following will work correctly when coded at line A? (Choose two.) (Choose all correct answers) EXECUTE IMMEDIATE 'v_first_string' || p_col_name || 'v_second_string'; EXECUTE v_first_string || p_col_name || v_second_string; v_first_string := v_first_string || p_col_name;

Mark for Review (1) Points

EXECUTE IMMEDIATE v_first_string || v_second_string; (*) EXECUTE IMMEDIATE v_first_string || p_col_name || v_second_string; (*) v_first_string || p_col_name || v_second_string; Correct 15.What happens when a SQL statement is parsed? (Choose three.)

(Choose all correct answers) The results of the statement are returned to the user. The statement is executed. Oracle queries the Data Dictionary to make sure that the tables referenced in the SQL statement exist. (*) The syntax of the statement is checked. (*) The user's required privileges are checked. (*) Correct

Mark for Review (1) Points