Building Oracle XML Applications

Working with Database Objects

All of the examples in this book use a database account named XMLBOOK that you’ll need to create to follow along and try out any code we discuss. In this section, we create an XMLBOOK user and explore JDeveloper 3.1’s features for working with database objects in the development environment.

Creating the XMLBOOK User to Run the Examples

To create the XMLBOOK user, connect to your Oracle database as a DBA account like SYS or SYSTEM using the SQL*Plus tool and issue the following commands:

SQL> CREATE USER xmlbook IDENTIFIED BY xmlbook;
User created.
SQL> GRANT connect, resource, query rewrite TO xmlbook;
Grant succeeded.

Then try to connect as the new XMLBOOK user and try a simple SELECT statement by doing the following:

SQL> CONNECT xmlbook/xmlbook
Connected
SQL> SELECT sysdate FROM dual;
SYSDATE
---------
23-APR-00

If this works, then you’re ready to go create a named connection for XMLBOOK inside the JDeveloper 3.1 environment.

Defining and Browsing Database Connections

JDeveloper 3.1 has a number of built-in features for working more easily with Oracle database objects. You can define any number of commonly used database connections that are then available for all workspaces and projects. After starting JDeveloper 3.1, you’ll notice a Connections folder at the top of the project navigator. Double-clicking on this folder or selecting Connections... from the right mouse button menu option on the folder brings up the JDeveloper Connection Manager dialog. From here you can create, edit, delete, import, and export connection definitions for databases you frequently work with during development. Click the New... button to define a new connection to work with our XMLBOOK user. The Connection dialog shown in Figure 4.8 appears.

Figure 4-8. Defining a new named connection

Enter the username of XMLBOOK and password of XMLBOOK and click on the Test Connection button to see if the JDBC connection information is correct for your database. The default values typically work for a local Oracle database running on the same machine as JDeveloper, but if you are working with a database on another machine, set the host, SID, and port values appropriately until clicking on Test Connection gives you a Success! message. Click OK on the Connection dialog and Done on the Connection Manager and we’re ready to go.

You can use JDeveloper’s built-in database browsing facilities by selecting your named xmlbook connection in the Connections folder of the project navigator and selecting the Open Viewer As → Database Browser option off the right mouse button menu as shown in Figure 4.9.

Figure 4-9. Browsing schema objects for a database connection

You can browse all schema objects to which you have access using the database browser’s tree control. For each kind of schema object you select, where appropriate, additional details appear in the right-hand pane of the browser. For example, selecting a:

Table or View

Displays information on its columns

PL/SQL Package, Package Body, Procedure, Function, or Trigger

Displays its PL/SQL source code

Sequence

Displays its last value, and minimum, maximum, and increment values

Object Type

Displays the source code of its type specification

By expanding the Deployed Java Classes folder in the browser, you can inspect the Java package hierarchy of all Java classes (to which you have access) that have been loaded into JServer. In Figure 4.10, we can see that the Oracle XML Parser for Java has been loaded into JServer by the presence of the oracle.xml.parser.v2 packages.

Figure 4-10. Browsing schema objects for a connection

Expanding a package node in the browser displays all classes in that package as well as subpackages. Clicking on a specific class shows its decompiled method signatures in the details panel.

In addition to browsing the contents of a named connection that you’ve defined, connections are also used by JDeveloper 3.1 for automatically:

Launching SQL*Plus on a connection

Just select the connection in the navigator and choose Invoke SQL*Plus... from the right mouse button menu.

Running any SQL script on a connection

Just select the SQL script in your project and select Invoke SQL*Plus... from the right mouse button menu. A submenu allows you to pick which connection you’d like to run the script under.

Deploying Java stored procedures to a connection

As we’ll see in detail in Chapter 6, JDeveloper’s deployment profiles feature automates the deployment and redeployment of Java code to the Oracle8i server using named connections.

Starting the JServer remote debug agent on a connection

Again, as we’ll see in Chapter 6, JDeveloper uses the named connections when you remotely debug Java code running inside Oracle8i ’s JServer VM.