Using Oracle 10g with PL/SQL, Java, and OCI
After completing this topic, you should be able to identify how Oracle works with common database interfaces.
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?
- It allows you to declare variables, constants, and cursors
- It allows you to use programming control structures, such as WHILE iterations and IF conditions
- It provides additional nonprocedural capabilities
- It provides extra error-handling capabilities
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.
- Define a variable
- Perform a SQL query
- Handle an error
- Include a procedural statement
- Define a constant
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.
SQL and PL/SQL commands.
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?
- You can define your own object type
- You can make a block execute when a change happens in a database
- You can write a function that performs calculations and returns a bank balance
- You must name all PL/SQL blocks
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?
- When working with Oracle data, you can invoke Java code from a PL/SQL block
- You can use the SQLJ API when making the Oracle database a back end to Java applications
- You must install the Oracle Java Virtual Machine in every Oracle database
- The Oracle Java Virtual Machine runs in a different process space from the Oracle database
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?
- It allows you to combine procedural and nonprocedural programming languages
- Its programs must be written in C
- It provides dynamic session and transaction management
- Programs using it must be precompiled
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.