Java, OCIs, and APIs

Learning objective

After completing this topic, you should be able to identify the characteristics of some common database interfaces.

1. Java and Oracle

When Sun Microsystems introduced the Java programming language in 1995, it had a radical impact on the way online applications were written.

Java is an object-oriented language. You can run Java code on any computer that has a Java interpreter called the Java Virtual Machine (JVM) installed.

Java code is compiled and stored as bytecode - a kind of hybrid between source code and machine code. To facilitate interoperability between systems, the bytecode is platform-independent.
The JVM translates the bytecode into instructions target platforms can read. The JVM is included in most web browsers. The Oracle instance also includes a built-in JVM.

Java is portable and available on many platforms, and so is suited to the web.

You can use Java code to enrich content with graphics, animations, and sound effects.

Java is especially suited to coding interactive applications - for example online e-learning lessons such as this one.

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.

This PL/SQL code sample shows a function called balance that returns an account balance.

FUNCTION balance (acct_id NUMBER) RETURN NUMBER IS
acct_bal NUMBER;
BEGIN
SELECT bal INTO acct_bal FROM accts
WHERE acct_no = acct_id;
RETURN acct_bal;
END;

And this Java code invokes the balance function.

CallableStatement
cstmt = conn.prepareCall("{? = CALL balance(?)}");
cstmt.registerOutParameter(1, Types.FLOAT); cstmt.setInt(2, acctNo); 
cstmt.executeUpdate();
float acctBal = cstmt.getFloat(1);

Oracle supports the following standards-based interfaces that enable Java developers to use the Oracle database as a back end to their applications:

• JDBC
• SQLJ

JDBC

The Java Database Connectivity (JDBC) interface is the standard application programming interface (API) that enables programs written in Java to access relational databases.

You use JDBC

• when the SQL is dynamic
• when you want explicit control over how applications interact with the database

SQLJ

Structured Query Language in Java (SQLJ) resembles Oracle precompilers in terms of its process. First, Java source files and extra profile files are generated whenever calls to the SQLJ runtime are made. Then the Java source code is compiled. Finally the application executes with the SQLJ runtime library.

You typically use SQLJ when static SQL statements are embedded within a Java program.

As a rule of thumb, you can use SQLJ when the SQL statements are static and JDBC when the statements are dynamic.

You can use a combination of both interfaces in the same program when some SQL is static and some is not.

The Java Virtual Machine located in every Oracle database is called the Oracle JVM (formerly called JServer). It runs in the same process space as the Oracle database.

The Oracle JVM loads compiled versions of Java programs with a .class extension, or source code into the database. It accesses database buffers directly and so retrieves information more quickly.

Because the Oracle JVM belongs to the Oracle server, it avails of the same authentication and security measures as its host. It runs within a database session and so doesn't have to connect directly to the Oracle server.

Question

Identify the ways in which Java can be used in an Oracle database.

Options:

1. It can be used to invoke PL/SQL stored procedures
   
2. It can be invoked from a PL/SQL block
3. You can combine the JDBC and SQLJ interfaces in the same program
4. You typically use the JDBC API when the SQL is static

Answer

You can invoke PL/SQL stored procedures from Java code and you can invoke Java code from a PL/SQL block. You can also combine the JDBC and SQLJ interfaces in the same program.

Option 1 is correct. The Java Database Connectivity (JDBC) API allows you to invoke stored functions and procedures coded in PL/SQL from your Java code.

Option 2 is correct. You can invoke Java code from a PL/SQL block, a subprogram, or a package.

Option 3 is correct. You can use a combination of the JDBC and SQLJ interfaces in the same program when some SQL is static and some is not.

Option 4 is incorrect. The JDBC interface is more suited to dynamic SQL and fine-tuned control over database interaction.

2. The OCI

The Oracle Call Interface (OCI) is a complex and powerful low-level API that makes all database features accessible to application developers.

It allows programming languages such as C or C++ to interact with one or more Oracle Database servers.

The OCI allows you to combine procedural programming languages such as C with nonprocedural programming languages such as SQL:

• procedural programming languages
• nonprocedural programming languages

procedural programming languages

Most programming languages are procedural. A statement in a procedural language depends on the code that goes before it and the code that comes after it. It may be wrapped within a programming construct such as an IF condition or a loop.

nonprocedural programming languages

A program written in a nonprocedural language specifies the data on which an operation is performed, but does not indicate the precise operation to be performed or the manner in which it executes.

OCI supports all the facilities provided by Oracle Database 10g servers for SQL data definition, data manipulation, querying, and transaction control.

As well as providing improved application performance and scalability - by using system memory and network connections efficiently - OCI also supports n-tier authentication.

In addition, OCI allows dynamic session and transaction management that can operate both within a two-tier client/server environment and within a multitier environment.

OCI programs are not precompiled as is the case with Oracle precompilers such as Pro*COBOL and Pro*C.

Although you can use many programming languages to write OCI programs, they're typically written in C or C++.

Any capabilities and features that are new to the database are first made available to application developers over the OCI.

Other APIs are subsequently layered over OCI.

Question

Identify the characteristics of the OCI.

Options:

1. It provides dynamic session and transaction management for a two-tier client/server environment only
2. Programs coded in both procedural and non procedural languages can use it
3. Programs using it are not precompiled
4. Programs using it must be written in COBOL

Answer

Programs coded in both procedural and nonprocedural languages can use the OCI. And programs using the OCI are not precompiled.

Option 1 is incorrect. OCI provides dynamic session and transaction management that can operate both within a two-tier client/server environment and within a multitier environment.

Option 2 is correct. The OCI allows you to combine procedural programming languages, such as C, with nonprocedural programming languages, such as SQL.

Option 3 is correct. Oracle precompilers such as Pro*C and Pro*COBOL offer interfaces to precompile programs.

Option 4 is incorrect. You can use many programming languages to write OCI programs, although typically they are written in C or C++.

3. Other APIs

In addition to the OCI, Oracle Database 10g supports the following APIs:

• Java Database Connectivity (JDBC)
• Pro*C/C++ and Pro*COBOL
• Oracle C++ Call Interface (OCCI)
• Open Database Connectivity (ODBC)
  
• Oracle Data Provider for .NET (ODP.NET)
• Oracle Objects for OLE (OO4O)
  

Java Database Connectivity (JDBC)

The JDBC interface is the standard API enabling programs written in Java to access relational databases. It can be used to

• load Java classes into an Oracle database and publish them as stored procedures
• enable Oracle database access for middle tier components such as servlets and Enterprise JavaBeans (EJBs)
• call stored procedures within Java programs, if those procedures are written in Java or PL/SQL

Pro*C/C++ and Pro*COBOL

Since Oracle began making databases, it has been possible for programmers to embed SQL within programs written in third-generation languages such as C and COBOL.

Pro*C/C++ and Pro*COBOL are precompilers. They translate the embedded SQL into library calls for database access that the Oracle runtime can understand.

Oracle C++ Call Interface (OCCI)

The OCCI interface provides object-oriented access to an Oracle database. As an interface for C++ programmers, it is more user-friendly than OCI and more intuitive than Pro*C/C++.

Although OCCI loses some of the functionality of the more low-level OCI, it manages to expose most of its advanced features.

Open Database Connectivity (ODBC)

The ODBC interface enables one application to access multiple data sources across different platforms.

Applications use database drivers to link to their elected data source.

Oracle's ODBC driver permits Windows applications to access Oracle databases using the ODBC interface in combination with native OCI client libraries.

Oracle Data Provider for .NET (ODP.NET)

The ODP.NET interface allows quick and regular access to both the data and the database features contained within all .NET applications.

It can also use and inherit from the interface and classes in the Microsoft .NET Framework Class Library.

Oracle Objects for OLE (OO4O)

The OO4O interface provides easy access to Oracle database data using programming or scripting languages supportive of ActiveX and Microsoft COM Automation technologies.

Supported languages include Visual Basic, Visual C++, Visual Basic for Applications (VBA), and IIS Active Server Pages (VBScript and JavaScript).

Question

Match each API with its characteristics.

Options:

1. JDBC
2. Pro*C
3. OCCI
4. ODBC
5. OO4O

Targets:

1. Enables programmers to embed SQL within C programs
2. Enables Windows applications to access Oracle databases
3. Is used by C++ programmers to gain object-oriented access to an Oracle database
4. Makes Oracle data accessible to languages that support ActiveX or Microsoft COM Automation
   
5. Enables programs written in Java to access relational databases

Answer

Pro*C enables programmers to embed SQL within C programs, and ODBC enables Windows applications to access Oracle databases. OCCI is an interface for C++ programmers that provides object-oriented access to an Oracle database. OO4O makes Oracle data accessible to languages that support ActiveX or Microsoft COM Automation. And JDBC enables programs written in Java to access relational databases.

The JDBC interface can be used to load Java classes into an Oracle database and publish them as stored procedures, to enable Oracle database access for middle tier components, and to call stored procedures within Java programs.

Pro*C is a precompiler that translates SQL code embedded in a C program into library calls for database access that the Oracle runtime library can understand.

OCCI is an intuitive and user-friendly interface which provides object-oriented access to an Oracle database.

With ODBC, applications use database drivers to link to their elected data source. Oracle's ODBC driver enables Windows applications to access Oracle databases using the ODBC interface in combination with native OCI client libraries.

Supported languages include Visual Basic, Visual C++, Visual Basic for Applications (VBA), and IIS Active Server Pages (VBScript and JavaScript).

Summary

Java is a portable, multiplatform, object-oriented language. Its code runs on computers with the Java Virtual Machine (JVM) installed, and is compiled and stored as bytecode. You can use Java code to enrich content with graphics, animations, and sound effects. You can use the JDBC or SQLJ interfaces to make Oracle a back end to Java applications.

The Oracle Call Interface (OCI) is a complex and powerful low-level API that makes all database features accessible to applications. It allows you to combine procedural programming languages such as C with nonprocedural programming languages such as SQL. OCI programs are not precompiled. Although you can use many programming languages to write OCI programs, they're typically written in C or C++.

In addition to the OCI, Oracle Database 10g supports the following APIs: Java Database Connectivity (JDBC), Pro*C/C++ and Pro*COBOL, Oracle C++ Call Interface (OCCI), Open Database Connectivity (ODBC), Oracle Data Provider for .NET (ODP.NET), and Oracle Objects for OLE (OO4O).