Oracle® Application Server Integration InterConnect Adapter for DB Installation and User's Guide
10g Release 2 (10.1.2) B14076-02 |
|
Previous |
Next |
This chapter provides answers to the following frequently asked questions about the Database adapter:
What should I enter on the Database User Configuration screen during installation?
Is it possible to edit the database configuration settings created during installation?
If we manually deploy the PL/SQL code, where is the code, exported through iStudio, saved?
What is the Returned IN Args feature in iStudio and how do I use it?
How do I deploy PL/SQL code to use with the Database adapter?
Why do I get errors when trying to load PL/SQL code generated through iStudio?
What are the steps to prepare a Database adapter that publishes events?
What are the steps to prepare a Database adapter that invokes procedures?
What are the steps to prepare a Database adapter that subscribes to events?
What are the steps to prepare a Database adapter that implements procedures?
How can I deliver a message to a specific partition of the publishing adapter?
What SQL Data Types are Supported and Not Supported in iStudio?
My Database adapter is not starting. What could be the reason?
This information is used to find where the stored procedures generated through iStudio will be installed for application inbound messages. At run time, the Database adapter uses this information to call a user-specified stored procedure. This user can be an existing user or a user created specifically for OracleAS Integration InterConnect.
OracleAS Integration InterConnect uses Oracle Wallet Manager to maintain system passwords. When you install OracleAS Integration InterConnect, Oracle Wallet Manager is also installed and a password store is created. All passwords used by OracleAS Integration InterConnect components are stored in the password store. The password is stored in the Oracle Wallet in the following format:
ApplicationName/password
The ApplicationName
is the name of the application, which is extracted from the adapter.ini
file of the corresponding adapter. In the adapter.ini
file, the application
parameter specifies the ApplicationName
to which this adapter connects. The password for the application is also retrieved from the adapter.ini
file.
The number of entries is dependent on the type of adapter. For example, Database adapter needs two entries whereas AQ Adapter needs only one entry. The following table lists the entries that will be created for each adapter:
Adapter | Entry In Oracle Wallet |
---|---|
AQ | ApplicationName /aq_bridge_password
|
HTTP | ApplicationName /http.sender.password
|
HTTP | ApplicationName /sender.wallet_password
|
SMTP | ApplicationName /smtp.receiver.password
|
MQ | ApplicationName /mq.default.password
|
FTP | ApplicationName /file.sender.password
|
FTP | ApplicationName /file.receiver.password
|
DB | ApplicationName /db_bridge_schema1_password
|
DB | ApplicationName /db_bridge_schema1_writer_password
|
You can create, update, and delete passwords using the oraclewallet
command. When you run the command, it prompts you for the admin password.
You can use the following commands to manage your passwords:
List all passwords in the store
oraclewallet -listsecrets
Create a password
oraclewallet -createsecret passwordname
For example, to create a password for the hub schema:
oraclewallet -createsecret hub_password
View a password
oraclewallet -viewsecret passwordname
For example, to view the password for the hub schema:
oraclewallet -viewsecret hub_password
Update a password
oraclewallet -updatesecret passwordname
For example, to update the password for the hub schema:
oraclewallet -updatesecret hub_password
Delete a password
oraclewallet -deletesecret passwordname
For example, to delete the password for the hub schema:
oraclewallet -deletesecret hub_password
Edit the adapter.ini
file located in the ORACLE_HOME
/integration/interconnect/adapters/[AppType][Partition]
directory.
The PL/SQL code is saved in the ORACLE_HOME
/integration/interconnect/iStudio
directory. iStudio enables any extension to be specified, which is used to prefix the name of every SQL file, generated through iStudio. The following convention is used in naming the SQL files:
PrefixSpecifiedInIStudio
_ApplicationName
_BusinessObjectTYPES
.sqlPrefixSpecifiedInIStudio
_ApplicationName
_BusinessObject
.sql
Please refer to "Returned In Arguments".
The following steps describe how to deploy PL/SQL code for the Database adapter:
Click the Deploy tab in the iStudio window.
Right-click a Database application and select Deploy PL/SQL. The Deploy PL/SQL - Select Events/Procedures screen is displayed.
Select the application, event or procedure to deploy the corresponding PL/SQL.
Click Next. The Deploy PL/SQL - Database Information dialog box is displayed. This dialog box enables you to specify the database connection information for deploying the PL/SQL code.
Enter information in the following fields:
Database username: The database user name required for connecting to the database.
Database password: The password required for connecting to the database.
Database URL: The URL of the database required for connecting to the database. The URL should be in the form: host:port:SID
.
Click Next. The Deploy PL/SQL - Summary dialog box is displayed, which displays a summary of the database connectivity information entered in the previous dialog box.
The Deploy PL/SQL - Summary dialog box displays the following:
Database Information
Selected Events/Procedures
This dialog box displays a list of selected packages and the corresponding procedures contained in those packages that you have selected for deployment. The status of each package appears in parenthesis next to the package name.
Click Next. The Deploy PL/SQL - Status dialog box is displayed.
Click Deploy. The generated PL/SQL is deployed for the selected application, event or procedure.
If you do not want to export all stored procedures, for all applications, as this can take a while, select one or more applications. Only the stored procedures for those applications will be generated. You can also select messages based on the role; for example, if you select publish, then only publish messages will be generated. Or, you can select to export the stored procedures for specific messages by selecting those messages in the list.
The database does not allow arrays of arrays. Thus, the application view of database messages should not contain arrays of arrays. For example, the application view of an database message can contain an array of Customers, where each message contains one Address. However, it cannot contain an array of Customers, where each contains an array of Addresses.
A start executable that is not the OracleAS Integration InterConnect start
script must be running. This is dependent on what is in the PATH environment variable. Thus, run the start
script as follows:
Platform | Executable |
---|---|
UNIX | ./start
|
Windows | Use the Service Panel. |
Ensure you none of the PL/SQL reserved keywords are used in OracleAS Integration InterConnect messages. For example, for a Phone object contains the attributes areacode
and number
, a problem would occur because number
is a reserved keyword in PL/SQL.
Before a Database adapter can publish events, some stored procedures need to be generated in iStudio.
iStudio will create two SQL scripts for a publish message; one with stored procedures and one with types. The types
script name will end with TYPES.sql
. Using any user name, load the types
scripts and the stored procedure script into the database.
When an event occurs, there are several PL/SQL methods that must be called to publish the event message. All of the methods reside in the event business object
package which is created in the stored procedure SQL script. The first procedure that must be called is crMsg
_event name
_event owner
_event version
. It has two out arguments which are both of type number
: the message id and the root data type id.
Next, populate the message with the correct data. For each non-primitive attribute that the message contains, there is a function called cr
_data type name
_attribute name
. This function has one argument for each primitive attribute it contains and it takes the message id and the parent data type id. It returns a number, which is the data type id. When all data types have been created, a procedure must be called to publish the message. This procedure is named pub
_event name
_eventowner
_event version
. This procedure has three arguments: the message id, the source application name, and the destination application name. The destination application name is ignored, so pass in whatever is applicable.
For example, an event in the Customer
business object is called create
. Application A
publishes this event. The application view of this event contains an attribute called C
of type cust
. The cust
type contains a name
attribute, which is a String and a loc
attribute of type Location
. The Location
type contains a city
attribute, which is a String, and a state
attribute, which is also a String. The following piece of code would publish a create
event.
DECLARE moid NUMBER; aoid NUMBER; custid NUMBER; locid NUMBER; BEGIN Customer.crMsg_create_TEST_V1(moid, aoid); custid := Customer.cr_cust_c('Homer', moid, aoid); locid := Customer.cr_Location_loc('Redwood Shores', 'CA', moid, custid); Customer.pub_create_TEST_V1(moid, 'a', ''); END
This is very similar to publishing events. All of the steps are the same until the final procedure call. The name is inv_
proc name
_proc_owner
_proc version
and has three IN arguments: the message id, the source application name, and a timeout. The timeout is how many seconds to wait for a response. The event also has as many OUT arguments as the procedure defined in iStudio has.
Before a Database adapter can subscribe to events, some stored procedures need to be generated in iStudio.
iStudio will create two SQL scripts for a subscribe message: one with stored procedures and one with types. The types script name will end with TYPES.sql
. Under the same user name specified on the Database Configuration page during installation, load the types
scripts and the stored procedure script into the database. A pre-existing user can be specified, but if a user name that does not exist is entered, that user must be created manually.
The Database adapter will call the procedure sub_
event name
_event owner
_event version
in the package eventbusiness object
when a message is received. Add PL/SQL code in this method to perform whatever tasks are necessary when this kind of message is received. This code can be added in iStudio when creating the message, or modify the stored procedure SQL script before loading it into the database.
The steps are very similar to subscribing to events. However, the procedure that the Database adapter will call is imp_
procname
_proc owner
_proc version
. This procedure will have OUT arguments corresponding to the OUT arguments in the procedure defined in iStudio. In addition to writing PL/SQL code to perform the necessary tasks, the OUT arguments must be filled in with correct values. Write this code in iStudio when creating the message, or modify the stored procedure SQL script before loading it into the database. If the start
script is used to start the Database adapter, there is a way to determine whether the Database adapter was started properly. This can be viewed in the log.xml
file in the logs directory of the Database adapter.
The Database adapter polls the MESSAGEOBJECTTABLE table in the OAI schema to check for incoming messages and picks up any message that is meant for that adapter by checking the application name. If you have created partitions on an application and you want a particular partition to receive message from the MESSAGEOBJECTTABLE, then you need to specify the partition name along with the application name before putting the message into the MESSAGEOBJECTABLE.
When Database adapter is used to connect to an application, iStudio generates the required PL/SQL stored procedures for the events defined for the application in iStudio. These stored procedures enable an application to interface with OracleAS Integration InterConnect through the Oracle database. For example, Application DBAPP
contains two partitions PAR1
and PAR2
. When you deploy a database event Publish(purchase_order.CreatePO)
from iStudio, procedures crMsg_Create_PO_OAI_V1
and pub_Create_PO_OAI_V1
are generated automatically. To specify that the message should be delivered to the partition PAR2
, create the following trigger that uses these generated procedures. Specify the application name followed by the partition name as value of the srcAppName
parameter:
CREATE OR REPLACE TRIGGER DBAPP.NEW_PO BEFORE INSERT ON DBAPP.PO_TABLE FOR EACH ROW DECLARE msg_id Number; ao_id Number; BEGIN dbapp.PURCHASE_ORDER.crMsg_Create_PO_OAI_V1(msg_id,ao_id,:new.price, :new.quantity,:new.poid,:new.poitem); dbapp.PURCHASE_ORDER.pub_Create_PO_OAI_V1(msg_id,'DBAPPPAR2');END;
The SQL data types supported in iStudio are:
NUMBER
DEC
DECIMAL
INTEGER
NUMERIC
INT
REAL
SMALLINT
FLOAT
DATE
VARCHAR2
LONG
CHAR
CLOB
BLOB
RAW
DOUBLE
All user-defined data types except arrays of arrays
The SQL data types not supported in iStudio are:
NVARCHAR2
BINARY_FLOAT
BINARY_DOUBLE
TIMESTAMP
ROWID
UROWID
NCHAR
NCLOB
BFILE
Following can be the reasons:
Repository might not be running.
Hub database might not be running.
Spoke database might not be running.
User information specified in adapter.ini
file and in Oracle Wallet might not be correct.
Oracle Wallet might not contain the password information corresponding to your application name. For example, during installation you defined the application name as myDBApp
. Later, you changed the application name in iStudio to DBApp
. In such case, you need to specify the password corresponding to the new application name DBApp
in the Oracle Wallet. You can create password by using the oraclewallet
command.
Perform the following:
Check if the MESSAGEINFOTABLE in OAI schema has the message.
Check if the trigger, that publishes the message to the OAI schema, has specified the application name similar to the one specified for the application
parameter in adapter.ini
file. If the partition paarmeter in the adapter.ini
file has a value, then the trigger should specify the application name as the string concatenation of the values of application
and partition
properties.
Ensure that while creating the message in the trigger, the parent-child relationships are properly handled in accordance with the structure created in iStudio
Ensure that the value of the db_bridge_schema1_num_readers
parameter in the adapter.ini
file is more than one..
Perform the following:
Verify if you are seeing the log message "Subscribing to message (BusinessObject.EventName:versioninfo)" during the startup.
Ensure that the value of the db_bridge_schema1_num_writers parameter in adapter.ini
file is more than one.
Ensure that you have deployed the sql from iStudio