PL/SQL Section 9 Quiz

 1. Which of the following best describes the Data Dictionary?

Tandai untuk Ditinjau

(1) Point

It is an automatically managed master catalog of all the objects stored in the database. (*)

It contains a backup copy of all the data in the database.

It contains a list of all database tables which are not in any schema.

It is a set of tables which can be updated by any user who has the necessary privileges.

Incorrect. Refer to Section 9 Lesson 3.

6. When creating a user-defined function that will be called from a SQL statement, the size of the returned values may be up to the size of any PL/SQL data type. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 9 Lesson 2.

---

7. Which of the following is NOT a legal location for a function call in a SQL statement?

Tandai untuk Ditinjau

(1) Point

WHERE clause in a DELETE statement

VALUES clause of an INSERT statement

SET clause of an UPDATE statement

FROM clause of a SELECT statement (*)

Incorrect. Refer to Section 9 Lesson 2.

---

8. Which of the following is NOT a benefit of user-defined functions?

Tandai untuk Ditinjau

(1) Point

They can do the same job as built-in system functions such as UPPER and ROUND. (*)

They can be used in a WHERE clause to filter data.

They can often be used inside SQL statements.

They can add business rules to the database and can be reused many times.

Incorrect. Refer to Section 9 Lesson 2.

---

9. The database administrator has granted the DROP ANY PROCEDURE privilege to user KIM. This allows Kim to remove other users' procedures and functions from the database. How would Kim now drop function GET_EMP, which is owned by user MEHMET?

Tandai untuk Ditinjau

(1) Point

DROP FUNCTION mehmet.get_emp (*)

None of these

DROP PROGRAM mehmet.get_emp

DROP PROCEDURE mehmet.get_emp

DROP FUNCTION get_emp FROM mehmet

Incorrect. Refer to Section 9 Lesson 4.

---

10. You want to see the names, modes, and data types of the formal parameters of function MY_FUNC in your schema. How can you do this? (Choose two)

Tandai untuk Ditinjau

(1) Point

SHOW PARAMETER my_func;

Query USER_FUNCTIONS

DESCRIBE my_func;(*)

Query USER_SOURCE(*)

Query USER_PARAMETERS

Incorrect. Refer to Section 9 Lesson 4.

11. You try to create a function named MYFUNC. The function does not compile correctly because there are errors in your code. Which Dictionary view can you query to see the errors?

Tandai untuk Ditinjau

(1) Point

USER_SOURCE

USER_DEPENDENCIES

USER_COMPILES

USER_ERRORS (*)

USER_OBJECTS

Incorrect. Refer to Section 9 Lesson 1.

---

12. Consider the following function:

CREATE FUNCTION ADD_EM
    (a NUMBER := 1,
    b NUMBER := 2 )
    RETURN NUMBER
IS BEGIN
    RETURN (a+b);
END ADD_EM;

Which one of the following blocks will NOT work correctly?

Tandai untuk Ditinjau

(1) Point

DECLARE
    x NUMBER;
BEGIN
    x:= add_em(b=4);
END; (*)

DECLARE
    x NUMBER;
BEGIN
    x:= add_em(4);
END;

DECLARE
    x NUMBER;
BEGIN
    x:= add_em(4,5);
END;

DECLARE
    x NUMBER;
BEGIN
    x:= add_em;
END;

None of them will work.

Incorrect. Refer to Section 9 Lesson 1.

---

13. A stored function:

Tandai untuk Ditinjau

(1) Point

is called as a standalone executable statement.

must return one and only one value. (*)

must have at least one IN parameter.

cannot be called in a SQL statement.

Incorrect. Refer to Section 9 Lesson 1.

---

14. User REYHAN creates the following procedure:

CREATE PROCEDURE proc1
    AUTHID CURRENT_USER IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM tom.employees;
END;

User BILL wants to execute this procedure. What privileges will BILL need?

Tandai untuk Ditinjau

(1) Point

EXECUTE on REYHAN.PROC1

EXECUTE on REYHAN.PROC1 and SELECT on TOM.EMPLOYEES (*)

SELECT on TOM.EMPLOYEES

BILL needs no privileges

None of these. The procedure will fail to compile because REYHAN does not have SELECT privilege on TOM.EMPLOYEES.

Incorrect. Refer to Section 9 Lesson 6.

---

15. User SALLY's schema contains a NEWEMP table. Sally uses Invoker's rights to create procedure GET_NEWEMP which includes the line:

    SELECT ... FROM NEWEMP ... ;

Sally also grants EXECUTE privilege on the procedure to CURLY, but no other privileges. What will happen when Curly executes the procedure?

Tandai untuk Ditinjau

(1) Point

The procedure will fail because Curly does not have the EXECUTE ANY PROCEDURE system privilege.

The procedure will fail because Curly does not have SELECT privilege on NEWEMP.

The procedure will execute successfully.

The procedure will fail because there is no NEWEMP table in Curly's schema. (*)

Incorrect. Refer to Section 9 Lesson 6.

---

2. You want to see the names of all the columns in a table in your schema. You want to query the Dictionary instead of using the DESCRIBE command. Which Dictionary view should you query?

Tandai untuk Ditinjau

(1) Point

USER_OBJECTS

USER_COLUMNS

USER_TAB_COLS (*)

USER_TABLES

Incorrect. Refer to Section 9 Lesson 3.

---

3. You have forgotten the name of the Dictionary view USER_TABLES. Which of the following statements is the best and quickest way to remind yourself?

Tandai untuk Ditinjau

(1) Point

SELECT * FROM dictionary
WHERE table_name = 'USER%';

Phone the database administrator.

SELECT * FROM dictionary
WHERE table_name = 'USER_TABLES';

SELECT * FROM dict
WHERE table_name LIKE 'USER%TAB%'; (*)

Read the online Oracle documentation at http://technet.oracle.com.

Incorrect. Refer to Section 9 Lesson 3.

---

4. You want to allow user JOE to query the CD_DETAILS table in your schema. Which command should you use?

Tandai untuk Ditinjau

(1) Point

GRANT cd_details TO joe;

GRANT SELECT ON joe TO cd_details;

GRANT SELECT ON cd_details TO joe; (*)

GRANT SELECT TO joe ON cd_details;

GRANT QUERY ON cd_details TO joe;

Incorrect. Refer to Section 9 Lesson 5.

---

5. Your schema contains two procedures named CHILD1 and CHILD2. You now create a third procedure by executing:

CREATE OR REPLACE PROCEDURE parent IS
BEGIN
child1;
child2;
END;
You now want user JOE to be able to invoke PARENT. Which of the following gives JOE the privileges he needs, but no unnecessary privileges?

Tandai untuk Ditinjau

(1) Point

GRANT EXECUTE ON parent TO joe WITH ADMIN OPTION;

GRANT EXECUTE ON parent TO joe;
GRANT EXECUTE ON child1 TO joe;
GRANT EXECUTE ON child2 TO joe;

GRANT EXECUTE ON parent TO joe; (*)

GRANT EXECUTE ON * TO joe;

GRANT EXECUTE ON parent, child1, child2 TO joe;

Incorrect. Refer to Section 9 Lesson 5.