6 Replies Latest reply on Aug 18, 2005 11:46 AM by 416900

    Interconnect - data from db to xml


      I'm going to make an interconnect solution where data from an Oracle Database is supposed to end up as an xml-file.
      Need some help getting started. :-)
      An insert in a spesific table in the Oracle database shall result in one xml-file. What adapter should I use against the oracle-database, databaseadapter or aq adapter? If I'm supposed to use a databaseadapter, how can I trigger the transmission to the hub?
      On the xml-side, shall I use the ftp-adapter in xml-mode, or d3l?

      :-) Lene
        • 1. Re: Interconnect - data from db to xml

          Taking the easy one first...on the target side, I would use the FTP Adapter and use it in XML mode.

          When you create the mappings between your Source Application View and the Common View, iStudio helps you out by creating a Package (which contains 2 procedures, and sometimes some functions if your Source Application View is an Array type).

          Steps to Take
          1. Create your Common View Business Object and Event (or Procedure).
          2. Create your Publish (Source) Application Event.
          3. Go through the Wizard, and create your mappings.
          4. Once the mappings are done, press the "Finish" button.
          5. In iStudio, click on 'File', 'Export PL/SQL'.
          6. Expand 'Applications', your Source Application and 'Published Events'.
          7. Highlight the 'Publish' Event you've just created.
          8. In 'File Prefix', browse or enter the output directory, followed by a final prefix.
          9. Press 'OK'
          10. You will see 'Operation Completed Successfully'
          11. Use Windows Explorer, and go to the directory you selected. You will see a file called "[APPLICATION_NAME_Event_Name].sql"

          If you look at the *.sql file created, it will contain at least two procedures. A crMsg_ procedure. And a pub_ procedure.

          Install this *.sql in your custom or appropriate schema.

          Create a wrapper script which calls the crMsg_ procedure first. Pass your data from your table to this crMsg_ procedure.

          Now call the pub_ procedure to finally "publish" the message.

          *** Note ***
          Ensure that you set the parameter "srcAppName" to the name of your Publishing (Source Application) Database Adapter name when you call the pub_ procedure.

          If your Source Application View is an Array type, then you will also notice that the iStudio created *.sql file will contain a function(named cr_[BUSINESS_OBJECT] for example), as well as the crMsg_ and pub_ procedures.
          This time, call the crMsg_ procedure first (to create the owning object), and pass into the function the aoID (as well as your 'looped' data). Once you've passed all the data to the function, close your loop and call the pub_ procedure.

          As I say, it isn't easy to explain in writing, however just to say that you can have single row -> single simple xml transaction using OAI, as well as multiple row -> complex xml using the DB Adapter / FTP(XML) Adapter combination.

          • 2. Re: Interconnect - data from db to xml
            Thank you for fast and helpful reply!

            Just a few more questions. :-)
            1) By a "wrapper script", do you then mean a db-trigger that calls the mentioned procedures on insert and update?

            2) I tried to execute the cr_[Business_object]-function manually to test, but how do I find the values for "messageobjectid" and "aoid"?

            3) The tablecontent that's supposed to end up as an XML-file (by the help of a db-adapter and a ftp-adapter) contains a clob-column. Will this be a problem?

            :-) Lene
            • 3. Re: Interconnect - data from db to xml
              Sorry about question nr 2. I see that the crMsg creates the values I asked about (they're output parametres, not input), and I can see them using dbms_output.put_line.

              :-) Lene
              • 4. Re: Interconnect - data from db to xml
                I always use "wrapper scripts" to call the crMsg_ and pub_ procedures. This is mainly due to the fact that iStudio bases the Exported PL/SQL code package names on the Business Object name. Therefore I have two packages.

                One package containing procedures to Generate message data, Consume message data and call the iStudio generated code. <-- this is what I mean by a "wrapper script".

                The other is the iStudio generated packaged procedures.

                Clobs - never used them. However, I think that OAI treats CLOB objects as "Strings", so in theory you should not have a problem. Just avoid having characters in the CLOB which could be interpreted as XML tags (e.g. < / > etc,).

                You'll have to try it and see Lene.

                HTH, Yan
                • 5. Re: Interconnect - data from db to xml
                  Can anyone let me know How to generate PL/SQL package from iStudio ?

                  • 6. Re: Interconnect - data from db to xml

                    If you have a DB Adapter subscribing to a message, then you need to add some logic in the subscribing code (generate using iStudio as described above) to consume it.

                    In the Subscribe Wizard, once you have performed your mapping in the 'Subscribe Wizard - Define Mapping' window, click Next.

                    The 'Subscribe Wizard - Define Stored Procedure Window' appears

                    Toggle the 'SQL Code for' to show your subscribing event.

                    After the statement
                    -- fill code here

                    add your consuming code. For example let's say I have an event called 'sub_Create_Employees_OAI_V1'. My code could look like this.

                    PROCEDURE sub_Create_Employees_OAI_V1(
                    EMPLOYEE_NUMBER IN LONG,
                    START_DATE IN DATE,
                    LAST_NAME IN LONG,
                    FIRST_NAME IN LONG

                    dummy NUMBER;
                    -- fill declarations here
                    -- fill code here
                    (EMPLOYEE_NUMBER, --the argument from the above procedure
                    START_DATE, --the argument from the above procedure
                    LAST_NAME, --the argument from the above procedure
                    FIRST_NAME --the argument from the above procedure

                    dummy:= 0;
                    END sub_Create_Employees_OAI_V1;

                    Once you finished your code, Press Finish and deploy the code to your subscribing database as descibed posts above.

                    Send me a mail to ian_scorrer@yahoo.com with the subject line "Simple" and I'll send you a file called simple.zip

                    This file contains scripts and guidance to create a simple DB to DB message.