Practice exercise
- Which of the following features are not available in SQL Developer?
- Query builder.
- Database export and import.
- Database backup and recovery functions.
- Code Subversion repository.
- For a function to be called from a SQL expression, which of the following conditions should it obey?
- A function in the
SELECTstatement should not contain DML statements. - The function should return a value.
- A function in the
UPDATEorDELETEstatement should not query the same table. - A function called from a SQL expression cannot contain TCL (
COMMITorROLLBACK) commands or DDL (CREATEorALTER) commands.
- A function in the
- 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?
- It displays the schema objects, created by the user
ORADEV, that use a table or view owned bySYS. - An exception occurs as user
SCOTThas insufficient privileges to accessALL_DEPENDENCIESview. - It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.
- It displays only those PL/SQL code objects created by the user
OEthat reference a table or view created by the userSYS.
- It displays the schema objects, created by the user
- Which of the following is true about PL/SQL blocks?
- Exception is a mandatory section without which an anonymous PL/SQL block fails to compile.
- Bind variables cannot be referred inside a PL/SQL block.
- The scope and visibility of the variables declared in the declarative section of the block are within the current block only.
- The
RAISE_APPLICATION_ERRORprocedure maps a predefined error message to a customized error code.
- From the following options, identify the ways of defining exceptions:
- Declare an
EXCEPTIONvariable and raise it using theRAISEstatement. - Use
PRAGMAEXCEPTION_INITto associate a customized exception message to a pre-defined oracle error number. - Declare an
EXCEPTIONvariable and use it inRAISE_APPLICATION_ERROR. - Use
RAISE_APPLICATION_ERRORto create a dynamic exception at any stage within the executable or exception section of a PL/SQL block.
- Declare an
- Choose the differences between procedures and functions:
- A function must mandatorily return a value, while a procedure may or may not.
- A function can be called from a SQL query, while a procedure can never be invoked from SQL.
- A function can accept parameters passed by a value, while a procedure can accept parameters passed by reference only.
- 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;
SQL%ROWCOUNT=1SQL%ISOPEN=FALSESQL%FOUND=FALSESQL%NOTFOUND=FALSE
