This content has been marked as final. Show 6 replies
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.
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?
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.
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.
Can anyone let me know How to generate PL/SQL package from iStudio ?
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.
EMPLOYEE_NUMBER IN LONG,
START_DATE IN DATE,
LAST_NAME IN LONG,
FIRST_NAME IN LONG
-- fill declarations here
-- fill code here
INSERT INTO DB1USER.PER_ALL_PEOPLE_F
(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
Once you finished your code, Press Finish and deploy the code to your subscribing database as descibed posts above.
Send me a mail to email@example.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.