2 Replies Latest reply on Jun 7, 2005 10:23 PM by 375819

    FTP & DB Adapter, Binary Files, Deploy to Production

      Greetings fellow InterConnect users:

      First off, let me say that we are using ALL Oracle 10g Release 2 components (10.1.2) and not 10iR1 or 9i (9.0.4 etc). I think this is an important distinction to make upfront as it seems (from my reading online etc) that things may be different between versions.

      I have installed Oracle InterConnect HUB, Adapters, and Dev Kit and have successfully passed some messages around, so I am sure that my products are installed and configured properly.

      In the end, here is what I would like to see: XML or CSV or Binary (Excel, PDF) File from FTP server or local filesystem -> FTP Adapter -> HUB -> DB Adapter -> Drop file as BLOB/CLOB into a table (auto-incremented id field, blob field). Once I get this basic example working, then I can see about applying transformations along the way or more complex business cases.

      Each XML file contains one or more instructions for one or more employees who belong to one or more companies in a single master instruction file. So the general structure is:
      Master Instruction -> Companies[] -> Company -> Employees[] -> Employee -> Instructions[] -> Instruction

      I have successfully made the FTP Adapter and DB Adapter work at a very basic level with a single XML test file from which I extracted the DTD. The FTP Adapter picks up the XML file, converts it into the DTD Common View/Object, passes it via the Hub to the DB Adapter which then extracts a few fields and puts them into a single new record in a table.

      I have the following questions:
      1. Is it possible to "easily" turn this Common View message back into an XML file/clob and store in my database on the DB Adapter end? From reading online in these Forums, it seems like I will need to write a stored procedure which accepts my Master Instruction object and rebuilds the XML from the varrays of data back into the original XML, and then store it as a CLOB all via PL/SQL. This seems like "a lot" of work if there is an easier or built-in method.

      2. Assume that instead of storing my data as an XML clob, I would like to break it up into multiple records, say 1 record in the table per Company + Employee + Instruction combination -- say Company "Oracle", Employee "Wayne", Instructions ["get coffee", "print file", "talk to boss"] becomes 3 individual records in my table. Is this possible without writing PL/SQL? From my limited experience, it seems like InterConnect enforces a "1 message = 1 record" paradigm and requires handling of arrays etc in PL/SQL. (PL/SQL is no problem, I just want to know if this is the correct approach.)

      3. Is it possible to pass the Excel and PDF files thru the FTP Adapter and eventually store them as binary data in my database? Clearly I cannot describe either file with an XML DTD or D3L file, so I'm not sure how I can get this file/data into the Hub? Someone else on the Forum suggested that you could create a new XML file and use a #PCDATA# field to store the UUENCODED binary data in the XML file, then use the regular Adapters etc to deal with the XML file, and finally Unencode it in PL/SQL in the Database, but it seems like the Binary XML data element was removed from 9.0.4 to 10.1.2 so that's no longer an option (?). Ideally I'd like to just pick these files up out of the FTP site as-is without having to turn them into XML with uuencoding etc anyways.

      3b. If the Excel/PDF file import is not possible using the standard InterConnect components, is it possible if we write some Java code (write new Adapters or Bridges)? If so, where can I find tips on where to find sample code or instructions etc for achieving this functionality? I have to imagine there are other Oracle InterConnect customers who require this same handling of binary files.

      4. Someone in the the Forums said that the Hub only supports messages of 32kb max size. Assuming I have an XML file that is 300kb (or even 10mb), how can I get this XML data inserted into my database using the FTP Adapter etc? And how does this work for 5mb binary (PDF, XLS) files? (This 32kb limit may have been increased in 10.1.2 but I have not found any detailed information in the InterConnect User Guide.)

      5. Assuming I can get my entire system built the way I want it to run in my local development environment. How can I easily package and deploy this system in my staging and production environments, assuming I have zero access to these systems and must package up the files etc in a zip file and provide instructions to our Production Support Team for deploying it? I've read briefly about "oaiimport" but perhaps there is another way to do it? Based on what I've read here in the Forums, it seems like there are several negatives to using oaiimport (lose message history, lose current [unretrieved] messages, etc) so perhaps someone who is already in Production has some advice?

      Thanks very much for any response and assistance!!
        • 1. Re: FTP & DB Adapter, Binary Files, Deploy to Production

          I will try to answer some of your questions, but will give way to other I/C users.

          Question 1 - I think that you would have to do some additional manipulation of the data. Because the DB Adapter does not recognise CLOB data type, you would have to pass an "XML file" as a single LONG type and pass it into a temporary table.

          From the temporary table do a to_lob() select, and insert / update into your final target table.

          Question 2 - In the past I've created tables which contain many layers of object TYPES of VARRAY(5000) size objects.
          This "raw" staging table has the same or similar structure to the inbound "XML" structure.
          So I end up with one row in the "raw" staging table.
          The next step is to expand this data into multiple rows on a second table.
          This approach, as you say, would be the one I'd take too.

          Question 3 - Can't help here.

          Question 4 - I've run XML (FTP Adapter) thru to DB Adapter with the XML file being 525kb (17,000 + lines) with no problems. See answer to Question 2.
          Not sure where this "32kb" comes in. Maybe on each individual attribute the data cannot be more than 32kb?!? I can't say.

          Question 5 - Migrate or oaiexport/oaiimport. I'm stuggling with that one too. At the moment we are having issues with our migrate facility from iStudio.
          Have you looked into the migrate facility in iStudio? Your local system would obviously need network connectivity to the Test or Prod env.

          As far as packages etc, we are looking at doing things the "long way" too.

          • 2. Re: FTP & DB Adapter, Binary Files, Deploy to Production
            Just wanted to say, THANKS Yan.

            Seems like we are going to find another solution for now as we are mostly dealing with binary files and chopping them up to pass them into hub and then paste them together to rebuild the file is just too much work when I can trivially insert files as BLOBs with other methods.