| Oracle Application Server Adapter for Oracle Applications User's Guide 10g (10.1.3.1.0) Part Number B28351-03 |
|
|
View PDF |
| Oracle Application Server Adapter for Oracle Applications User's Guide 10g (10.1.3.1.0) Part Number B28351-03 |
|
|
View PDF |
| Oracle Application Server Adapter for Oracle Applications User's Guide 10g (10.1.3.1.0) Part Number B28351-03 |
 Contents |
 Previous |
 Next |
This chapter covers the following topics:
OracleAS Adapter for Oracle Applications uses PL/SQL application programming interfaces (APIs) to insert and update data in Oracle Applications. APIs are stored procedures that enable you to insert and update data in Oracle Applications. Additionally, you can use PL/SQL APIs to retrieve data. For example, by using PL/SQL APIs, you can insert a customer record in Oracle Applications.
Note: For more information about PL/SQL procedure limitations, refer to Oracle Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide.
This section describes how to configure the OracleAS Adapter for Oracle Applications to use PL/SQL APIs. It describes the tasks required to configure OracleAS Adapter for Oracle Applications using the Adapter Configuration Wizard in Oracle JDeveloper.
Prerequisites to Configure PL/SQL APIs
OracleAS adapter for Oracle Applications is deployed using the BPEL Process Manager (PM) in Oracle JDeveloper. The BPEL PM creates the WSDL interfaces for the API.
You need to populate certain variables in the BPEL PM in order to provide context information for Oracle Applications. The context information required for an API transaction includes the username and responsibility of an Oracle Applications user that has sufficient privileges (based on the applications context of Organization ID, Username and Responsibility) to run the program. The default value passed for the username is SYSADMIN. The default value passed for responsibility is SYSTEM ADMINISTRATOR.
You can change the default values specified in the generated WSDL for the username and responsibility. This is a static way of changing the context information. These values would apply to all invocations of the deployed business process. However, if you need to provide different context information for different invocations of the business process, then you can dynamically populate the header variable with values for username and responsibility. The context information can be specified by configuring an Assign activity before the Invoke activity in the BPEL PM.
Following is a list of the procedures required to accomplish the design-time tasks.
To create a new BPEL project
Open BPEL Designer.
From the File menu, select New. The New Gallery dialog box appears.
Select All Items from the Filter By list. This displays a list of available categories.
Expand the General node, and then select Projects.
Select BPEL Process Project from the Items list.
Creating a New BPEL Process Project
Click OK. The BPEL Process Project dialog box appears.
In the BPEL Process Name field, enter a descriptive name. For example, InsertShipNotice.
From the Template list, select Empty BPEL Process. Keep the default selection for Use Default in the Project Content section.
Specifying a Name for the New BPEL Process Project
Click OK. An empty BPEL process, with the necessary source files including bpel.xml, InsertShipNotice.xml, and InsertShipNotice.wsdl is created.
New BPEL Process
The next task is to add a partner link to the BPEL process. This section describes how to create an OracleAS adapter for the application service by adding a partner link to your BPEL process. A BPEL partner link defines the link name, type, and the role of the BPEL process that interacts with the partner service.
To add a partner link
Drag and drop PartnerLink into the border area of the process diagram. The Create Partner Link dialog box appears.
Click Define Adapter Service in the WSDL Settings section. The Adapter Configuration Wizard appears.
Click Next. The Adapter Type dialog box appears.
Select Oracle Applications to specify the adapter you want to configure.
Selecting OracleAS Adapter for Oracle Applications
Click Next. The Service Name dialog box appears.
Enter the following information:
In the Service Name field, enter a service name.
In the Description field, enter a description for the service. This is an optional field. The Service Name dialog box appears.
Specifying the Service Name and Description
Click Next. The Service Connection dialog box appears.
Creating a New Database Connection
Click New to define a database connection. The Create Database Connection Wizard appears. Alternatively, you can select an existing database connection from the Connection list.
Note: You need to connect to the database where Oracle Applications is running.
Click Next. The Type dialog box appears.
Enter the following information in the Type dialog box:
In the Connection Name field, specify a unique name for the database connection.
From the Connection Type list, select the type of connection for your database.
Specifying a Name and Type for the Database Connection
Click Next. The Authentication dialog box appears.
Enter the following information:
In the UserName field, specify a unique name for the database connection.
In the Password field, specify a password for the database connection.
Click Next. The Connection dialog box appears.
Enter the following information:
From the Driver list, select Thin.
In the Host Name field, specify the host name for the database connection.
In the JDBC Port field, specify the port number for the database connection.
In the SID field, specify the unique SID value for the database connection.
Specifying Connection Details for the New Database
Click Next. The Test dialog box appears.
Click Test Connection to determine whether the specified information establishes a connection with the database.
Click Next. The Service Connection dialog box appears, providing a summary of your database connection.
The JNDI (Java Naming and Directory Interface) name corresponding to the database connection appears automatically in the Database Server JNDI Name field. Alternatively, you can specify a JNDI name.
Note: When you specify a JNDI name, the deployment descriptor of the Oracle Applications adapter must associate this JNDI name with configuration properties required by the adapter to access the database.
The JNDI name acts as a placeholder for the connection used when your service is deployed to the BPEL server. This enables you to use different databases for development and later for production.
Note: For more information about JNDI concepts, refer to Oracle Application Server Adapter Concepts.
Click Finish to complete the process of creating a new database connection.
Once you have completed creating a new connection for the service, you can add a PL/SQL API by browsing through the list of APIs available in Oracle Applications.
Click Next. The Application Interface dialog box appears.
Note: If you connect to a pre-11.5.10 Oracle Applications instance, you must select the interface type in the Adapter Configuration Wizard. Select Tables/Views/APIs/Concurrent Programs to proceed.
Adding the Database Objects
Click Get Object to open the Oracle Applications Module Browser.
Specifying the PL/SQL API
Oracle Applications Module Browser includes the various product families that are available in Oracle Applications. For example, the Marketing Suite or the Order Management Suite are product families in Oracle Applications. The product families contain the individual products. For example, the Order Management Suite contains the Shipping Execution Common product. The product contains the business entities associated with the product. For example, the Shipping Execution Common product contains the Delivery entity.
Business entities contain the various application modules that are exposed for integration. These modules are grouped according to the interface they provide. PL/SQL APIs can be found under the PLSQL category.
Note: In previous releases, the Module Browser displayed all of the PL/SQL APIs that were available in a package. Starting in release 11.5.10, the Module Browser displays only the public PL/SQL APIs that are exposed to the Oracle Integration Repository.
You can specify whether customized APIs within an existing (Integration Repository) PL/SQL package are visible in the Module Browser tree. Right-click the name of a PL/SQL package, and click Yes to display all of its APIs (the default), or click No to hide the customized APIs.
Note: This selection applies separately to each PL/SQL package, and remains in effect only for the duration of the current JDeveloper session.
Select the required PL/SQL API. The signature of that given API appears.
Click OK. You can select only one API for each adapter service.
Note: Use the Search option to quickly find the required objects. Enter the required database object name in the Object Name field and select APIs. And then, click Search to retrieve the required database objects. When searching for a PL/SQL API, enter the package name, and not the procedure name. In contrast, when using the DB Adapter Wizard, the user must enter the name of the PL/SQL API while searching.
Adding the PL/SQL API
Click Next, then click Finish to complete the process of configuring OracleAS Adapter for Oracle Applications. The wizard generates the WSDL file corresponding to the XML schema. This WSDL file is now available for the partner link.
Click OK. The partner link is created with the required WSDL settings.
After adding and configuring the partner link, the next task is to configure the BPEL process.
The Adapter Configuration wizard generates a wrapper API when a PL/SQL API has arguments of data types, such as PL/SQL Boolean, PL/SQL Table, or PL/SQL Record. For generating the wrapper API, Oracle JPublisher is automatically invoked in the background. When a wrapper API is created, besides the WSDL and XSD files, two SQL files are created: one for creating the wrapper API, and necessary datatypes and another for deleting it. The two SQL files are saved in the same directory where the WSDL and XSD files are stored, and are available in the Project view.
Following is a sample code of a PL/SQL API that would require a wrapper to be generated:
package pkg is
type rec is record (...);
type tbl is table of .. index by ..;
procedure proc(r rec, t tbl, b boolean);
end;
If the preceding PL/SQL API is selected in the wizard, then a wrapper API will be created, and loaded into the database. This wrapper API will be used instead of the originally selected PL/SQL API. For this reason, the content of the WSDL and XSD files represent the wrapper procedure, not the procedure originally selected.
The following are the types that will be created for the wrapper API:
Object type for PL/SQL RECORD
Nested table of the given type for PL/SQL TABLE. For example, the nested table of NUMBER.
INTEGER substituted for PL/SQL BOOLEAN
The generated SQL file that creates the wrapper API also creates the required schema objects. The types of the wrapper API's parameters will be those of the new schema object types. The wrapper package will contain conversion APIs to convert between the base PL/SQL type and the new schema object types.
Note: The SQLJUTL package contains the BOOL2INT and INT2BOOL conversion functions used for PL/SQL BOOLEAN arguments whose data types have been changed to INTEGER.
The wrapper API is created in a package. This package is named XX_BPEL_servicename.servicename is the name of the service that you entered in step 6 of Adding a Partner Link. If this package already exists, then the wizard prompts for a different package name, or to select a checkbox, to overwrite the existing package. Overwriting an existing package causes all PL/SQL APIs in the specified package to be lost. When the wizard creates a package for the wrapper, only one API, that is, the wrapper API, is contained in it.
Note: Despite specifying to overwrite an existing package, if the wrapper API already exists in the specified package, the wizard will not re-create the wrapper API, as it would take some time. This means no SQL files will be created, Oracle JPublisher will not be run, and the WSDL and XSD files will be for the existing wrapper API. The Finish page of the wizard will indicate that these actions will take place, but it is possible that they will not, depending on whether the wrapper already exists.
Entering a Package Name for the Wrapper API
Note: The package name for the wrapper has a limit of 30 characters, and the wrapper API name has a limit of 29 characters. Thus, if the package name or the wrapper API name is longer than the maximum limit, it will be truncated accordingly.
The name of the wrapper API depends on whether the PL/SQL API that was originally selected is in a package or not. If the original PL/SQL API is a root-level API, that is, it does not belong in a package, then the name of the wrapper API will be, TOPLEVEL$original_api_name. If the originally selected PL/SQL API is in a package, then the name of the wrapper API will be original_package_name$original_api_name.
Wrapper APIs follow the naming convention of Oracle JPublisher. For example, if the original PL/SQL API was called CREATE_EMPLOYEE and was a root-level API, then the wrapper API would be named TOPLEVEL$CREATE_EMPLOYEE. If the original PL/SQL API is in a package called EMPLOYEE, then the wrapper API would be named EMPLOYEE$CREATE_EMPLOYEE.
The Finish page of the wizard is different when a wrapper API needs to be created. The Finish page informs you that a wrapper API is needed, and in addition, lists the name of the wrapper package, wrapper API, and the SQL files that will be created.
Note: When a wrapper API needs to be created, it may take a while before the wizard completes. However, the processing time for subsequent PL/SQL APIs in the same package would be much shorter.
The Finish Page
Note: The REF CURSOR type is not supported out of the box. However, for detailed steps to generate an adapter service for a PL/SQL API which takes REF CURSOR type, see the section "Support for REF CURSOR" in Oracle BPEL Process Manager Developer's Guide on OTN.
Overloaded APIs are not supported in release 11.5.10.
You can declare parameters of a stored procedure with a DEFAULT clause, so that when you invoke the procedure without that parameter, BPEL Process Manager will supply a default value for that parameter. For example:
PROCEDURE addEmployee (name VARCHAR2, country VARCHAR2 DEFAULT ‘US')
This procedure can be invoked in the following two ways:
addEmployee (‘John Smith') // country => ‘US'
addEmployee (‘John Smith', ‘France') // country => ‘France'
Omitting Parameters With a DEFAULT Clause
You can omit elements for parameters with default values in the instance XML. The procedure will be invoked without these parameters, allowing their default values to be used, as shown in the following example of input and runtime invocation.
Input
<db:InputParameters xmlns:db="…">
<name>John Smith</name>
</db:InputParameters>
Runtime Invocation
BEGIN addEmployee (name=>?); END; // country => 'US'
If the input includes a value for the defaulted parameter, the value in the input will be used, rather than the default, as follows:
Input
<db:InputParameters xmlns:db="…">
<name>John Smith</name>
<country>France</country>
</db:InputParameters>
Runtime Invocation
BEGIN addEmployee (name=>?, country=>?); END; // country => 'France'
Omitting Parameters Without a DEFAULT Clause
The element in the XSD for parameters with a default clause is annotated with a special tag to indicate that the parameter has a default clause, as shown in the following example.
<element name="country" … db:default="true" …/>
This new functionality allows elements for parameters without a default clause also to be omitted in the instance XML. In these cases, the parameter is still included in the invocation of the stored procedure. A value of NULL is bound by default. Following is an example of a declaration where neither parameter has a DEFAULT clause:
PROCEDURE addEmployee (name VARCHAR2, country VARCHAR2)
In BPEL Process Manager release 10.1.2, elements for both parameters were required in the instance XML. If an element was omitted, it was presumed to have a DEFAULT clause, so the parameter was not included in the invocation of the procedure. In this case, the missing parameter resulted in a PL/SQL error stating that an incorrect number of arguments was passed to the procedure.
In the current BPEL Process Manager release, the missing parameter will be included in the invocation of the procedure. A NULL value will be bound, as shown in the following example:
Input
<db:InputParameters xmlns:db="…">
<name>John Smith</name>
</db:InputParameters>
Runtime Invocation
BEGIN addEmployee (name =>?, country=>?); END; // country => NULL
Even though the element for country was not provided in the instance XML, it still appears in the call to the procedure. In this case, country will be NULL.
DEFAULT Clause Handling in Wrapper Procedures
If a procedure contains a special type requiring a wrapper to be generated, the default clauses on any of the original parameters will not be carried over to the wrapper, as shown in the following example:
PROCEDURE needsWrapper(isTrue BOOLEAN, value NUMBER DEFAULT 0)
Assuming that the procedure in the preceding example was defined at the top level, outside of a package, the generated wrapper will appear, as shown in the following example:
TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER, value NUMBER)
In the preceding example, the BOOLEAN type has been replaced by INTEGER. The default clause on the value parameter is missing. In the current release, parameters of generated wrapper procedures will never have a default clause, even if these parameters did in the original procedure. If the element is missing in the instance XML, instead of defaulting to 0, then the value of the parameter will be NULL, as shown in the following example:
Input
<db:InputParameters xmlns:db="…">
<isTrue>1</isTrue>
</db:InputParameters>
Runtime Invocation
BEGIN TOPLEVEL$NEEDSWRAPPER (isTrue =>?, value =>?); END; // value => NULL
To fix this, you can edit the generated SQL file, restoring the default clauses. You should then, run the SQL file to reload the wrapper definitions into the database schema. In addition, you should modify the generated XSD.
Following are the steps to fix the default clause with the wrapper generated for needsWrapper():
Change the signature in the following manner in the generated wrapper SQL file, from:
TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER, value NUMBER)
To:
TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER, value NUMBER DEFAULT 0)
Reload the modified wrapper SQL file mentioned in the preceding example into the appropriate database schema. For BOOLEAN parameters with DEFAULT clause, you need to map as follows:
DEFAULT TRUE(base) to DEFAULT 1 (wrapper) DEFAULT FALSE (base) to DEFAULT 0 (wrapper)
For example, if the base stored procedure is PROCEDURE needsWrapper(isTrue BOOLEAN TRUE, value NUMBER DEFAULT 0), then the generated wrapper would be TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER, value NUMBER). You should manually fix the store procedure to be TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER DEFAULT 1, value NUMBER DEFAULT 0)
If a parameter has a default clause, then its corresponding element in the XSD must have an extra attribute, db:default="true". For example, if a parameter has a default clause TOPLEVEL$NEEDSWRAPPER(isTrue INTEGER DEFAULT 1, value NUMBER DEFAULT 0), then the elements in the XSD for isTrue and value need to have the following new attributes:
<element name="ISTRUE" ... db:default="true" .../> <element name="VALUE" ... db:default="true" .../>
To configure the Invoke activity
Drag Invoke from the Component palette and drop it at the location where you want to insert the invoke activity in your BPEL process.
Dragging the Invoke Activity
Double-click Invoke in the process map to open the Invoke dialog box. The General tab is selected by default.
Configuring the Invoke Activity
In the Partner Link box, select the partner link to invoke. This is the partner link that you configured in the previous section. The Operation is automatically selected.
Click the Create icon next to the Input Variable field. Enter a descriptive name for the variable in the Create Variable dialog box that appears. You can also accept the default name. Click OK.
Creating a Variable
Click the Create icon next to the Output Variable field. Enter a descriptive name for the variable in the Create Variable dialog box that appears. You can also accept the default name. Click OK.
In the Invoke dialog box, click Apply, and then click OK. The invoke activity is configured.
The Transform activity can be used to configure the parameters for the input and output variables. The Transform activity can also be used if variable values need to be transformed before updating them in Oracle Applications.
To configure the Transform activity
Drag and drop Transform into the process map window. The Transform activity should be placed in between Receive and Invoke.
Adding the Transform Activity
Double-click Transform in the process map to open the Transform dialog box. The Transformation tab is selected by default.
Configuring the Transform Activity
Select the Source Variable and Target Variable from the respective boxes. Elements are mapped from the Source Variable to the Target Variable.
Select the Source Part of the variable from which to map elements. For example, the source part may be a payload schema consisting of a purchase order request.
Select the Target Part of the variable to which to map elements. For example, the target part may be a payload schema consisting of a purchase order acknowledgment.
Click the Create icon next to the Mapper File field to create a new transformation mapping file. Mapper File specifies the file in which you create the mappings using the XSLT Mapper Transformation tool.
The transformation mapping file appears. The Design view appears by default.
You can define the parameter values in the Design view. Drag a string function to the Design area. Connect the function to the appropriate parameter for which you want to define a value.
Note: You can use an input parameter value from the source variable, transform it using a string function, and use it as the input parameter value for the target variable.
Double-click the icon for the function. The Edit Function dialog box appears.
Supplying the Function Parameters
Repeat steps 8 and 9 for all the parameters that you need to supply.
After designing the BPEL process, the next step is to deploy, run and monitor it.
You must deploy the BPEL process before you can run it. The BPEL process is first compiled, and then deployed to the BPEL server.
To deploy the BPEL process
Select the BPEL project in the Applications window.
Right-click the project name, and then select Deploy from the menu that appears.
Select Local BPEL Server followed by Deploy to Default Domain, if you are deploying the process on the local BPEL server.
Deploying the BPEL Process
The Password Prompt dialog box appears. Enter the password for the default domain in the Domain Password field, and then click OK.
Specifying the Domain Password
The BPEL process is compiled and deployed. You can check the progress in the Messages window.
Messages Window
Once the BPEL process is deployed, it can be seen in the BPEL console. You can manage and monitor the process from the BPEL console. You can also test the process and the integration interface by manually initiating the process.
To test the BPEL process
To open the BPEL console, click Start, and then choose Programs. In the Programs menu, select Oracle - ORACLE_HOME, Oracle BPEL Process Manager 10.1.3, and then select BPEL Console.
The BPEL console login page appears.
Select Default in the Domain box. Enter the password for the default domain in the Password field, and then click Login.
The Oracle BPEL console appears.
A list of deployed processes is shown under Deployed BPEL Processes.
Deployed BPEL Processes
Click the BPEL process that you want to initiate. The Initiate page appears. Enter the input string required by the process.
Click Post XML Message to initiate the process.
The BPEL process is now initiated. You can check the process flow by clicking the Visual Flow icon.
BPEL Console Initiate Page
Note: To confirm that the records have been written into the Oracle Applications, you can write SQL SELECT statements and fetch the results showing the latest records inserted into Oracle Applications. Alternatively, you can go to the specific module in Oracle Applications and verify the appropriate changes in the records.
If you experience problems with your PL/SQL API integration, you can take the following troubleshooting steps:
If you designed your PL/SQL API integration in one instance of your Oracle Applications environment, and plan to run it in another instance, be sure to rerun the SQL scripts in the second instance to create the necessary PL/SQL wrappers.
Ensure that the JNDI location in oc4j-ra.xml is properly configured.
Use encrypt.bat to encrypt the passwords in oc4j-ra.xml if needed.
If you still experience problems with your integration, you can enable debugging.
You can enable debugging for PL/SQL APIs using the BPEL Process Manager.
To enable debugging:
Log into your BPEL Process Manager domain.
Select yourdomain.collaxa.cube.ws
Select Debug.
Debugging information is output to the log file for your domain. To examine the log file in the BPEL Process Manager, navigate to Home > BPEL Domains > yourdomain > Logs. The log file is yourdomain.log.
