Skip Headers

Oracle TopLink Developer's Guide 10g Release 3 (10.1.3) B13593-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# Using Oracle Database Features

If you are using Oracle Database, you can take advantage of TopLink support for the following Oracle Database features:

- Oracle Hints

- Hierarchical Queries

- Stored Procedure Cursor Output Parameters

## Oracle Hints

Oracle Hints is an Oracle Database feature through which a developer makes decisions usually reserved for the optimizer. Developers use hints to specify things such as join order for a join statement, or the optimization approach of an SQL call.

The TopLink query framework supports Oracle Hints with the following API:

    setHintString("/*[hints or comments]*/");

TopLink adds the hint to the SQL string as a comment immediately following a `SELECT`, `UPDATE`, `INSERT`, or `DELETE` statement.

To add hints to a read query:

1. Create a `ReadObjectQuery` or a `ReadAllQuery`

2. Set the selection criteria.

3. Add hints as needed.

For example, the following code uses the `FULL` hint (which explicitly chooses a full table scan for the specified table):

    // This line sets up the query
    ReadObjectQuery query = new ReadObjectQuery(Employee.class);
    query.setSelectionCritera(new ExpressionBuilder().get("id").equal(new Integer(1));
    // This line adds the hint
    query.setHintString("/*+ FULL */" );

This code generates the following SQL:

    SELECT /*+ FULL */ FROM EMPLOYEE WHERE ID=1

To add hints to `WRITE`, `INSERT`, `UPDATE`, and `DELETE`, create custom queries for these operations in the TopLink query framework, then specify hints as required. For more information, see the following:

- "Configuring Custom SQL Queries for Basic Persistence Operations"

- "Configuring Custom EIS Interactions for Basic Persistence Operations"

For more information about the available hints, see the Oracle Database documentation.

## Hierarchical Queries

Hierarchical Queries is an Oracle Database mechanism that lets you select database rows based on hierarchical order. For example, you can design a query that reads the row of a given employee, followed by the rows of people the employee manages, followed by their managed employees, and so on.

To create a hierarchical query, use the `setHierarchicalQueryClause` method. This method takes three parameters, as follows:

    setHierarchicalQueryClause(StartWith, ConnectBy, OrderSibling)

This expression requires all three parameters, as described in the subsequent text.

### StartWith Parameter

The `StartWith` parameter in the expression specifies the first object in the hierarchy. This parameter mirrors the Oracle Database `START WITH` clause.

To include a `StartWith` parameter, build an expression to specify the appropriate object, and pass it as a parameter in the `setHierarchicalQueryClause` method. If you do not specify the root object for the hierarchy, set this value to `null`.

### ConnectBy Parameter

The `ConnectBy` parameter specifies the relationship that creates the hierarchy. This parameter mirrors the Oracle Database `CONNECT BY` clause.

Build an expression to specify the `ConnectBy` parameter, and pass it as a parameter in the `setHierarchicalQueryClause` method. Because this parameter defines the nature of the hierarchy, it is required for the `setHierarchicalQueryClause` implementation.

### OrderSibling Parameter

The `OrderSibling` parameter in the expression specifies the order in which the query returns sibling objects in the hierarchy. This parameter mirrors the Oracle Database `ORDER SIBLINGS` clause.

To include an `OrderSibling` parameter, define a vector, and to include the order criteria, use the `addElement` method. Pass the vector as the third parameter in the `setHierarchicalQueryClause` method. If you do not specify an order, set this value to `null`.

Example 99-8 Hierarchical Query

    ReadAllQuery raq = new ReadAllQuery(Employee.class);
    // Specifies a START WITH expression
    Expression startExpr = expressionBuilder.get("id").equal(new Integer(1));
    // Specifies a CONNECT BY expression
    Expression connectBy = expressionBuilder.get("managedEmployees");
    // Specifies an ORDER SIBLINGS BY vector
    Vector order = new Vector();
    order.addElement(expressionBuilder.get("lastName"));
    order.addElement(expressionBuilder.get("firstName"));
    raq.setHierarchicalQueryClause(startExpr, connectBy, order);
    Vector employees = uow.executeQuery(raq);

This code generates the following SQL:

    SELECT * FROM EMPLOYEE START WITH ID=1 CONNECT BY PRIOR ID=MANAGER_ID ORDER SIBLINGS BY LAST_NAME, FIRST_NAME

## Stored Procedure Cursor Output Parameters

Oracle databases use output parameters rather than result sets to return data from stored procedures. Cursored output parameters let you retrieve the result set in a cursored stream rather than as a single result set. When you use the Oracle JDBC drivers, configure a `StoredProcedureCall` object to pass a cursor to TopLink as a standard result set.

Example 99-9 Stored Procedure with a Cursored Output Parameter

    StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("READ_ALL_EMPLOYEES");
    call.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
    ReadAllQuery query = new ReadAllQuery();
    query.setReferenceClass(Employee.class);
    query.setCall(call);
    Vector employees = (Vector) Session.executequery(Query);

For more information, see the following:

- "StoredProcedureCall"

- "Handling Cursor and Stream Query Results"