1. A package's state is initialized initialized when the package is first loaded. True or False? Review
Mark for
(1) Points
True (*)
False
[Correct]
Correct
2. 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 is 10 User B: my_pkg.g_var is 10 User A: my_pkg.g_var is 50 User B: my_pkg.g_var is 25 Point A Mark for Review (1) Points
10
50 (*)
25
[Correct]
Correct
3. 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? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
4.
DECLARE
In the following example, which statement best fits in Line 1? (Choose 1)
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.emp_curs%ISOPEN;
curs_pkg.close_curs;
curs_pkg.open_curs; (*)
EXIT WHEN curs_pkg.emp_curs%NOTFOUND;
[Incorrect]
Incorrect. Refer to Section 11 Lesson 1.
Section 1 (Answer all questions in this section)
(Choose two)
1. The DBMS_OUTPUT package is useful for which of the following activities? Mark for Review
(1) Points
(Choose all correct answers)
Interact with a user during execution of a function or procedure
Display results to the developer during testing for debugging purposes (*)
Trace the code execution path for a function or procedure (*)
Write operating system text files to the user's screen
[Incorrect]
Incorrect. Refer to Section 11 Lesson 2.
2. 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
[Incorrect]
Incorrect. Refer to Section 11 Lesson 1.
3. 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; Mark for Review (1) Points
DBMS_OUTPUT.PUT('IF branch was executed'); (*)
DBMS_OUTPUT.PUT_LINE('IF branch was executed');
DBMS_OUTPUT.GET_LINE('IF branch was executed');
DBMS_OUTPUT.NEW_LINE('IF branch was executed');
[Incorrect]
Incorrect. Refer to Section 11 Lesson 2.
4. The UTL_FILE package can be used to read and write binary files such as JPEGs as well as text files. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
(Choose 2)
Correct
5. Using the FOPEN function, you can do which actions with the UTL_FILE package? Mark for Review
(1) Points
(Choose all correct answers)
It is used to append to a file until processing is complete. (*)
It is used to read and write text files stored outside the database. (*)
It is used to find out how much free space is left on an operating system disk.
It is used to manipulate large object data type items in columns.
[Incorrect]
Incorrect. Refer to Section 11 Lesson 2.
6. The UTL_FILE package contains several exceptions exclusively used in this package. Which are they? (Choose 3) Mark for Review (1) Points
(Choose all correct answers)
INVALID_PATH (*)
NO_DATA_FOUND
WRITE_ERROR (*)
INVALID_OPERATION (*)
ZERO_DIVIDE
[Incorrect]
Incorrect. Refer to Section 11 Lesson 2.
7. Which general exceptions may be handled by the UTL_FILE package? (Choose 2) Mark for Review (1) Points
(Choose all correct answers)
TOO_MANY_ROWS
VALUE_ERROR (*)
ZERO_DIVIDE
NO_DATA_FOUND (*)
[Incorrect]
1.
Incorrect. Refer to Section 11 Lesson 2.
What happens when a SQL statement is parsed? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
The user's required privileges are checked. (*)
The syntax of the statement is checked. (*)
The statement is executed.
The results of the statement are returned to the user.
Oracle queries the Data Dictionary to make sure that the tables referenced in the SQL statement exist. (*)
[Incorrect]
Incorrect. Refer to Section 12 Lesson 1.
2. When SQL statements are included within a procedure, the statements are parsed when the procedure is compiled. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 12 Lesson 1.
3. 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:
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? Mark for Review (1) Points
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.
[Incorrect]
Incorrect. Refer to Section 12 Lesson 1.
4. For which of the following is it necessary to use Dynamic SQL? (Choose three.) Mark for Review (1) Points
(Choose all correct answers)
ALTER (*)
GRANT (*)
SAVEPOINT
UPDATE
DROP (*)
[Incorrect]
Incorrect. Refer to Section 12 Lesson 1.
5. an IN parameter:
Examine the following procedure, which drops a table whose name is passed as
CREATE OR REPLACE PROCEDURE drop_tab (p_table_name IN VARCHAR2) IS v_sql_statement VARCHAR2(100); BEGIN ... END;
Which of the following will work correctly when coded in the procedure's executable section? (Choose two.) Mark for Review (1) Points
(Choose all correct answers)
EXECUTE IMMEDIATE 'DROP TABLE p_table_name';
EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
(*)
v_sql_statement := 'DROP TABLE ' || p_table_name; EXECUTE IMMEDIATE v_sql_statement;
(*)
v_sql_statement := 'DROP TABLE ' || p_table_name; EXECUTE IMMEDIATE 'v_sql_statement';
v_sql_statement := 'DROP TABLE ';
EXECUTE IMMEDIATE v_sql_statement p_table_name;
[Incorrect]
Incorrect. Refer to Section 12 Lesson 1.
6.
What will happen when the following procedure is invoked?
CREATE OR REPLACE PROCEDURE do_some_work IS CURSOR c_curs IS SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION'; BEGIN FOR v_curs_rec IN c_curs LOOP EXECUTE IMMEDIATE 'ALTER FUNCTION ' || v_curs_rec.object_name || ' COMPILE'; EXIT WHEN c_curs%ROWCOUNT > 2; END LOOP; END; Mark for Review (1) Points
All functions in the user's schema will be recompiled.
The first two functions in the user's schema will be recompiled.
The first three functions in the user's schema will be recompiled. (*)
The procedure will not compile successfully because you cannot ALTER functions using Dynamic SQL.
The procedure will not compile successfully because the syntax of the ALTER FUNCTION statement is incorrect.
[Incorrect]
Incorrect. Refer to Section 12 Lesson 1.
7. The DBMS_SQL package is easier to use than EXECUTE IMMEDIATE. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
8. Only one call to DBMS_SQL is needed in order to drop a table. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
9.
Name two reasons for using Dynamic SQL.
Mark for Review
(1) Points
(Choose all correct answers)
Avoids errrors at compile time of DML statements
Creates a SQL statement with varying column data, or different conditions (*)
Enables data-definition statements to be written and executed from PL/SQL (*)
Enables system control statements to be written and executed from PL/SQL
[Incorrect]
1.
Incorrect. Refer to Section 12 Lesson 1.
What are benefits of using the 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 (*)
[Correct]
Correct
Mark for Review
2. 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
[Incorrect]
Incorrect. Refer to Section 12 Lesson 2.
3.
The following procedure compiles successfully. True or False?
CREATE OR REPLACE PACKAGE emp_pkg IS TYPE t_emp IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; PROCEDURE emp_proc (p_small_arg IN NUMBER, p_big_arg NOCOPY OUT t_emp); ... END emp_pkg; Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 12 Lesson 2.
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) 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; Mark for Review (1) Points
Position A
Position B (*)
Position C
[Incorrect]
Incorrect. Refer to Section 12 Lesson 2.
5.
What is wrong with this code example?
CREATE OR REPLACE PROCEDURE insert_emps IS TYPE t_emp IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; v_emptab t_emp; BEGIN FORALL i IN v_emptab.FIRST..v_emptab.LAST INSERT INTO employees VALUES v_emptab(i); END LOOP; END insert_emps; Mark for Review (1) Points
The phrase should be FOR ALL.
v_emptab is incorrectly typed.
FORALL does not require END LOOP. (*)
Nothing is wrong; it will compile successfully.
[Incorrect]
Incorrect. Refer to Section 12 Lesson 2.
6.
FORALL can only be used with the INSERT statement. True or False?
Mark
for Review (1) Points
True
False (*)
[Correct]
Correct
7.
...
In the following example, where do you place the phrase BULK COLLECT?
BEGIN SELECT -- Position A salary -- Position B INTO v_saltab -- Position C FROM employees WHERE department_id = 20 ORDER BY salary -- Position D ; ... Mark for Review (1) Points
Position A
Position B (*)
Position C
Position D
[Incorrect]
Incorrect. Refer to Section 12 Lesson 2.
8.
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
[Incorrect]
Incorrect. Refer to Section 12 Lesson 2.
9.
What is the main purpose for using the RETURNING clause?
Mark for
Review (1) Points
Improve performance by returning a single value
Improve performance by minimizing the number of statements
Improve performance by making one call to the SQL engine (*)
Return more readily any exceptions that are raised by the statement
[Incorrect]
Incorrect. Refer to Section 12 Lesson 2.
10.
The following statement is a valid example of using the RETURNING clause. True
or False?
DECLARE TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE); emp_info EmpRec;
emp_id NUMBER := 100; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id RETURNING last_name, salary INTO emp_info; dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary); END; Mark for Review (1) Points
True (*)
False
[Correct]
Correct
Section 1 (Answer all questions in this section)
1.
Which of the following could NOT be done by a database trigger?
for Review (1) Points
Enforcing a complex business rule
Mark
Enforcing a complex database security check
Recalculating the total salary bill for a department whenever an employee's salary is changed
Ensuring that a student never arrives late for a class (*)
Keeping a log of how many rows have been inserted into a table
[Incorrect]
Incorrect. Refer to Section 13 Lesson 1.
2. You can use a database trigger to prevent invalid transactions from being committed. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 13 Lesson 1.
3.
Which of the following best describes a database trigger?
Mark for
Review (1) Points
It allows users to log on to the database.
It executes automatically whenever a particular event occurs within the database. (*)
It prevents unique constraints from being violated.
It executes automatically whenever a user clicks on a button with his mouse.
It allows foreign key constraints to be violated.
[Correct]
Correct
4. A database trigger is a PL/SQL stored subprogram which is explicitly invoked just like a procedure or a function. True or False? Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 1.
5. Which of the following events could NOT automatically fire a database trigger? Mark for Review (1) Points
A user logging on to the database
A SQL INSERT statement
You click your mouse on a button to choose the correct answer to this question (*)
A DML operation on a view
The Database Administrator shuts down the database
[Incorrect]
Incorrect. Refer to Section 13 Lesson 1.
6. While editing a document in Microsoft Word, you go to the FILE menu and SAVE your work. To do this, Microsoft Word has executed an application trigger. True or False? Mark for Review (1) Points
True (*)
False
[Correct]
Correct
7. A business rule states that an employee's salary must be between 4000 and 30000. We could enforce this rule using a check constraint, but it is better to use a database trigger. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
(Choose two)
Correct
8. Which of the following are good guidelines to follow when creating triggers? Mark for Review
(1) Points
(Choose all correct answers)
Be aware of recursive and cascading effects (*)
Where possible, use triggers to enforce NOT NULL constraints
Avoid lengthy trigger logic by creating a procedure and invoking it from within the trigger (*)
Use triggers to replace functionality which is already built into the database
Always create more triggers than you need, because it is better to be safe
[Incorrect]
Incorrect. Refer to Section 13 Lesson 1.
9. A user's schema contains procedure MYPROC, function MYFUNC, trigger MYTRIGG and package MYPACK which contains a public procedure PACKPROC. These subprograms have no parameters, and the function returns a NUMBER. Which of the following calls to these objects (from an anonymous block) are incorrect? (Choose two) Mark for Review (1) Points
(Choose all correct answers)
mypack.packproc;
mytrigg; (*)
myproc;
v_number := myfunc;
IF NOT myfunc THEN ... (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 1.
10. Which of the following are NOT allowed within a database trigger? (Choose two) Mark for Review (1) Points
(Choose all correct answers)
COMMIT (*)
A call to a packaged procedure
INSERT
A Boolean variable
SAVEPOINT (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 1.
1. Which of the following is the correct syntax for creating a DML trigger associated with the EMPLOYEES table? The trigger must fire whenever an employee's JOB_ID is updated, but not if a different column is updated. Mark for Review (1) Points
CREATE TRIGGER job_upd_trigg AFTER UPDATE ON employees(job_id) BEGIN ...
CREATE TRIGGER job_upd_trigg WHENEVER UPDATE OF job_id IN employees BEGIN ...
CREATE TRIGGER job_upd_trigg AFTER UPDATE ON employees.job_id BEGIN ...
CREATE TRIGGER job_upd_trigg AFTER UPDATE OF job_id ON employees BEGIN ...
(*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 2.
2.
What is wrong with the following code?
CREATE OR REPLACE TRIGGER mytrigg AFTER DELETE ON departments BEGIN INSERT INTO audit_table (who, when) VALUES (USER, SYSDATE); COMMIT; END; Mark for Review (1) Points
A DML trigger cannot itself contain a DML statement such as INSERT INTO audit_table.
You cannot use COMMIT inside a trigger. (*)
The last line of code should be END mytrigg;
The second line should be: AFTER DELETE OF DEPARTMENTS
Nothing is wrong, the trigger will execute successfully.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 2.
3. Which of the following are possible keywords for the timing component of a trigger? (Choose three.) Mark for Review (1) Points
(Choose all correct answers)
BEFORE (*)
INSTEAD
WHENEVER
INSTEAD OF (*)
AFTER (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 2.
4. We want to create a log record automatically every time any DML operation is executed on either or both of the EMPLOYEES and DEPARTMENTS tables. What is the smallest number of triggers that must be create to do this? Mark for Review (1) Points
One
Two (*)
Three
Six
Eight
[Incorrect]
Incorrect. Refer to Section 13 Lesson 2.
5. We want to prevent employees from being deleted on Sundays. To do this, we create the following trigger:
CREATE OR REPLACE TRIGGER stop_del_emps ....... DELETE ON employees -- Line A BEGIN IF TO_CHAR(SYSDATE','DY') = 'SUN' THEN RAISE_APPLICATION_ERROR(-20101,'Invalid delete'); END IF; END;
Should this be a BEFORE or AFTER trigger, and why? Mark for Review (1) Points
It should be a BEFORE trigger because if an AFTER trigger were created, the employee would already have been deleted by the time the trigger checks the date. (*)
It should be a BEFORE trigger because you cannot use RAISE_APPLICATION_ERROR with AFTER triggers.
It should be an AFTER trigger because the Oracle Server cannot fire the trigger until it knows that the employee has been deleted.
It does not matter, either a BEFORE or an AFTER trigger could be created.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 2.
6. An AFTER UPDATE trigger can specify more than one column. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 13 Lesson 2.
7. A BEFORE statement trigger inserts a row into a logging table every time a user updates the salary column of the employees table. The user now tries to update the salaries of three employees with a single UPDATE statement, but the update fails because it violates a check constraint. How many rows will be inserted into the logging table? Mark for Review (1) Points
None, the transactions are rolled back because the update failed. (*)
One
Three
Four
None of the above
[Incorrect]
Incorrect. Refer to Section 13 Lesson 2.
8.
There are five employees in department 50. A statement trigger is created by:
CREATE OR REPLACE TRIGGER emp_upd_trigg AFTER DELETE ON EMPLOYEES BEGIN ...
A user now executes: DELETE FROM employees WHERE department_id = 50;
How many times will the trigger fire, and when? Mark for Review (1) Points
Once, before the DELETE is executed
Five times, after each employee row is deleted
Once, after the DELETE is executed (*)
Six times, once after each row and once at the end of the statement
The trigger will not fire at all.
[Incorrect] 1.
Incorrect. Refer to Section 13 Lesson 2.
You decide to create the following trigger:
CREATE OR REPLACE TRIGGER empl_trigg BEFORE UPDATE ON employees BEGIN -- Line A
RAISE_APPLICATION_ERROR('Cannot update salary'); ELSE INSERT INTO log_table values (USER, SYSDATE); END IF; END;
You want the trigger to prevent updates to the SALARY column, but allow updates to all other columns. What should you code at Line A? Mark for Review (1) Points
IF UPDATING SALARY THEN
IF UPDATING('SALARY') THEN (*)
IF UPDATE('SALARY') THEN
IF UPDATING(SALARY) THEN
IF UPDATE(SALARY) THEN
[Correct]
Correct
2. Which of the following best describes conditional predicates in a trigger? Mark for Review (1) Points
They are special variables which must be DECLAREd within the trigger.
They allow the trigger code to see what data values are being inserted into a row.
They are automatically declared boolean variables which allow the trigger body to detect which DML operation is being executed. (*)
They are special cursor attributes, like %ROWCOUNT and %NOTFOUND
[Incorrect]
Incorrect. Refer to Section 13 Lesson 3.
3. included at Line A?
Examine the following code. To create a row trigger, what code should be
CREATE OR REPLACE TRIGGER del_emp_trigg BEFORE DELETE ON employees ---- Line A
BEGIN ... Mark for Review (1) Points
FOR EVERY ROW
FOR EACH ROW (*)
FOR EVERY ROW
FOR ALL ROWS
Nothing is needed because DML triggers are row triggers by default.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 3.
4. A row trigger has been created which is fired by UPDATE ON employees. A user now executes a single SQL statement which updates four rows of the EMPLOYEES table. How many times will the row trigger fire? Mark for Review (1) Points
One time
Two times
Four times (*)
Five times
Eight times
[Correct]
Correct
5. Whenever an employee's JOB_ID is updated, we want to insert a row into a logging table to record the employee_id and the new value of JOB_ID. We create a row trigger whose body includes the following code:
BEGIN INSERT INTO logging_table (emp_id, job_id) VALUES -- Point A END;
At point A, which of the following will insert the correct data into the logging table? (Choose two.) Mark for Review (1) Points
(Choose all correct answers)
(:OLD.employee_id, :OLD.job_id);
(:OLD.employee_id, :NEW.job_id); (*)
(:NEW.employee_id, :OLD.job_id);
(:NEW.employee_id, :NEW.job_id); (*)
(NEW.employee_id, NEW.job_id);
[Incorrect]
Incorrect. Refer to Section 13 Lesson 3.
6. The OLD and NEW qualifiers can be used with statement triggers as well as row triggers. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
7. Which of the following statements about INSTEAD OF triggers are NOT true? (Choose two.) Mark for Review (1) Points
(Choose all correct answers)
They can be created on a table. (*)
They can be created on a simple view.
They can be created on a complex view.
They can be statement triggers. (*)
They can be row triggers.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 3.
8.
The following view and trigger have been created:
CREATE VIEW dept_view AS SELECT * FROM departments; CREATE OR REPLACE TRIGGER dept_view_trigg INSTEAD OF UPDATE ON dept_view BEGIN DBMS_OUTPUT.PUT_LINE('Sample Message'); END;
Departments 50 and 80 exist but department 81 does not. A user now executes the following statement:
UPDATE dept_view SET department_name = 'Sales' WHERE department_id IN (50,80,81);
What happens? Mark for Review (1) Points
Two rows are updated and "Sample Message" is displayed once.
No rows are updated and "Sample Message" is displayed once.
No rows are updated and "Sample Message" is displayed twice. (*)
No rows are updated and "Sample Message" is displayed three times.
None of the above
[Correct]
Correct
9.
What are the timing events for a compound trigger?
Mark for Review
(1) Points
Before the triggering statement; After the triggering statement; Instead of the triggering statement
Before the triggering statement; Before each row; After each row; After the triggering statement (*)
Before the triggering statement; After the triggering statement; After each row
Before the triggering statement; Before each row; After the triggering statement
[Incorrect]
Incorrect. Refer to Section 13 Lesson 3.
10.
What is wrong with this compound trigger example?
CREATE OR REPLACE TRIGGER compound_trigger FOR UPDATE OF salary COMPOUND TRIGGER threshold CONSTANT SIMPLE_INTEGER := 200;
BEFORE EACH ROW IS BEGIN -- some action END BEFORE EACH ROW;
AFTER EACH ROW IS BEGIN -- some action END AFTER EACH ROW;
AFTER STATEMENT IS BEGIN -- some action END AFTER STATEMENT; END compound_trigger; Mark for Review (1) Points
Missing BEFORE timing statement
Missing the EXCEPTION section
Missing name of table on which the trigger fires (*)
Missing the INSTEAD OF timing section
Missing the BEFORE and INSTEAD OF timing sections
[Incorrect]
Incorrect. Refer to Section 13 Lesson 3.
1. Which of the following could NOT cause a DDL or Database Event trigger to fire? for Review (1) Points
Mark
A table is dropped.
A user connects to the database.
The DBA starts up the database.
A user deletes rows from the EMPLOYEES table. (*)
A specific exception is raised in a user's session.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
2. The database administrator creates a trigger that automatically disconnects user HACKER whenever HACKER connects to the database. What type of trigger is this? Mark for Review (1) Points
A DDL trigger
A Database Event trigger (*)
A DML trigger
A statement trigger
An INSTEAD OF trigger
[Correct]
Correct
3. User HARJIT wants to prevent any objects which he owns from being dropped. Harjit decides to execute the following code:
CREATE OR REPLACE TRIGGER stop_drop ---- Line A BEGIN RAISE_APPLICATION_ERROR(-20201,'Attempted drop'); END; What should Harjit code at Line A? Mark for Review (1) Points
BEFORE DROP ON HARJIT
BEFORE DROP ON TABLE
BEFORE DROP ON SCHEMA (*)
BEFORE DROP ON OWNER
BEFORE DROP ON USER_OBJECTS
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
4. You can create a trigger which prevents DDL statements on an individual table, while still allowing DDL on other tables in the same schema. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
5. The database administrator wants to write a log record every time any user's session raises an ORA-00942 exception. The DBA decides to create the following trigger:
CREATE OR REPLACE TRIGGER log_942_trigg AFTER SERVERERROR ON DATABASE BEGIN -- Line A INSERT INTO log_table VALUES ( ...); END; What should the DBA code at Line A? Mark for Review (1) Points
IF (SERVERERROR(942)) THEN
IF (IS_SERVERERROR(942)) THEN (*)
IF (SERVERERROR = 942) THEN
IF (IS_SERVERERROR = 942) THEN
IF (IS_SERVERERROR(ORA-00942)) THEN
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
6.
What is the benefit of using the CALL statement in a trigger body?
Mark
for Review (1) Points
It allow both DDL events and database events to be handled by a single trigger.
It prevents data being read from a mutating table.
It allows the database administrator to monitor who is currently connected to the database.
It allows the trigger body code to be placed in a separate procedure. (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
7.
What is wrong with the following code?
CREATE OR REPLACE TRIGGER call_trigg AFTER UPDATE OR DELETE ON employees BEGIN CALL del_emp_proc END; Mark for Review (1) Points
When CALL is used, the BEGIN and END; statements should be omitted. (*)
The CALL statement should end with a semicolon (;)
You cannot use a CALL statement in a DML trigger.
When using CALL, only one DML statement can be tested, so UPDATE OR DELETE is wrong.
[Correct]
Correct
8.
What is wrong with the following code?
CREATE OR REPLACE TRIGGER emp_dml_trigg BEFORE UPDATE OF salary ON employees FOR EACH ROW DECLARE v_max_sal employees.salary%TYPE; BEGIN SELECT max(sal) INTO v_max_sal FROM employees; END; Mark for Review (1) Points
You cannot use a DECLARE statement in a trigger.
The trigger body is reading the same table (employees) that the triggering event is updating. (*)
You must use RAISE_APPLICATION_ERROR in a BEFORE trigger.
You can never use SELECT inside a DML trigger.
Nothing is wrong, the trigger will execute correctly.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
9. Mutating table errors can be caused by DML triggers, but not by database event triggers. True or False? Mark for Review (1) Points
True (*)
False
[Correct]
Correct
10. You have been granted CREATE TRIGGER privilege. You can now create an AFTER LOGOFF ON SCHEMA trigger. True or False? Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
1. Which dictionary view would you query to see the detailed body code of triggers in your schema? Mark for Review (1) Points
USER_SOURCE
USER_TRIGGER
USER_TRIGGERS (*)
USER_OBJECTS
None of the above; you cannot view the code of the trigger body after the trigger has been created.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 5.
or False?
2. By default, any user can create a DML trigger on a table in his/her schema. True Mark for Review
(1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 5.
3. You have created several DML triggers which reference your DEPARTMENTS table. Now you want to disable all of them using a single SQL statement. Which command should you use? Mark for Review (1) Points
ALTER TRIGGER DISABLE ALL ON departments;
ALTER TABLE departments DISABLE ALL TRIGGERS; (*)
ALTER TABLE departments DISABLE TRIGGERS;
DROP ALL TRIGGERS ON departments;
[Incorrect]
Incorrect. Refer to Section 13 Lesson 5.
4. Which command would you use to see if your triggers are enabled or disabled? Mark for Review (1) Points
SELECT trigger_name, status FROM USER_TRIGGERS;
(*)
SELECT object_name, status FROM USER_OBJECTS WHERE object_type = 'TRIGGER';
SELECT trigger_name, trigger_type FROM USER_TRIGGERS;
DESCRIBE TRIGGER
[Correct]
Correct
5. User KULJIT creates two triggers named EMP1_TRIGG and EMP2_TRIGG, which are both DML triggers referencing her EMPLOYEES table. Kuljit now wants to remove both of these triggers from the database. What command(s) should Kuljit use to do this? Mark for Review (1) Points
DROP ALL TRIGGERS ON employees;
DROP TRIGGERS ON employees;
DROP TRIGGER emp1_trigg; DROP TRIGGER emp2_trigg;
(*)
DROP TRIGGER emp1_trigg AND emp2_trigg;
[Correct]
Correct
6.
A user creates the following trigger:
CREATE OR REPLACE TRIGGER emp_trigg AFTER DELETE ON employees BEGIN ... END;
The user now tries to drop the EMPLOYEES table. What happens? Mark for Review (1) Points
The table is dropped but the trigger is not dropped.
An error message is displayed because you cannot drop a table that is referenced by a trigger.
The table is dropped and the trigger is disabled.
Both the table and the trigger are dropped. (*)
1. Which of the following could NOT cause a DDL or Database Event trigger to fire? for Review (1) Points
A table is dropped.
A user connects to the database.
The DBA starts up the database.
A user deletes rows from the EMPLOYEES table. (*)
A specific exception is raised in a user's session.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
Mark
2. The database administrator creates a trigger that automatically disconnects user HACKER whenever HACKER connects to the database. What type of trigger is this? Mark for Review (1) Points
A DDL trigger
A Database Event trigger (*)
A DML trigger
A statement trigger
An INSTEAD OF trigger
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
3. User HARJIT wants to prevent any objects which he owns from being dropped. Harjit decides to execute the following code:
CREATE OR REPLACE TRIGGER stop_drop ---- Line A
BEGIN RAISE_APPLICATION_ERROR(-20201,'Attempted drop'); END; What should Harjit code at Line A? Mark for Review (1) Points
BEFORE DROP ON HARJIT
BEFORE DROP ON TABLE
BEFORE DROP ON SCHEMA (*)
BEFORE DROP ON OWNER
BEFORE DROP ON USER_OBJECTS
[Correct]
Correct
4. You can create a trigger which prevents DDL statements on an individual table, while still allowing DDL on other tables in the same schema. True or False? Mark for Review
(1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
5. The database administrator wants to write a log record every time any user's session raises an ORA-00942 exception. The DBA decides to create the following trigger:
CREATE OR REPLACE TRIGGER log_942_trigg AFTER SERVERERROR ON DATABASE BEGIN -- Line A INSERT INTO log_table VALUES ( ...); END; What should the DBA code at Line A? Mark for Review (1) Points
IF (SERVERERROR(942)) THEN
IF (IS_SERVERERROR(942)) THEN (*)
IF (SERVERERROR = 942) THEN
IF (IS_SERVERERROR = 942) THEN
IF (IS_SERVERERROR(ORA-00942)) THEN
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
6.
What is the benefit of using the CALL statement in a trigger body?
Mark
for Review (1) Points
It allow both DDL events and database events to be handled by a single trigger.
It prevents data being read from a mutating table.
It allows the database administrator to monitor who is currently connected to the database.
It allows the trigger body code to be placed in a separate procedure. (*)
[Correct]
Correct
7.
What is wrong with the following code?
CREATE OR REPLACE TRIGGER call_trigg AFTER UPDATE OR DELETE ON employees BEGIN CALL del_emp_proc END; Mark for Review (1) Points
When CALL is used, the BEGIN and END; statements should be omitted. (*)
The CALL statement should end with a semicolon (;)
You cannot use a CALL statement in a DML trigger.
When using CALL, only one DML statement can be tested, so UPDATE OR DELETE is wrong.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
8.
What is wrong with the following code?
CREATE OR REPLACE TRIGGER emp_dml_trigg BEFORE UPDATE OF salary ON employees FOR EACH ROW DECLARE v_max_sal employees.salary%TYPE; BEGIN SELECT max(sal) INTO v_max_sal FROM employees; END; Mark for Review (1) Points
You cannot use a DECLARE statement in a trigger.
The trigger body is reading the same table (employees) that the triggering event is updating. (*)
You must use RAISE_APPLICATION_ERROR in a BEFORE trigger.
You can never use SELECT inside a DML trigger.
Nothing is wrong, the trigger will execute correctly.
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
9. Mutating table errors can be caused by DML triggers, but not by database event triggers. True or False? Mark for Review (1) Points
True (*)
False
[Correct]
Correct
10. You have been granted CREATE TRIGGER privilege. You can now create an AFTER LOGOFF ON SCHEMA trigger. True or False? Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 13 Lesson 4.
1. PL/SQL procedure A invokes procedure B, which in turn invokes procedure C, which references table T. If table T is dropped, which of the following statements is true? Mark for Review (1) Points
C is invalid but A and B are still valid
A, B and C are all invalid (*)
B and C are invalid but A is still valid
A, B and C are all still valid
None of the above
[Correct]
Correct
2.
A procedure show_emps contains the following declaration:
CURSOR emp_curs IS SELECT last_name, salary FROM employees;
What will happen to the procedure if a new column is added to the employees table? Mark for Review (1) Points
The procedure will still be valid and execute correctly because it does not reference the added column.
The procedure will automatically be dropped and must be recreated.
The procedure will be marked invalid and must be recompiled before it can be reexecuted. (*)
Users' privileges to execute the procedure will automatically be revoked.
[Correct]
Correct
3. View dept_view is based on a select from table departments. Procedure show_dept contains code which selects from dept_view. Which of the following statements are true? (Choose three.) Mark for Review (1) Points
(Choose all correct answers)
departments is indirectly dependent on show_dept
show_dept is directly dependent on dept_view (*)
dept_view is directly dependent on departments (*)
show_dept is indirectly dependent on departments (*)
emp_view is directly dependent on show_dept
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
4. A single PL/SQL subprogram such as a procedure can be both a referenced object and a dependent object. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
5. Which data dictionary view shows information about references and dependencies? Mark for Review (1) Points
DEPTREE
USER_DEPENDENCIES (*)
USER_REFERENCES
USER_LOCAL_DEPENDENCIES
[Correct]
or invalid?
Correct
6. Which of the following statements will show whether procedure myproc is valid Mark for Review
(1) Points
SELECT status FROM USER_OBJECTS WHERE object_type = 'PROCEDURE' AND object_name = 'MYPROC';
(*)
SELECT status FROM USER_PROCEDURES WHERE procedure_name = 'MYPROC';
SELECT valid FROM USER_OBJECTS WHERE object_type = 'PROCEDURE' AND object_name = 'MYPROC';
SELECT * FROM deptree;
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
7. Which of the following database objects are created when the utldtree.sql script is run? (Choose three.) Mark for Review (1) Points
(Choose all correct answers)
The utldtree table
The deptree_temptab table (*)
The deptree and ideptree views (*)
The deptree table
The deptree_fill procedure (*)
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
8. User ALICE owns a procedure show_emps which references table employees. Which of the following will generate information that shows this dependency? Mark for Review (1) Points
BEGIN deptree_fill('TABLE','EMPLOYEES'); END;
BEGIN deptree_fill('PROCEDURE','ALICE','SHOW_EMPS'); END;
BEGIN deptree_fill('ALICE','TABLE','EMPLOYEES'); END;
BEGIN deptree_fill('TABLE','ALICE','EMPLOYEES'); END;
(*)
BEGIN deptree_fill('ALICE','PROCEDURE','SHOW_EMPS'); END;
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
9.
>NESTED_LEVEL
A SELECT from DEPTREE produced the following output.
>TYPE >NAME
>0
>TABLE >EMPLOYEES
>1
>VIEW >EMP_VW
>2
>PROCEDURE
>ADD_EMP
>1
>PROCEDURE
>QUERY_EMP
What dependencies does this show? (Choose three.) Mark for Review (1) Points
(Choose all correct answers)
QUERY_EMP is directly dependent on EMPLOYEES (*)
ADD_EMP is directly dependent on EMPLOYEES
ADD_EMP is directly dependent on EMP_VW (*)
QUERY_EMP is directly dependent on ADD_EMP
EMP_VW is directly dependent on EMPLOYEES (*)
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
10. The IDEPTREE view shows dependencies by indenting the lines of output instead of by using a NESTED_LEVEL column. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
11. Procedure get_depts has been marked invalid because one of the objects it references has been altered. Which of the following statements are true? (Choose two.) Mark for Review (1) Points
(Choose all correct answers)
The procedure will be recompiled automatically the next time it is invoked. The recompilation will always be successful.
The procedure will be recompiled automatically the next time it is invoked. The recompilation may or may not be successful.
(*)
The procedure can be recompiled manually by: ALTER PROCEDURE get_depts COMPILE;
(*)
The procedure can be recompiled manually by: ALTER PROCEDURE get_depts RECOMPILE;
The procedure does not need to be recompiled.
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
12.
A procedure includes the following code:
SELECT first_name, salary INTO v_first_name, v_salary FROM employees WHERE employee_id = 100;
Which of the following changes to the employees table will allow the procedure to be recompiled successfully ? (Choose two.) Mark for Review (1) Points
(Choose all correct answers)
The table is dropped but a public table exists with the same name and structure. (*)
The table is dropped.
A new column is added to the table. (*)
The table name is changed to newemps.
The first_name column is dropped from the table.
[Incorrect]
two.)
Incorrect. Refer to Section 14 Lesson 1.
13. Which of the following will NOT help to minimize dependency failures? (Choose Mark for Review
(1) Points
(Choose all correct answers)
SELECTing a list of column names instead of using SELECT * (*)
Declaring records using the %ROWTYPE attribute
Including a column list with INSERT statements
Declaring scalar variables with NOT NULL if the corresponding table column has a NOT NULL constraint (*)
Declaring scalar variables using the %TYPE attribute
[Incorrect]
Incorrect. Refer to Section 14 Lesson 1.
14. Package emp_pack contains two public procedures: get_emps and upd_emps. A separate procedure emp_proc invokes emp.pack.get_emps. The upd_emps package body code is now altered, and the package body (but not the package specification) is recreated.
emp_proc will be marked invalid and needs to be recompiled. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
1. A remote dependency is when a dependent object resides on a database on a separate node. True or False? Mark for Review (1) Points
True (*)
False
[Correct]
Correct
2. With remote dependencies, one master data dictionary that resides on one server identifies the status of all schema objects. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
3. The Data Dictionary controls the remote dependency mode. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
4. Which statement for setting a database parameter is the default for remote dependency checking? Mark for Review (1) Points
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = TIMESTAMP (*)
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE
ALTER SESSION REMOTE_DEPENDENCIES_MODE = TIMESTAMP
ALTER SESSION REMOTE_DEPENDENCIES_MODE = SIGNATURE
[Correct]
Correct
5.
In this scenario, the following status is given for each procedure:
- Procedure A is local and has a time stamp of 10 AM - Procedure B is remote and has a local time stamp of 5 AM and has a remote time stamp of 4 AM
In Timestamp Mode, Procedure A will execute successfully at 11 AM. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
6. In Signature Mode, a procedure will not compile if the signatures of the remote dependencies do not match. True or False? Mark for Review (1) Points
True (*)
False
[Correct]
Correct
7.
In this scenario, the following status is given for each procedure:
- Procedure A is local, executed, and invalidated because the remote Procedure B time stamp does not match the local time stamp for Procedure B - Procedure A is recompiled.
In Timestamp Mode, now Procedure A will execute successfully. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 14 Lesson 2.
8. In Signature Mode, a compiled procedure is still valid if its dependent procedure has a parameter data type change from NUMBER to VARCHAR2. Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 14 Lesson 2.
9. A remote dependency is when a dependent object resides on a database on a separate node. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 14 Lesson 2.
10. With remote dependencies, one master data dictionary that resides on one server identifies the status of all schema objects. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
11. The Data Dictionary controls the remote dependency mode. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
12. Which statement for setting a database parameter is the default for remote dependency checking? Mark for Review (1) Points
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = TIMESTAMP (*)
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE
ALTER SESSION REMOTE_DEPENDENCIES_MODE = TIMESTAMP
ALTER SESSION REMOTE_DEPENDENCIES_MODE = SIGNATURE
[Incorrect]
Incorrect. Refer to Section 14 Lesson 2.
13.
In this scenario, the following status is given for each procedure:
- Procedure A is local and has a time stamp of 10 AM - Procedure B is remote and has a local time stamp of 5 AM and has a remote time stamp of 4 AM
In Timestamp Mode, Procedure A will execute successfully at 11 AM. True or False? Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 14 Lesson 2.
14. In Signature Mode, a procedure will not compile if the signatures of the remote dependencies do not match. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 14 Lesson 2.
15.
In this scenario, the following status is given for each procedure:
- Procedure A is local, executed, and invalidated because the remote Procedure B time stamp does not match the local time stamp for Procedure B - Procedure A is recompiled.
In Timestamp Mode, now Procedure A will execute successfully. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 14 Lesson 2.
16. In Signature Mode, a compiled procedure is still valid if its dependent procedure has a parameter data type change from NUMBER to VARCHAR2. Mark for Review (1) Points
True
False (*)
[Incorrect]
1.
Incorrect. Refer to Section 13 Lesson 5.
To set the PLSQL_CODE_TYPE to its fastest execution speed, which command do you use? Mark for Review
(1) Points
ALTER SYSTEM SET PLSQL_CODE_TYPE=NATIVE;
ALTER SYSTEM SET PLSQL_CODE_TYPE=2;
(*)
ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;
ALTER SESSION SET PLSQL_CODE_TYPE = 2;
[Incorrect]
Incorrect. Refer to Section 15 Lesson 1.
2. PLSQL_CODE_TYPE determines the type of code for both PL/SQL code and for SQL statements, which is what speeds up the execution speed. True or False? Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 15 Lesson 1.
two)
3. Which are NOT examples of benefits of using PLSQL_OPTIMIZE_LEVEL. (Choose Mark for Review
(1) Points
(Choose all correct answers)
Control what PL/SQL does with useless code
Combining compiled code from one subprogram into another subprogram
Separating compiled code so that separate units may be repeated as needed (*)
Backward compatible with previous versions of the Oracle database
Modify source code to optimize frequently-used elements at the top (*)
[Incorrect]
Incorrect. Refer to Section 15 Lesson 1.
4. When setting PLSQL_OPTIMIZE_LEVEL = 2, the compiled code will remove code and exceptions that can never be executed. True or False? Mark for Review (1) Points
True (*)
False
[Correct]
Correct
5. Which data dictionary view allows you to see the setting for PLSQL_OPTIMIZE_LEVEL? Mark for Review (1) Points
USER_PLSQL_OBJECTS
USER_PLSQL_OPTIMIZE
USER_PLSQL_OBJECT_SETTINGS (*)
USER_OBJECT_SETTINGS
USER_PLSQL_CODE_TYPE
[Correct]
Correct
6. What are the valid values for PLSQL_OPTIMIZE_LEVEL in the data dictionary? Mark for Review (1) Points
0,1,2,3 (*)
0,1,2,3,4
1,2,3
1,2,3,4
[Incorrect]
Incorrect. Refer to Section 15 Lesson 1.
1. An error in PL/SQL is when the compiler does not proceed successfully and an error message is displayed. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 15 Lesson 2.
2. A warning in PL/SQL is the same as an error in PL/SQL, but can only be viewed through the USER_ERRORS data dictionary view. True or False? Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 15 Lesson 2.
3. Which PL/SQL warning message identifies code that can cause unexpected behavior or wrong results when executed? Mark for Review (1) Points
INFORMATIONAL
PERFORMANCE
ALL
SEVERE (*)
ERROR
[Incorrect]
Incorrect. Refer to Section 15 Lesson 2.
4. The informational warning level for PL/SQL compiled code identifies the code that may cause execution speed to be slow. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
5.
The two statements below are equivalent. True or False?
DBMS_WARNING.ADD_WARNING_SETTING_CAT ('INFORMATIONAL','ENABLE','SESSION');
and
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:INFORMATIONAL'; Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 15 Lesson 2.
6. Which pair of DBMS_WARNING commands would allow you to obtain the current settings and change and restore those settings in a PL/SQL subprogram? (Choose two) Mark for Review (1) Points
(Choose all correct answers)
DBMS_WARNING.SET_WARNING_SETTING_STRING (*)
DBMS_WARNING.ADD_WARNING_SETTING_CAT
DBMS_WARNING.GET_WARNING_SETTING_STRING (*)
DBMS_WARNING.GET_WARNING_STRING
[Incorrect]
Incorrect. Refer to Section 15 Lesson 2.
1. Conditional compilation allows you to include extra code to help with debugging, which can be removed once errors are resolved. True or False? Mark for Review
(1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 15 Lesson 3.
2. You can choose which code to include in a PL/SQL program based on conditional compilation directives. True or False? Mark for Review (1) Points
True (*)
False
[Incorrect]
Incorrect. Refer to Section 15 Lesson 3.
3. for Review (1) Points
$IF $THEN $ELSE $END $CCFLAG
$$IF $$THEN $$ELSE $$ELSIF $$END
$IF $THEN $ELSE $ELSIF $ENDIF
$IF $THEN $ELSE $ELSIF
Identify the selection directives used in conditional compilation. compilation.
Mark
$END (*)
$$IF $$THEN $$ELSE $$END $$DEBUG
[Incorrect]
Incorrect. Refer to Section 15 Lesson 3.
4. Inquiry directives are used to selectively include or exclude PL/SQL code based on values of pre-defined variables that are set using the PLSQL_CCFLAGS parameter. parameter. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
5. The value of DBMS_DB_VERSION.VER_LE_11 is TRUE when the version of the Oracle database is version 11 or greater. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
6. If the version and release of the Oracle database in use is 10.2, what statement will allow syntax available in version 10.2 or later? Mark for Review (1) Points
$IF DBMS_DB_VERSION.VER_LE_10_2 $THEN -- some messaage $ELSE -- some action $END
$IF DBMS_DB_VERSION.VER_LE_10_2 $THEN -- some messaage $ELSE -- some action $END;
$IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- some messaage $ELSE -- some action $END (*)
$IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- some messaage $ELSE -- some action $END;
[Incorrect]
1.
Incorrect. Refer to Section 15 Lesson 3.
One benefit of obfuscation is to protect intellectual property written in PL/SQL. True or False? Mark for Review
(1) Points
True (*)
False
[Correct]
Correct
2. Obfuscation allows the owner to see the source code, but not the users to whom EXECUTE privileges have been granted. True or False? Mark for Review (1) Points
True
False (*)
[Incorrect]
Incorrect. Refer to Section 15 Lesson 4.
3. When wrapping subprograms, the entire PL/SQL code must be included as an IN argument with data type VARCHAR2 up to 32,767 characters. True or False? Mark for Review
(1) Points
True (*)
False
[Correct]
Correct
4.
To obfuscate the procedure my_proc, what statement should be at Line A?
BEGIN -- Line A ('CREATE OR REPLACE PROCEDURE mycleverproc (p_param1 IN NUMBER, p_param2 OUT NUMBER) IS BEGIN ... /* some clever but private code here */ END mycleverproc;'); END; Mark for Review (1) Points
DBMS_DML.CREATE_WRAP
DBMS_DDL.CREATE_WRAP
DBMS_DDL.CREATE_WRAPPED (*)
DBMS_DDL.WRAPPED
DBMS_DDL.WRAP_CODE
[Incorrect]
Incorrect. Refer to Section 15 Lesson 4.
5. To create obfuscated code using the wrapper utility, determine the order in which to execute the following steps. A Connect to the database and execute the wrapped text file as a script to compile the wrapped code into the Data Dictionary. B Log into the database server computer. C Create a text file containing your complete unwrapped source code. D Execute WRAP to create a second text file containing the wrapped code. Mark for Review (1) Points
A,B,C,D
B,C,D,A (*)
C,D,A,B
C,A,B,D
B,D,C,A
[Incorrect]
Incorrect. Refer to Section 15 Lesson 4.
6. For PL/SQL code larger than 32,767 characters, you must use the wrap utility. True or False? Mark for Review (1) Points
True (*)
False