Using PL/SQL in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize the basic concepts of PL/SQL.

Exercise overview

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.

Options:

1. Function
2. Package
3. Package body
4. Procedure
5. Trigger

Targets:

1. Implements the package specification
2. Returns a single value
3. Performs a specific action
4. Executes in response to a specific event
5. Groups related functions and procedures

Result

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 CALL command.

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.

Options:

1. Called with the CALL command
2. Must include a RETURN statement
3. Can transfer values through arguments
4. Used to return values

Targets:

1. Procedures
2. Functions

Result

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?

Options:

1. They are always associated with user-defined data types
2. They consist of a package body and a package specification
3. They group functions and procedures
4. They improve performance and maintenance

Result

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.

Options:

1. Contain code for subprograms
2. Contain definitions for procedures and functions
3. May be wrapped
4. Contain information on calling subprograms

Targets:

1. Package bodies
2. Package specifications

Result

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.

Options:

1. PLSQL_CODE_TYPE
2. PLSQL_DEBUG
3. PLSQL_NATIVE_LIBRARY_DIR
4. PLSQL_OPTIMIZE_LEVEL
5. PLSQL_WARNING

Result

The initialization parameters that control PL/SQL performance are PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL, and PLSQL_WARNING.

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?

Options:

1. To call procedures and functions
2. To enforce complex constraints
3. To perform value-based auditing
4. To store large blocks of code

Result

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.

Options:

1. PLSQL_CODE_TYPE=NATIVE
2. PLSQL_DEBUG=TRUE
3. PLSQL_OPTIMIZE_LEVEL=2
4. PLSQL_WARNINGS=DISABLE:ALL

Result

The PL/SQL settings that provide the fastest performance are PLSQL_CODE_TYPE=NATIVE, PLSQL_OPTIMIZE_LEVEL=2, and PLSQL_WARNINGS=DISABLE:ALL.

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 FALSE.

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.