Using Oracle 10g with PL/SQL, Java, and OCI

Learning objective

After completing this topic, you should be able to identify how Oracle works with common database interfaces.

Exercise overview

In this exercise, you're required to identify how Oracle works with common database interfaces.

This involves the following tasks:

• differentiating PL/SQL from SQL
• identifying PL/SQL code structures
• identifying how Oracle uses PL/SQL
• identifying how Oracle uses Java
• identifying how Oracle uses the OCI

Task 1: Differentiating PL/SQL from SQL

PL/SQL is an extension of SQL.

Before you can use PL/SQL, it's helpful to know what it can do that SQL by itself cannot.

Step 1 of 1

How does PL/SQL extend SQL?

Options:

1. It allows you to declare variables, constants, and cursors
2. It allows you to use programming control structures, such as WHILE iterations and IF conditions
3. It provides additional nonprocedural capabilities
4. It provides extra error-handling capabilities

Result

PL/SQL extends SQL by allowing you to declare variables, constants, and cursors, and use programming control structures such as WHILE iterations and IF conditions. It also provides extra-error handling capabilities.

Option 1 is correct. You can declare variables, constants, and cursors in the DECLARE part of a PL/SQL block.

Option 2 is correct. You can place procedural code - including WHILE iterations and IF conditions - in the BEGIN part of an SQL block.

Option 3 is incorrect. In fact, SQL is a nonprocedural language. PL/SQL extends it to provide extra procedural capabilities, such as variable declaration, the inclusion of programming control structures, and error-handling capabilities.

Option 4 is correct. The EXCEPTION part of an SQL block allows you to do this.

Task 2: Structuring PL/SQL code

Before you can work with PL/SQL, you need to be able to identify the structure of a PL/SQL block.

Step 1 of 2

Match the actions to the parts of a PL/SQL block in which they would occur.

Options:

1. Define a variable
2. Perform a SQL query
3. Handle an error
4. Include a procedural statement
5. Define a constant

Targets:

1. DECLARE
2. BEGIN
3. EXCEPTION

Result

In the DECLARE part of a PL/SQL block, you can define a variable or a constant. In the BEGIN part, you can include an SQL query or a procedural statement. And in the EXCEPTION part, you can handle an error.

Step 2 of 2

Order the parts of a PL/SQL block that contains all four of its possible parts.

Options

Option	Description
A	BEGIN
B	DECLARE
C	END
D	EXCEPTION

Result

Correct ranking

Option	Description
B	DECLARE The optional DECLARE part allows you to define variables, types, and constants. You must declare any variables or constants before using them in other statements.
A	BEGIN The mandatory BEGIN part marks the beginning of the block's code. It can contain a combination of SQL statements and procedural statements. In terms of languages, this code will consist of SQL and PL/SQL commands.
D	EXCEPTION The optional EXCEPTION part is where you handle errors. In this part, you can define local exceptions that may arise as a result of an error condition.
C	END The mandatory END part ends a block of code. For every BEGIN part, you must include an END part.

Task 3: Identifying how Oracle uses PL/SQL

PL/SQL is widely used in Oracle so it's important to know how it works with an Oracle database.

Step 1 of 1

Which of these statements accurately describe the use of PL/SQL code when querying and writing to an Oracle database?

Options:

1. You can define your own object type
2. You can make a block execute when a change happens in a database
3. You can write a function that performs calculations and returns a bank balance
4. You must name all PL/SQL blocks

Result

You can define your own object type using PL/SQL. You can make a block execute when a change occurs in a database. And you can write a function that performs calculations and returns a bank balance.

Option 1 is correct. An object type is a kind of data type that can include functionality as well as data.

Option 2 is correct. In PL/SQL, you use a trigger to execute a block of SQL code when something happens in the database - for example, when a table is inserted.

Option 3 is correct. You can write a PL/SQL function to return a bank balance. A function performs a set of actions and returns a value.

Option 4 is incorrect. PL/SQL supports unnamed blocks called anonymous blocks that are typically nested within named blocks. These give you extra programmatic control.

Task 4: Identifying how Oracle works with Java

Java code is widely used in Oracle, so it's important to know how it works in conjunction with an Oracle database.

Step 1 of 1

Which of these statements accurately describe how Java and Oracle interact?

Options:

1. When working with Oracle data, you can invoke Java code from a PL/SQL block
2. You can use the SQLJ API when making the Oracle database a back end to Java applications
3. You must install the Oracle Java Virtual Machine in every Oracle database
4. The Oracle Java Virtual Machine runs in a different process space from the Oracle database

Result

When working with Oracle data, you can invoke Java code from a PL/SQL block. And you can use the SQLJ API when making the Oracle database a back end to Java applications.

Option 1 is correct. In Oracle, you can invoke a Java stored procedure from a PL/SQL block, a subprogram, or a package. Similarly, you can invoke PL/SQL code using Java.

Option 2 is correct. You would typically use this interface when the SQL you're using is primarily static.

Option 3 is incorrect. The Oracle JVM is already included in every Oracle database.

Option 4 is incorrect. The Oracle JVM runs in the same process space as the Oracle database.

Task 5: Identifying how Oracle uses the OCI

Because the Oracle Call Interface (OCI) API makes all database features accessible to applications, you need to know how it works.

Step 1 of 1

Which of these are characteristics of the OCI?

Options:

1. It allows you to combine procedural and nonprocedural programming languages
2. Its programs must be written in C
3. It provides dynamic session and transaction management
4. Programs using it must be precompiled

Result

The OCI allows you to combine procedural and nonprocedural programming languages and it provides dynamic session and transaction management.

Option 1 is correct. C is an example of a procedural programming language. And SQL is an example of a nonprocedural programming language.

Option 2 is incorrect. Although you typically write OCI programs in either C or C++, you can use many programming languages to write them.

Option 3 is correct. This management can operate both within a two-tier client/server environment and within a multitier environment.

Option 4 is incorrect. Oracle precompilers such as Pro*C and Pro*COBOL offer interfaces to precompile programs if precompilation is required.