Re: Using InterConnect to push data from one Oracle DB to another
From: Harri Kaukovuo <harri.kaukovuo4_at_nic.fi>
Date: 20 Dec 2002 05:37:49 +0200
Message-ID: <3e02908d_at_news.dnainternet.net>
>
> 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
>
>
>
> 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;
> ------------------------------------------------------------------
>
>
> 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?
>
>
> I've got:
>
> "Oracle 9iAS InterConnect
> User's Guide
> Release 2 (9.0.2)
> January 2002
> Part No. A92174-01
>
> .... still confused.
>
>
> Would love that!
>
> Thanks!
>
> - Bruce
>
>
Date: 20 Dec 2002 05:37:49 +0200
Message-ID: <3e02908d_at_news.dnainternet.net>
You need to use the iStudio "Export" functionality to first export the PL/SQL packages to file(s) and then manually install these packages to target spoke database. Export can be found choosing File->Export. Export will usually create two files per application (one for the object types, one for the procedures).
Adapters do not read the OAIHUB.STOREDPROC table on run time. This is just for metadata.
You got the new version 9.0.2 of the user guide. You should have user guide for 4.1.
- Harri
Bruce Loth <no_junk_mail_bruce.loth_at_iname.com_no_spam> wrote in news:r9e40vcea5142oml73onofddqbd47igkkp_at_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
>
>
-- ----------------------------------------------------------------------- Anti-spam: To reply by e-mail please ERASE the "4" after kaukovuo4Received on Fri Dec 20 2002 - 04:37:49 CET