Stored Procedure Generator

You can generate stored procedures based on the dynamic SQL that is associated with descriptors and mappings. After you generate the stored procedures, attach them to the mappings and descriptors of the domain object. At that point, access to the database is accomplished through stored procedures, rather than through SQL.

Note:

Implement this feature only if your database requires access by stored procedures. Doing this does not enhance performance and has the same limitations that are associated with stored procedures.

Generating Stored Procedures

You can generate stored procedures for all descriptors and most relationship mappings with the exception of many-to-many mappings. Many-to-many mappings are not supported by the stored procedure generator, and stored procedures for Read operations for the Oracle platform.

Sequencing and Stored Procedures

illustrates how to specify a stored procedure for sequence updates. In this example, the stored procedure is named UPDATE_SEQ and it takes one argument: the name of the sequence to update (SEQ_NAME). The stored procedure increments the sequence value associated with the sequence named SEQ_NAME.

Example A-2 Using a Stored Procedure for Sequence Updates

DataModifyQuery seqUpdateQuery = new DataModifyQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("UPDATE_SEQ");
seqUpdateQuery.addArgument("SEQ_NAME");
seqUpdateQuery.setCall(spCall);
login.((QuerySequence)getDefaultSequence()).setUpdateQuery(seqUpdateQuery)

illustrates how to specify a stored procedure for sequence selects. In this example, the stored procedure is named SELECT_SEQ and it takes one argument: the name of the sequence to select from (SEQ_NAME). The stored procedure reads one data value: the current sequence value associated with the sequence name SEQ_NAME.

Example A-3 Using a Stored Procedure for Sequence Selects

ValueReadQuery seqReadQuery = new ValueReadQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("SELECT_SEQ");
seqReadQuery.addArgument("SEQ_NAME");
seqReadQuery.setCall(spCall);
login.((QuerySequence)getDefaultSequence()).setSelectQuery(seqReadQuery)

For more information about creating an amendment class, see "Customizing OracleAS TopLink Descriptors with Amendment Methods".

Attaching the Stored Procedures to the Descriptors

After you create the stored procedures on the database, and after you create the amendment file, enable them on the descriptors:

Before logging in, call a method on the generated amendment class:

Session session = project.createDatabaseSesssion();
com.demo.Tester.amendDescriptors(project);

For more information about creating an amendment class, see "Customizing OracleAS TopLink Descriptors with Amendment Methods".