Re: Using InterConnect to push data from one Oracle DB to another

From: Bruce Loth <no_junk_mail_bruce.loth_at_iname.com_no_spam>
Date: Thu, 19 Dec 2002 17:16:15 -0500
Message-ID: <r9e40vcea5142oml73onofddqbd47igkkp@4ax.com>

On Thu, 19 Dec 2002 19:26:48 +0100, Frank <fbortel_at_home.nl> wrote:

>Versions?!? I will assume you use 4.1 (you refer to OAI), and 8.1.7(.4?)
>as RDBMS

The version of InterConnect is 4.1
The version of the Oracle RDBMS is 8.1.7 I am using just the Database Adapter
The OS (if it matters) is Win2K

If it helps at all, this is what the project looks like in iStudio:

http://bloth.dsl.patriot.net/images/istudio.gif

>Define these... entries in the table? - nothing. Code snippets? - see
>below

Some of the rows that are inserted in OAIHUB.STOREDPROC as as result of creating "Publish Events" and "Subscribe Events" under "Applications" in iStudio contain complete or partial code for stored procedures. For the simple example that I created, these are the values for the NAME column:

GeneratedDataTypes
crMsg_NEW_EMPLOYEE_ICF_V1
pub_NEW_EMPLOYEE_ICF_V1
GeneratedDataTypes
sub_NEW_EMPLOYEE_ICF_V1

(only 5 rows in the table)

For rows where NAME like 'GeneratedDataTypes', the BODY column is NULL. For the row where NAME like 'crMsg_NEW_EMPLOYEE_ICF_V1', the BODY column contains:

---

PROCEDURE crMsg_NEW_EMPLOYEE_ICF_V1
(
messageObjectID OUT NUMBER,
aoID OUT NUMBER,
EMPNO IN NUMBER,
JOB IN LONG,
MGR IN NUMBER,
HIREDATE IN DATE,
SAL IN NUMBER,
COMM IN NUMBER,
DEPTNO IN NUMBER,
ENAME IN LONG
)

AS
BEGIN
-- create a new message
messageObjectID :=
OAI.Agent.createMessageObject('NEW_EMPLOYEE:ICF/V1','EMPLOYEE','ICF/V1');

• create the message tree OAI.Agent.createAttributeObject(messageObjectID,'EMPLOYEE_NEW_EMPLOYEE_ICF_V1_P:ICF/V1', aoID);

OAI.Agent.addAttribute(messageObjectID, aoID, 'EMPNO', EMPNO);
OAI.Agent.addAttribute(messageObjectID, aoID, 'JOB', JOB);
OAI.Agent.addAttribute(messageObjectID, aoID, 'MGR', MGR);
OAI.Agent.addAttribute(messageObjectID, aoID, 'HIREDATE', HIREDATE);
OAI.Agent.addAttribute(messageObjectID, aoID, 'SAL', SAL);
OAI.Agent.addAttribute(messageObjectID, aoID, 'COMM', COMM);
OAI.Agent.addAttribute(messageObjectID, aoID, 'DEPTNO', DEPTNO);
OAI.Agent.addAttribute(messageObjectID, aoID, 'ENAME', ENAME);

END crMsg_NEW_EMPLOYEE_ICF_V1;

---

For the row where NAME like 'pub_NEW_EMPLOYEE_ICF_V1', the BODY column contains:

---

PROCEDURE pub_NEW_EMPLOYEE_ICF_V1(
messageObject IN NUMBER,
srcAppName IN VARCHAR2
)

AS
BEGIN
-- send the message
OAI.Agent.publish(messageObject, srcAppName);

END;

---

For the row where NAME like 'sub_NEW_EMPLOYEE_ICF_V1', the BODY column contains:

---

PROCEDURE sub_NEW_EMPLOYEE_ICF_V1(
EMPNO IN NUMBER,
ENAME IN LONG,
JOB IN LONG,
MGR IN NUMBER,
HIREDATE IN DATE,
SAL IN NUMBER,
COMM IN NUMBER,
DEPTNO IN NUMBER
)

AS
dummy NUMBER;
-- fill declarations here
BEGIN
-- fill code here
dummy:= 0;
END sub_NEW_EMPLOYEE_ICF_V1;

---

>You need to code WHAT you want to do, and HOW.

OK .. Fine ... and then what? Deploy these procs that are stored in OAIHUB.STOREDPROC.BODY as table insert triggers in the subscribe and publish databases? Specifically, the DB adapter NT service is polling the adapter database, OAI. I am guessing that the adapter is querrying the OAI.MESSAGEOBJECTTABLE table and looking for new entries. Is this correct? And what process is inserting rows into this table?

>Anyway, in short you need Oracle Applications Interconnect Users
>Guide, part A90225-02

I've got:

"Oracle 9iAS InterConnect
User's Guide
Release 2 (9.0.2)
January 2002
Part No. A92174-01

 .... still confused.

>I could work out your example, when you provide some mode details
>about versions and type of adapters.
>

Would love that!

Thanks!

• Bruce

Received on Thu Dec 19 2002 - 16:16:15 CST