Practice exercise

• Which of the following features are not available in SQL Developer?

  1. Query builder.

  2. Database export and import.

  3. Database backup and recovery functions.

  4. Code Subversion repository.

• For a function to be called from a SQL expression, which of the following conditions should it obey?

  1. A function in the SELECT statement should not contain DML statements.

  2. The function should return a value.

  3. A function in the UPDATE or DELETE statement should not query the same table.

  4. A function called from a SQL expression cannot contain TCL (COMMIT or ROLLBACK) commands or DDL (CREATE or ALTER) commands.

• The following query is executed in the SCOTT schema:

    SELECT NAME, referenced_owner, referenced_name
    FROM all_dependencies
    WHERE owner = USER
    AND referenced_type IN ('TABLE', 'VIEW')
    AND referenced_owner IN ('SYS')
    ORDER BY owner, NAME, referenced_owner, referenced_name;

  Which statement is true about the output of this query?

  1. It displays the schema objects, created by the user ORADEV, that use a table or view owned by SYS.

  2. An exception occurs as user SCOTT has insufficient privileges to access ALL_DEPENDENCIES view.

  3. It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.

  4. It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS.

• Which of the following is true about PL/SQL blocks?

  1. Exception is a mandatory section without which an anonymous PL/SQL block fails to compile.

  2. Bind variables cannot be referred inside a PL/SQL block.

  3. The scope and visibility of the variables declared in the declarative section of the block are within the current block only.

  4. The RAISE_APPLICATION_ERROR procedure maps a predefined error message to a customized error code.

• From the following options, identify the ways of defining exceptions:

  1. Declare an EXCEPTION variable and raise it using the RAISE statement.

  2. Use PRAGMA EXCEPTION_INIT to associate a customized exception message to a pre-defined oracle error number.

  3. Declare an EXCEPTION variable and use it in RAISE_APPLICATION_ERROR.

  4. Use RAISE_APPLICATION_ERROR to create a dynamic exception at any stage within the executable or exception section of a PL/SQL block.

• Choose the differences between procedures and functions:

  1. A function must mandatorily return a value, while a procedure may or may not.

  2. A function can be called from a SQL query, while a procedure can never be invoked from SQL.

  3. A function can accept parameters passed by a value, while a procedure can accept parameters passed by reference only.

  4. A standalone function can be overloaded but a procedure cannot.

• Examine the values of the cursor attribute for the following query and pick the attribute with the wrong value:

    BEGIN
    …
    SELECT ENAME, SAL
    INTO L_ENAME, L_SAL
    FROM EMPLOYEES
    WHERE EMPID = 7900;
    …
    END;

  1. SQL%ROWCOUNT = 1

  2. SQL%ISOPEN = FALSE

  3. SQL%FOUND = FALSE

  4. SQL%NOTFOUND = FALSE