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