Semester 2 Final Exam

Semester 2 Final Exam

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?

                                                                               

                                               

                One time

                                               

                Two times

                                               

                Four times (*)

                                               

                Five times

                                               

                Eight times

                                                                               

                                                               

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.)

                                                                               

                                                (Choose all correct answers)      

                                                                               

                                               

                (:NEW.employee_id, :NEW.job_id); (*)

                                               

                (:OLD.employee_id, :OLD.job_id);

                                               

                (NEW.employee_id, NEW.job_id);

                                               

                (:OLD.employee_id, :NEW.job_id); (*)

                                               

                (:NEW.employee_id, :OLD.job_id);

                                                                               

                                                               

What are the components of a compound trigger?       

                                                                               

                                               

                Declaration section and all four timing sections.

                                               

                Declaration section and at least two timing sections.

                                               

                Declaration section, referencing section, and timing sections.

                                               

                Declaration section, timing sections, and exception section.

                                               

                Declaration section and at least one timing section. (*)

                                                                               

                                                               

Which of the following statements about INSTEAD OF triggers are NOT true? (Choose two.)         

                                                                               

                                                (Choose all correct answers)      

                                                                               

                                               

                They can be created on a complex view.

                                               

                They can be created on a simple view.

                                               

                They can be created on a table. (*)

                                               

                They can be statement triggers. (*)

                                               

                They can be row triggers.

                                                                               

                                                               

Which of the following can NOT be coded in the body of a DML trigger? (Choose two.)   

                                                                               

                                                (Choose all correct answers)      

                                                                               

                                               

                IF SELECTING THEN (*)

                                               

                IF DELETING THEN

                                               

                IF OTHERS THEN (*)

                                               

                IF INSERTING THEN

                                               

                IF UPDATING ('JOB_ID') THEN

Examine the following trigger. It should raise an application error if a user tries to update an employee's last name. It should allow updates to all other columns of the EMPLOYEES table. What should be coded at line A?

CREATE TRIGGER stop_ln_trigg

BEFORE UPDATE ON employees

BEGIN

       -- Line A

    RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');

    END IF;

END;

                                                            

                                               

                IF UPDATING LAST_NAME THEN

                                               

                IF UPDATING('LAST_NAME') THEN (*)

                                               

                IF UPDATING THEN

                                               

                IF UPDATE('LAST_NAME') THEN

                                                                               

                                                               

Which of the following are NOT stored inside the database? 

                                                                               

                                               

                An anonymous block (*)

                                               

                A database trigger

                                               

                A PL/SQL package specification

                                               

                A sequence

                                               

                An index

                                                                               

                                                               

Which of the following best describes a database trigger?

                                                                               

                                               

                It allows foreign key constraints to be violated.

                                               

                It allows users to log on to the database.

                                               

                It executes automatically whenever a user clicks on a button with his mouse.

                                               

                It executes automatically whenever a particular event occurs within the database. (*)

                                               

                It prevents unique constraints from being violated.

                                                                               

                                                               

You can code COMMIT and ROLLBACK statements in a trigger body. True or False? 

                                                                               

                                               

                True

                                               

                False (*)

                                                                               

                                                               

You can use a trigger to prevent rows from being deleted from the EMPLOYEES table on Mondays. True or False?    

                                                                               

                                               

                True (*)

                                               

                False

Which of the following best describes a database trigger?  

                                                                               

                                               

                A subprogram that checks whether a user has typed the correct password to log on to the database

                                               

                A PL/SQL subprogram that executes automatically whenever an associated database event occurs (*)

                                               

                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

                                                                               

                                                               

You can use a database trigger to prevent invalid transactions from being committed. True or False?  

                                                                               

                                               

                True (*)

                                               

                False

                                                                               

                                                               

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;

                                                                               

                                               

                When using CALL, only one DML statement can be tested, so UPDATE OR DELETE is wrong.

                                               

                You cannot use a CALL statement in a DML trigger.

                                               

                When CALL is used, the BEGIN and END; statements should be omitted. (*)

                                               

                The CALL statement should end with a semicolon (;)

                                                                               

                                                               

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?   

                                                                               

                                               

                True

                                               

                False (*)

                                                                               

                                                               

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?

                                                                               

                                               

                IF (IS_SERVERERROR(ORA-00942)) THEN

                                               

                IF (IS_SERVERERROR = 942) THEN

                                               

                IF (SERVERERROR = 942) THEN

                                               

                IF (IS_SERVERERROR(942)) THEN (*)

                                               

                IF (SERVERERROR(942)) THEN

Which of the following could NOT cause a DDL or Database Event trigger to fire? 

                                               

                A table is dropped.

                                               

                The DBA starts up the database.

                                               

                A user connects to the database.

                                               

                A specific exception is raised in a user's session.

                                               

                A user deletes rows from the EMPLOYEES table. (*)

                                                                               

                                                               

The database administrator creates a trigger that automatically disconnects user HACKER whenever HACKER connects to the database. What type of trigger is this? 

                                               

                A DDL trigger

                                               

                A statement trigger

                                               

                An INSTEAD OF trigger

                                               

                A Database Event trigger (*)

                                               

                A DML trigger

                                                                               

                                                               

Which command would you use to see if your triggers are enabled or disabled? 

                                                                               

                                               

                SELECT trigger_name, status

FROM USER_TRIGGERS;

(*)

                                               

                DESCRIBE TRIGGER

                                               

                SELECT object_name, status

FROM USER_OBJECTS

WHERE object_type = 'TRIGGER';

                                               

                SELECT trigger_name, trigger_type

FROM USER_TRIGGERS;

                                                                               

                                                               

You need to disable all triggers that are associated with DML statements on the DEPARTMENTS table. Which of the following commands should you use?     

                                               

                ALTER TRIGGER DISABLE ALL ON departments;

                                               

                ALTER TABLE departments DISABLE ALL TRIGGERS; (*)

                                               

                DISABLE ALL TRIGGERS ON departments;

                                               

                ALTER TABLE departments DISABLE TRIGGERS;

                                               

                ALTER TABLE departments DROP ALL TRIGGERS;

                                                                               

                                                               

By default, any user can create a DML trigger on a table in his/her schema. True or False?               

                                                                               

                                               

                True

                                               

                False (*)

Examine the following code:

CREATE TRIGGER emp_trigg

    -- Line A

BEGIN

    INSERT INTO log_table VALUES (USER, SYSDATE);

END;

Which of the following can NOT be coded at Line A?

                                                             

                                               

                BEFORE DELETE ON employees

                                               

                BEFORE UPDATE ON employees

                                               

                AFTER INSERT OR DELETE ON employees

                                               

                AFTER UPDATE OF last_name ON employees

                                               

                AFTER SELECT ON employees (*)

                                                                               

                                                               

An AFTER UPDATE trigger can specify more than one column. True or False?

                                                                               

                                               

                True (*)

                                               

                False

                                                                               

                                                               

What is wrong with the following code?

CREATE OR REPLACE TRIGGER loc_trigg

BEFORE DELETE ON locations

BEGIN

    RAISE_APPLICATION_ERROR(-20201,'Invalid delete');

    ROLLBACK;

END;

                                                                               

                                               

                The last line should be:

END loc_trigg;

                                               

                The second line should be:

BEFORE DELETE OF locations

                                               

                You cannot use RAISE_APPLICATION_ERROR inside a trigger.

                                               

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

                                               

                You cannot use ROLLBACK inside a trigger.

(*)

                                                                               

                                                               

There are five employees in department 50. The following trigger is created:

CREATE TRIGGER upd_emp

AFTER UPDATE ON employees

BEGIN

    INSERT INTO audit_table VALUES (USER, SYSDATE);

END;

A user now executes:

UPDATE employees

SET salary = salary * 1.1

    WHERE department_id = 50;

How many rows will be inserted into audit_table?

                                                                               

                                               

                One (*)

                                               

                Two

                                               

                Five

                                               

                Six

                                               

                None of these.

                                                                               

                                                               

A DML statement trigger fires only once for each triggering DML statement, while a row trigger fires once for each row processed by the triggering statement. 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 (*)

                                                                               

                                                               

DBMS_OUTPUT.PUT_LINE can be invoked from inside a private packaged function. True or False?   

                                                                               

                                               

                True (*)

                                               

                False

                                                                               

                                                               

Which of the following exceptions can be raised ONLY when using the UTL_FILE package? (Choose two.)  

                                                                               

                                                (Choose all correct answers)      

                                                                               

                                               

                INVALID_PATH (*)

                                               

                VALUE_ERROR

                                               

                READ_ERROR (*)

                                               

                NO_DATA_FOUND

                                               

                E_MYEXCEP

                                                                               

                                                               

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

                                                                               

                                                               

Why is it better to use DBMS_OUTPUT only in anonymous blocks, not inside stored subprograms such as procedures?  

                                                                               

                                               

                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 anonymous blocks display messages while the block is executing, while procedures do not display anything until their execution has finished

                                               

                Because DBMS_OUTPUT cannot be used inside procedures

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 MULTIPACK declares the following global variable:

    g_myvar NUMBER;

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: 0, Hazel: 0

                                               

                Dick: 45, Hazel: 45

                                               

                Dick: 45, Hazel: 0

                                               

                Both queries will fail because the syntax of DBMS_OUTPUT.PUT_LINE is incorrect

                                               

                Dick: 45, Hazel: null (*)

                                                                               

                                                               

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

                                                                               

                                               

                50 (*)

                                               

                25

                                               

                10

                                                                               

                                                               

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;

                                                                               

                                               

                curs_pkg.close_curs;

                                               

                curs_pkg.emp_curs%ISOPEN;

                                               

                EXIT WHEN curs_pkg.emp_curs%NOTFOUND;

                                               

                curs_pkg.open_curs; (*)

                                                                               

                                                               

A cursor is declared in a package specification. User SIOBHAN opens the cursor and fetches the first three rows from the cursor's active set, but does not close the cursor.

User FRED now connects to the database. FRED can immediately fetch the next three rows without opening the cursor. True or False?

                                                                               

                                               

                True

                                               

                False (*)

A package's state is initialized when the package is first loaded. True or False?

                                                                               

                                               

                True (*)

                                               

                False

                                                                               

                                                               

When a user session changes the value of a package variable, the new value can immediately be seen by other sessions. True or False?  

                                                                               

                                               

                True

                                               

                False (*)

                                                                               

                                                               

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?

    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 10 rows will be fetched, then the first 5 rows will be fetched again.

                                               

                The first 15 rows will be fetched.

                                               

                An error will occur at line 2.

                                               

                The first 5 rows will be fetched three times.

                                               

                An error will occur at line 4. (*)

                                                                               

                                                               

You created a package named pkg1. The code is approximately 90,000 characters. What is the statement that you use to obfuscate this package in the database?

                                                                               

                                               

                DBMS_DDL.CREATE_WRAPPED ('CREATE OR REPLACE PACKAGE BODY pkg1...);

                                               

                DBMS_DDL.CREATE_WRAP (pkg1);

                                               

                DBMS_DDL.CREATE_WRAPPED (pkg1);

                                               

                WRAP pkg1.sql

                                               

                WRAP INAME=pkg1.sql (*)

                                                                               

                                                               

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;

                                                                               

                                               

                DBMS_DDL.WRAP_CODE

                                               

                DBMS_DML.CREATE_WRAP

                                               

                DBMS_DDL.CREATE_WRAPPED (*)

                                               

                DBMS_DDL.WRAPPED

                                               

                DBMS_DDL.CREATE_WRAP

For PL/SQL code larger than 32,767 characters, you must use the wrap utility. True or False?                                                                     

                                               

                True (*)

                                               

                False

                                                                               

                                                               

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?

                                                                               

                                               

                True

                                               

                False (*)

                                                                               

                                                               

What is the name of the column used to identify the PLSQL_OPTIMIZE_LEVEL in the data dictionary?

                                                                               

                                               

                PLSQL_CODE_TYPE

                                               

                PLSQL_OPTIMIZE_LEVEL (*)

                                               

                PLSQL_LEVEL

                                               

                USER_PLSQL_OPTIMIZE

                                               

                OPTIMIZE_LEVEL

                                                                               

                                                               

Which are NOT examples of benefits of using PLSQL_OPTIMIZE_LEVEL. (Choose two)  

                                                                               

                                                (Choose all correct answers)      

                                                                               

                                               

                Combining compiled code from one subprogram into another subprogram

                                               

                Modify source code to optimize frequently-used elements at the top (*)

                                               

                Control what PL/SQL does with useless code

                                               

                Separating compiled code so that separate units may be repeated as needed (*)

                                               

                Backward compatible with previous versions of the Oracle database

                                                                               

                                                               

Identify the selection directives used in conditional compilation.   

                                                                               

                                               

                $$IF

                $$THEN

                $$ELSE

                $$ELSIF

                $$END

                                               

                $IF

                $THEN

                $ELSE

                $ELSIF

                $END

                (*)

                                               

                $IF

                $THEN

                $ELSE $ELSIF

                $ENDIF

                                               

                $IF

                $THEN

                $ELSE

                $END

                $CCFLAG

                                               

                $$IF

                $$THEN

                $$ELSE

                $$END

                $$DEBUG

In the following example, what statement belongs in Line A?

ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true';

CREATE OR REPLACE PROCEDURE testproc IS BEGIN

...

   $IF $$debug $THEN

     DBMS_OUTPUT.PUT_LINE('This code was executed');

           -- Line A

...

END testproc;

ALTER SESSION SET PLSQL_CCFLAGS = 'debug:false';

                                                                               

                                               

                $END;

                                               

                $END (*)

                                               

                $ENDIF

                                               

                $ELSIF

                                               

                $$END;

                                                                               

                                                               

Identify some benefits of using conditional compilation. (Choose two) 

                                                                               

                                                (Choose all correct answers)      

                                                                               

                                               

                Determine initialization values during start up of a database session

                                               

                Activate debugging or tracing statements in the development environment (*)

                                               

                Speed up the compilation time of a lengthy PL/SQL subprogram

                                               

                Use new features with the latest database release and disable them with older database versions (*)

                                                                               

                                                               

An error in PL/SQL is when the compiler does not proceed successfully and an error message is displayed. True or False?

                                                                               

                                               

                True (*)

                                               

                False

                                                                               

                                                               

Which term best describes the action below:

A PL/SQL program compiles successfully, but contains some code that causes performance to be less than optimal.

                                                           

                                               

                Warning (*)

                                               

                Error

                                                                               

                                                               

The informational warning level for PL/SQL compiled code identifies the code that may cause execution speed to be slow. True or False?  

                                                                               

                                               

                True

                                               

                False (*)