PLSQL Final

  1.  PL/SQL can be used not only with an Oracle database, but also with any kind of relational database. True or False?
    True
    False (*)

 2.  A program which specifies a list of operations to be performed sequentially to achieve the desired result can be called:

    declarative
    nondeclarative
    procedural (*)
    low level

 3.  Which component of Oracle Application Express is used to enter and run SQL statements and PL/SQL blocks?
     
    Application Builder
    SQL Workshop (*)
    Utilities
    Object Browser

 4.  Which of the following should NOT be used as the name of a variable?

    A table name.
    A table column name. (*)
    The database name.
   
  5.  What will be displayed when the following code is executed?
DECLARE
    x VARCHAR2(6) := 'Chang';
BEGIN
    DECLARE
       x VARCHAR2(12) := 'Susan';
    BEGIN
       x := x || x;
    END;
    DBMS_OUTPUT.PUT_LINE(x);
END;

    Susan
    Chang (*)
    ChangChang
    SusanChang
    The code will fail with an error

 6.  Which of the following are disadvantages of implicit data type conversions? (Choose two.)
   (Choose all correct answers)
   
    The code is harder to read and understand (*)
    You cannot store alphabetic characters in a variable of data type NUMBER
    If Oracle changes the conversion rules in the future, your code may not work any more (*)
    Oracle cannot implicitly convert a number value to a character string
   
  7.  When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?      
    True (*)
     False

 8.  Which of the following are valid identifiers? (Choose two.)  (1) Points (Choose all correct answers)
   
    Full Name
    students_street_address (*)
    v_code (*)
    #hours
    completion_%
 
  9.  A movie is an example of which category of data type?
    Scalar
    Composite
    Reference
    LOB (*)

 10.  Which is the correct way to erase one row from a table?
   
    REMOVE employee_id=100
FROM employees;

    DROP TABLE employees
WHERE employee_id=100;

    TRUNCATE employees
WHERE employee_id=100;

    DELETE FROM employees
WHERE employee_id=100; (*)

  11.  How many DML statements can be included in a single transaction?
    
    Only one
    None. A transaction cannot include DML statements.
    A maximum of four DML statements
    As many as needed (*)

 12.  Which rows will be deleted from the EMPLOYEES table when the following code is executed?
DECLARE
    salary employees.salary%TYPE := 12000;
BEGIN
    DELETE FROM employees
    WHERE salary > salary;
END;

   All rows whose SALARY column value is greater than 12000.
   All rows in the table.
   No rows. (*)
   All rows whose SALARY column value is equal to 12000.
 
 13.  Examine the following code:
DECLARE
    a BOOLEAN := TRUE;
    b BOOLEAN := FALSE;
    c BOOLEAN := TRUE;
    d BOOLEAN := FALSE;
    game char(4) := 'lost';
BEGIN
    IF ((a AND b) AND (c OR d))
    THEN game := 'won';
    END IF;
What is the value of GAME at the end of this block?
   
    NULL
    won'
    lost' (*)
    False
   
  14.  Which of the following blocks produces the same output as this block?
BEGIN
    FOR i in 1 .. 3 LOOP
       DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
   
    DECLARE
    i PLS_INTEGER := 0;
BEGIN
    WHILE i<3 LOOP
       DBMS_OUTPUT.PUT_LINE(i);
       i := i + 1;
    END LOOP;
END;

    DECLARE
    i PLS_INTEGER := 0;
BEGIN
    WHILE i<3 LOOP
       i := i + 1;
       DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END; (*)

   DECLARE
    i PLS_INTEGER := 0;
BEGIN
    WHILE i<3 LOOP
       DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
    i := i+ 1;
END;
   
  15.  Which kind of loop is this?
v_count := 1;
LOOP
    v_count := v_count + 1;
    EXIT WHEN i > 20;
END LOOP;

    FOR loop
    IF-THEN loop
    Basic loop (*)
    WHILE loop
    CASE loop
   
  16.  What kinds of loops can be nested?  (1) Points
    BASIC loops
    WHILE loops
    FOR loops
    All of the above (*)
   
  17.  What value will v_answer contain after the following code is executed?
DECLARE
    v_age NUMBER:= 18;
    v_answer VARCHAR2(10);
BEGIN
    v_answer :=
       CASE
          WHEN v_age < 25 THEN 'Young'
          WHEN v_age = 18 THEN 'Exactly 18'
          ELSE 'Older'
       END CASE;
END;
   
    Exactly 18
    Young (*)
    Null
    Older
   
  18.  What is one of the advantages of using parameters with a cursor? (1) Points
   
    You can use a cursor FOR loop.
    You can declare the cursor FOR UPDATE.
    You do not need to DECLARE the cursor at all.
    You can use a single cursor to fetch a different set of rows each time the cursor is opened. (*)
    It will execute much faster than a cursor without parameters.
   
  19.  The employees table contains 11 columns. The following block declares a cursor and a record based on the cursor:
DECLARE
    CURSOR emp_curs IS
       SELECT * FROM employees;
    v_emp_rec emp_curs%ROWTYPE;
A twelfth column is now added to the employees table. Which of the following statements is true?
   
    The declaration of emp_rec must be changed to add an extra field.
    The block will still work correctly without any changes to the PL/SQL code. (*)
    The block will fail and an INVALID_CURSOR exception will be raised.
    An extra scalar variable must be declared to correspond to the twelfth table column.
   
  20.  There are no employees in department_id 75.
Which of the following is NOT a valid cursor FOR loop with a subquery?

    FOR emp_rec IN
   (SELECT last_name, salary FROM employees) LOOP ...

    FOR emp_rec IN
    (SELECT * FROM employees) LOOP ...

    FOR emp_rec IN
    (SELECT last_name, salary FROM employees ORDER BY last_name) LOOP ...
 
    FOR emp_rec IN
    (SELECT * FROM employees WHERE department_id = 75) LOOP ...

    None of the above. They are all valid. (*)
   
  21.  What will happen when the following code is executed?
DECLARE
    CURSOR emp_curs IS
       SELECT salary FROM employees;
    v_salary employees.salary%TYPE;
BEGIN
    FETCH emp_curs INTO v_salary;
    DBMS_OUTPUT.PUT_LINE(v_salary);
    CLOSE emp_curs;
END;

    The first employee's salary will be fetched and displayed.
    All employees' salaries will be fetched and displayed.
    The execution will fail and an error message will be displayed. (*)
    The lowest salary value will be fetched and displayed.

 22.  Examine the following code:
DECLARE
CURSOR c IS SELECT * FROM employees FOR UPDATE;
c_rec c%ROWTYPE;
BEGIN
OPEN c;
FOR i IN 1..20 LOOP
FETCH c INTO c_rec;
IF i = 6 THEN
UPDATE employees SET first_name = 'Joe'
WHERE CURRENT OF c;
END IF;
END LOOP;
CLOSE c;
END;
Which employee row or rows will be updated when this block is executed?

    The first 6 fetched rows will be updated.
    No rows will be updated because you locked the rows when the cursor was opened.
    The 6th fetched row will be updated. (*)
    The block will not compile because the cursor should have been declared .... FOR UPDATE WAIT 5;
    None of the above.
   
  23.  When using multiple nested cursors, what kinds of loops can you use?      
    Cursor FOR loops only.
    Basic loops only.
    WHILE loops only.
    None of the above.
    All of the above. (*)
   
  24.  The following exception handler will successfully insert the Oracle error number and error message into a log table whenever an Oracle Server error occurs. True or False?
EXCEPTION
    WHEN OTHERS THEN
       INSERT INTO err_log_table (num_col, char_col)
          VALUES (SQLCODE, SQLERRM);
END;
(Assume that err_log_table has been created with suitable columns and datatypes.)
   
    True
    False (*)
   
  25.  Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block?      
    A SELECT statement returns no rows
    A SELECT statement returns more than one row
    Any other kind of exception that can occur within the block
    All of the above (*)
    None of the above
   
  26.  A user-defined exception can be raised:
A. In the declaration section
B. In the executable section
C. In the exception section
   
    B
    C
    A and B
    B and C (*)
    A and C
   
  27.  There are no employees in department 75. What will be displayed when this code is executed?
DECLARE
    v_last_name employees.last_name%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('A');
    BEGIN
       SELECT last_name INTO v_last_name
          FROM employees WHERE department_id = 75;
       DBMS_OUTPUT.PUT_LINE('B');
    END;
    DBMS_OUTPUT.PUT_LINE('C');
EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('D');
END;
A
C
D

A
D (*)

A

A
B
D
 
None of the above
   
  28.  Suppose you set up a parameter with an explicit IN mode. What is true about that parameter?
   
    It must have a DEFAULT value.
    It cannot have a DEFAULT value.
    It acts like a constant (its value cannot be changed inside the subprogram). (*)
    It must be the same type as the matching OUT parameter.
    It inherits its type from the matching OUT parameter.
   
  29.  A stored PL/SQL procedure can be invoked from which of the following?
A PL/SQL anonymous block
Another PL/SQL procedure
A calling application
   
    A only
    A and B
    A and C
    A, B and C (*)
    B and C
   
  30.  You have created the following procedure:
CREATE OR REPLACE PROCEDURE double_it
(p_param IN OUT NUMBER)
IS
BEGIN
p_param := p_param * 2;
END;
Which of the following anonymous blocks invokes this procedure successfully?

    BEGIN
EXECUTE double_it(20);
END;

    BEGIN
SELECT double_it(20)
FROM DUAL;
END;

    DECLARE
v_result NUMBER(6);
BEGIN
v_result := double_it(20);
END;

    DECLARE
v_result NUMBER(6) := 20;
BEGIN
double_it(v_result);
END; (*)

    BEGIN
double_it(20);
END;

  31.  Which one of the following statements about user-defined functions is NOT true?
   
    They can execute spell-checking routines.
    They can be used inside SQL statements.
    They can be combined (nested) together, similar to nesting system functions, for example INITCAP(SUBSTR( .....)).
    They can return a TIMESTAMP datatype.
    They can allow you to COMMIT from inside a SELECT statement. (*)

  32.  Examine the following code:
CREATE OR REPLACE FUNCTION add_func
(p_param1 NUMBER, p_param2 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (p_param1 + p_param2);
END;
What will be displayed when the following SQL statement is executed?
SELECT add_func(6, add_func(3,8)) FROM dual;
   
    23
    11
    66
    17 (*)
    An error message will be displayed because you cannot nest user-defined functions.
   
  33.  How do you specify that you want a procedure MYPROCA to use "Definer's Rights"?
   
    CREATE OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS...
 
    CREATE OR REPLACE PROCEDURE myproca
AUTHID OWNER IS...
 
    GRANT DEFINER TO myprocA;
 
    ALTER PROCEDURE myproca TO DEFINER;
 
    Definer's Rights are the default, therefore no extra code or commands are needed. (*)
   
  34.  Examine the following code: CREATE PROCEDURE parent
IS BEGIN
    child1;
    child2;
EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
END parent;
Neither CHILD1 nor CHILD2 has an exception handler.
When PARENT is invoked, CHILD1 raises a NO_DATA_FOUND exception. What happens next?
   
    PARENT handles the exception, then CHILD1 continues to execute.
    CHILD1 ends abruptly. PARENT handles the exception and then ends. CHILD2 does not execute. (*)
    CHILD1 ends abruptly, PARENT handles the exception, then CHILD2 executes.
    CHILD1 ends abruptly, PARENT also ends abruptly and returns an unhandled exception.
    PARENT does not compile because you cannot use NULL; in an exception handler.
   
  35.  Examine the following package code:
CREATE OR REPLACE PACKAGE ol_pack IS
    PROCEDURE subprog (p1 IN VARCHAR2, p2 IN NUMBER);
    PROCEDURE subprog (param1 IN CHAR, param2 IN NUMBER);
    FUNCTION subprog (param1 IN VARCHAR2, param2 IN NUMBER) RETURN DATE;
END ol_pack;

Which of the following calls will be successful? (Choose two.) (Choose all correct answers)
   
    ol_pack.subprog('Jane',30);
    ol_pack.subprog(param1=>'Jane',param2=>30); (*)
    v_number := ol_pack.subprog(p1=>'Jane');
    v_date := ol_pack.subprog('Jane',30); (*)
   
  36.  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: 45, Hazel: 45
    Dick: 45, Hazel: 0
    Dick: 45, Hazel: null (*)
    Dick: 0, Hazel: 0
    Both queries will fail because the syntax of DBMS_OUTPUT.PUT_LINE is incorrect

 37.  Your schema contains four packages, each having a specification and a body. You have also been granted privileges to access three packages (and their bodies) in other users' schemas. What will be displayed by the following query?
SELECT COUNT(*) FROM ALL_OBJECTS
    WHERE object_type LIKE 'PACK%'
    AND owner <> USER;
   
    14
    7
    3
    6 (*)
    0
   
  38.  You want to create a function which drops a table. You write the following code:
CREATE OR REPLACE FUNCTION droptab
    (p_tab_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
    DROP TABLE p_tab_name;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;
Why will this procedure not compile successfully?

    Because you can never drop a table from inside a function
    Because the PL/SQL compiler cannot check if the argument of p_tab_name is a valid table-name (*)
    Because you do not have the privilege needed to drop a table
    Because you cannot use RETURN in the exception section
   
  39.  Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?
    True (*)
    False
   
  40.  DBMS_OUTPUT.PUT_LINE can be invoked from inside a private packaged function. True or False?    
    True (*)
    False
   
  41.  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;
    You cannot use RAISE_APPLICATION_ERROR inside a trigger.
    The second line should be: BEFORE DELETE OF locations
    You cannot use ROLLBACK inside a trigger. (*)
    Nothing is wrong, this trigger will compile and execute successfully.

 42.  Examine the following code. To create a row trigger, what code should be included at Line A?
CREATE TRIGGER dept_trigg
AFTER UPDATE OR DELETE ON departments
    -- Line A
BEGIN ...

    AFTER EACH ROW
    FOR EVERY ROW
    FOR EACH ROW (*)
    ON EACH ROW
    ON EVERY ROW
   
  43.  The following objects have been created in a user's schema:
- a function FUNC1
- A package PACK1 which contains a public procedure PACKPROC and a private function PACKFUNC
- a trigger TRIGG1.
The procedure and functions each accept a single IN parameter of type NUMBER, and the functions return BOOLEANs. Which of the following calls to these objects (from an anonymous block) are correct? (Choose two.) (Choose all correct answers)
   
    pack1.packproc(25); (*)
    SELECT func1(100) FROM dual;
    trigg1;
    IF pack1.packfunc(40) THEN ...
    IF func1(75) THEN ... (*)
   
  44.  Examine this code:
CREATE TRIGGER de_trigg
    -- Line A
BEGIN ...

Which of the following are NOT valid at Line A ? (Choose two.) (Choose all correct answers)
   
    AFTER LOGOFF ON SCHEMA (*)
    AFTER LOGON ON SCHEMA
    BEFORE LOGOFF ON SCHEMA
    BEFORE DISCONNECT ON SCHEMA (*)
    AFTER SERVERERROR ON SCHEMA
   
  45.  Which of the following will remove a trigger in your schema named EMP_TRIGG from the database?      
    DROP emp_trigg TRIGGER;
    ALTER TRIGGER emp_trigg DISABLE;
    DROP TRIGGER emp_trigg; (*)
    REMOVE TRIGGER emp_trigg;
    None of the above
 
  46.  Which of the following will declare a composite PL/SQL data type named COMPO_TYPE, containing two fields named FIELD1 and FIELD2?
    DECLARE
    compo_type
       (field1 NUMBER,
       field2 VARCHAR2(30));
 
    DECLARE
    TYPE compo_type IS
       (field1 NUMBER,
       field2 VARCHAR2(30));
 
    DECLARE
    TYPE compo_type IS RECORD
       (field1 NUMBER,
       field2 VARCHAR2(30)); (*)

    DECLARE
    compo_type IS RECORD
       (field1 NUMBER,
       field2 VARCHAR2(30));
   
  47.  You have created several directory objects in the database, as pointers to operating system directories which contain BFILEs. Which data dictionary view would you query to see these directories?    
    USER_DIRECTORIES
    USER_BFILES
    ALL_DIRECTORIES (*)
    USER_EXTERNAL_FILES
    ALL_BFILES

  48.  Which of the following successfully declares an INDEX BY table named DEPT_NAMES_TAB, which could be used to store all the department names from the DEPARTMENTS table?
    DECLARE
    TYPE t_dnames IS TABLE OF
       departments.department_name%TYPE
       INDEX BY INTEGER;
    dept_names_tab t_dnames;
 
    DECLARE
    TYPE t_dnames IS TABLE OF
       departments.department_name%TYPE
       INDEX BY BINARY_INTEGER;
    dept_names_tab t_dnames; (*)

     DECLARE
    TYPE t_dnames IS TABLE OF
       departments.department_name%TYPE
       INDEX BY PLS_INTEGER;
    dept_names_tab t_dnames%TYPE;

    DECLARE
    TYPE t_dnames IS TABLE OF
       department_name
       INDEX BY BINARY_INTEGER;
    dept_names_tab t_dnames;

  49.  CLOB and BLOB are internal LOB datatypes, while BFILE is an external LOB datatype. True or False?
   
    True (*)
    False
   
  50.  A single procedure can be both a referenced object and a dependent object. True or False?    
    True (*)
    False