9 Replies Latest reply on Feb 21, 2007 10:26 AM by 416900

    Creating a cross reference in iStudio

    50698
      I have tried to create a cross reference table in iStudio following the instructions in the users guide. Data never populates this table, and receive no errors. Can anyone offer advice or suggestions for setting up a cross reference table?
      Thanks!
        • 1. Re: Creating a cross reference in iStudio
          50698
          Ian:
          Many thanks for the response.

          Yep, I checked the "XREF_..." table, and it contained no records.

          Is there anything else I can look at?

          Thanks.
          • 2. Re: Creating a cross reference in iStudio
            416900
            Hi 47695,

            Before I try and help you out more, you said "Data never populates this table, and receive no errors."

            Can I ask did you actually look on the repository database at the contents of the XREF table that was created, or did you just look in iStudio for the XREF data after you ran your message?

            In iStudio, you tend only to see the Design Time elements, so the results will never appear in the Cross Reference table (unlike DVM's).

            Cross Reference tables can be easily thought of as 'Dynamic Domain Value Maps'. Whereas you add the LookUp values "manually" for a DVM (at Design Time), Cross Reference values only get populated at Run Time.

            Have a look at the repository database for your XREF table. For example, if I created a Cross Reference called "EMPLOYEE_NUM", the table created in the Repository Database is prefixed with "XREF_". So in my example, my table has been called "XREF_EMPLOYEE_NUM". Log on to the Hub Database and see if the results have been populated in the table.

            If not, let us know.

            Hope this helps
            Ian
            • 3. Re: Creating a cross reference in iStudio
              428493
              I was trying the same thing without much progress and I think we need a clarification on the following points:

              1) After the cross reference table definition and addition of applications to the table, how can we populate the contents of the table? - Manually insert rows using SQLPLUS or automatically insert rows with a dedicated database application subscribing to a "new" event?
              2) How do we use the cross reference table, suppose we already have contents there? - The "Cross Reference" in the subscribing wizard let us get the application specific keys from the table, given the keys of the common view. Am I correct about this?
              3) Following 2), is the common view corresponds to WorkFlow application in iStudio?

              Oracle experts, please clarify these points because the OracleAS 10g documentation doesn't give many clues on how the "Cross Reference" functionality should be used.

              Thank you very much.
              • 4. Re: Creating a cross reference in iStudio
                416900
                Hi 47695,

                Here's what I've done in the past to get XREF working.

                Scenario

                My Publishing Application / Adapter is DB2.
                My Subscribing Application / Adapter is DB1.

                Say I want to create a Cross Reference for Employee Number during a "Create Employee Event".

                I want to be able to prefix 'EMP' to the start of the EMPLOYEE_NUMBER sent from DB2.

                So, DB2 will send EMPLOYEE_NUMBER = '1234' and DB1 will have its EMPLOYEE_NUMBER as 'EMP1234'.

                My Publishing, Subscribing Application and Common Views are very simple like:

                EMPLOYEE_NUMBER (String)
                LAST_NAME (String)
                FIRST_NAME (String)

                Create a Cross Reference

                I create a Cross Reference in iStudio.
                Enabling Infrastructure > Cross Reference Tables.
                Right-click, select 'New'.
                Give your Cross Reference a name. e.g. 'EMP_NO_DB1_DB2'
                Press OK.
                Right-click on 'EMP_NO_DB1_DB2', select 'Add Application'.
                Choose 'DB1'
                Press OK.
                Right-click on 'EMP_NO_DB1_DB2', select 'Add Application'.
                Choose 'DB2'
                Press OK.

                Add Cross Reference to your Subscribing Event
                Create your Subscribing Event as normal by invoking the "Subscribe Wizard".
                When you get to the "Subscribe Wizard - Define Application View" window, you will notice a button called 'Cross Reference'.
                Press the 'Cross Reference' button to open the XRef dialog box.
                Now press the 'Modify Returned Arguments' button.
                We will create (Add) an argument called 'O_EMP_NO' (out employee number). e.g.
                Name = O_EMP_NO
                Type = String
                Owner = [blank]
                Array = No (Not checked)
                DEFAULT = NULL

                Once finished, Press 'Save'. This returns you to the XRef dialog box.

                From the top-left, expand the 'Returned Object' to find your new output parameter 'O_EMP_NO'. Highlight 'O_EMP_NO'.

                On the top-right, expand the 'Common Object', and select the EMPLOYEE_NUMBER element.

                Press the 'Map' button. The 'Cross Reference Table' dialog box appears. Choose the 'EMP_NO_DB1_DB2' XRef table, and Press 'OK'.

                You should now see in the bottom section of the 'XRef' window and new mapping. e.g.
                O_EMP_NO:mapped to:EMPLOYEE_NUMBER(using:EMP_NO_DB1_DB2)
                Press 'OK' to close the XRef dialog box.

                Click 'Next' to open the next 'Subscribe Wizard - Define Mapping'.

                Again, you will notice that a mapping has already been created for you. e.g.
                O_EMP_NO=CreateXref(EMPLOYEE_NUMBER,"EMP_NO_DB1_DB2")
                Do any other mappings you wish for this message, then press the 'Next' button.

                In the 'Defined Stored Procedure', you will now see that not only will your Procedure arguments contain your message elements (i.e. EMPLOYEE_NUMBER, LAST_NAME and FIRST_NAME) as IN parameters, but there is now an OUT argument too called O_EMP_NO.

                In my simple example, I just added some code to this procedure to set the O_EMP_NO value e.g.

                O_EMP_NO:= 'EMP'||employee_number;

                Export the PL/SQL code as normal, and load it (create the package / procedures) in your Subscribing Application (DB1).

                When you send the message through, and the procedure completes normally on the target (subscribing database), it passes back the value for O_EMP_NO to the Cross Reference Table. (Just as an aside, this actually works on message correlation ids.)

                So, the DB2 value is '1234', and the DB1 value is 'EMP1234'.

                Using Cross References in Subsequent Events

                As I have run the above Event end-to-end successfully, I now have a value in my XREF_EMP_NO_DB1_DB2 table.

                I can use this like I would use a Domain Value Map (DVM) transformation.

                For example, in an "Update Employee Event" (again from DB2 to DB1), I could simply use my XRef table as follows.

                Again, in my Subscribe side "Update Employee Event", create the mapping using the LookUpXref transfromation. e.g.

                EMPLOYEE_NUMBER=LookupXref (EMPLOYEE_NUMBER, "EMP_NO_DB1_DB2", "FALSE")

                This means that now when the Publishing Application (DB2) sends through an Employee Number, this is 'Looked Up' in the 'EMP_NO_DB1_DB2' Xref, and replaced with the DB1 value. (Otherwise, the 'pass through' value is "FALSE", therefore the target value will be NULL.)

                So, '1234' now becomes 'EMP1234'.

                I hope this helps.
                Yan

                • 5. Re: Creating a cross reference in iStudio
                  428493
                  Hi Ian,

                  Thank you very much for your step-by-step explanation. The questions in my previous posting may be rearranged as: What if your subscribing application is not a database application? Now you don't have a stored procedure, where the output parameter suppose to go?

                  Hope you have experiences.

                  Thank you.

                  Zhenyuan
                  • 6. Re: Creating a cross reference in iStudio
                    416900
                    Zhenyuan,

                    I can honestly say - I don't know!
                    The floor is open :-)
                    Yan
                    • 7. Re: Creating a cross reference in iStudio
                      428365
                      Please refer to InterConnect Documentation.

                      Here is the link to "How to populate Cross-Reference Tables?"

                      -They are populated at Runtime with data from events (messages)
                      -If you want to pre-seed a Table at design time, you should be using Domain Value Maps and not Cross-Reference Table. The two are similar except Domain Value Map values must be defined at design time while the Cross Reference Tables are populated at Runtime. The 'usage' for reading these values is similar.

                      http://download-west.oracle.com/docs/cd/B14113_01/integrate.904/b10404/infrast.htm#1020789

                      • 8. Re: Creating a cross reference in iStudio
                        556212
                        Hi ian

                        I followed this case study and it is working fine on oracle interconnect 9i but when i apply similar concept on oracle 10g then it don't work.

                        is this case study meant for oracle AS 9i only?
                        • 9. Re: Creating a cross reference in iStudio
                          416900
                          Hi,

                          I've never had the pleasure of using the 10g version, so as you suggest there may be differences.

                          Have a look at Chapter 6

                          http://download-uk.oracle.com/docs/cd/B14113_01/integrate.904/b10404.pdf

                          Hope this helps,

                          Yan