Using PL/SQL in Oracle 10g
After completing this topic, you should be able to recognize the basic concepts of PL/SQL.
In this exercise, you're required to demonstrate your knowledge of PL/SQL concepts.
This involves the following task:
- identifying concepts pertaining to PL/SQL objects
Task 1: Identifying PL/SQL object concepts
You are a DBA and you are going to be advising developers on the use of PL/SQL objects in application development. You therefore need to ensure that you are familiar with PL/SQL concepts.
Step 1 of 7
Match the types of PL/SQL objects to their characteristics.
- Package body
- Implements the package specification
- Returns a single value
- Performs a specific action
- Executes in response to a specific event
- Groups related functions and procedures
A function returns a single value and a package groups related functions and procedures. A package body implements the package specification and a procedure performs a specific action. A trigger executes in response to a specific event.
A function always uses the
RETURN command to return a value.
The package specification declares the subprograms, variables, and constants available for use.
The package body contains details and declarations that are hidden from your application.
Procedures are called with the
Triggers can fire in response to table or database events.
Step 2 of 7
Match the characteristics according to whether they belong to functions or procedures.
- Called with the
- Must include a
- Can transfer values through arguments
- Used to return values
Procedures are called with the
CALL command and transfer values through arguments. Functions must include a
RETURN statement, and are used to return values.
Step 3 of 7
What are the characteristics of packages?
- They are always associated with user-defined data types
- They consist of a package body and a package specification
- They group functions and procedures
- They improve performance and maintenance
Packages consist of a package body and a package specification. They group functions and procedures and improve performance and maintenance.
Option 1 is incorrect. The
TYPE BODY PL/SQL object groups functions and procedures associated with user-defined data types.
Option 2 is correct. Package specifications and package bodies are compiled separately.
Option 3 is correct. You call procedures and functions from within the package using dot notation.
Option 4 is correct. Grouping related functions and procedures into packages improves performance and maintainability.
Step 4 of 7
Match the correct characteristics to package bodies and package specifications.
- Contain code for subprograms
- Contain definitions for procedures and functions
- May be wrapped
- Contain information on calling subprograms
- Package bodies
- Package specifications
Package bodies contain code for subprograms and can be wrapped. Package specifications contain definitions for procedures and functions, as well as information on calling subprograms.
Step 5 of 7
Identify the initialization parameters that control PL/SQL performance.
The initialization parameters that control PL/SQL performance are
Option 1 is correct. The
PLSQL_CODE_TYPE initialization parameter enables you to choose to store code as interpreted bytecode or as native machine code.
Option 2 is correct. The
PLSQL_DEBUG initialization parameter enables debugging features for development and forces code to be stored as interpreted.
Option 3 is incorrect. The
PLSQL_NATIVE_LIBRARY_DIR initialization parameter is used to create the directory to hold shared libraries.
Option 4 is correct. The
PLSQL_OPTIMIZE_LEVEL initialization parameter enables you to optimize compiler performance or improve runtime performance.
Option 5 is correct. The
PLSQL_WARNING initialization parameter controls warning messages from the PL/SQL compiler.
Step 6 of 7
How can PL/SQL triggers be used?
- To call procedures and functions
- To enforce complex constraints
- To perform value-based auditing
- To store large blocks of code
PL/SQL triggers can be used to call procedures and functions, to enforce constraints, and to perform value-based auditing.
Option 1 is correct. Triggers can call procedures and functions from packages, so the trigger code can be kept short.
Option 2 is correct. Triggers should only be used to enforce complex constraints that can not be supported by the other integrity features provided with Oracle Database 10g.
Option 3 is correct. Value-based auditing enables you to gather information on values that have been changed in a database.
Option 4 is incorrect. Developers usually keep trigger code short and place lengthy code in a separate package.
Step 7 of 7
Identify the PL/SQL settings that ensure the fastest performance.
The PL/SQL settings that provide the fastest performance are
Option 1 is correct. Native machine code provides better runtime performance than interpreted code.
Option 2 is incorrect. To improve runtime performance, you should turn off extended debugging features by setting the
PLSQL_DEBUG parameter to
Option 3 is correct. This setting improves runtime performance but at the expense of compiler performance. It should be used only if applications use stored PL/SQL.
Option 4 is correct. This setting disables all warning messages from the PL/SQL compiler, thus improving performance.