1 2 Previous Next 15 Replies Latest reply: Mar 10, 2005 12:32 PM by 428490 RSS

    Sample update/ insert Trigger code

    428490
      Hello every one,
      I'm an intern and I'm new to triggers programming.
      I've a java application that ping the SAP System. So now I want to develop a trigger so that when ever some tables in an external oracle db are updated or an insert is done in these tables , the trigger should fire and call the java application to ping the SAP system. Is there any sample code out there that can accomplish my task? or is there a tutorial out there which can help me accomplish this? This is urgent please. Any Help will be highly apprciated.

      Also could you help me explain where to place the trigger(presume on the database some where but I do not now) and how to call( or invoke the java app. from the trigger?) the trigger in the java app.

      Thank you all.

      Paddyman
        • 1. Re: Sample update/ insert Trigger code
          428490
          Given the above description which is suitable: Trigger, Procedure or a function?

          The idea is after an insert, update or delete on an oracle table(s) a java application should be called to ping the SAP System. Help me please.
          • 2. Re: Sample update/ insert Trigger code
            JustinCave
            I'm assuming that, in general, you're rather unfamiliar with PL/SQL and Oracle, since you're not familiar with triggers and you're an intern...

            First, I'd double-check that this is actually your assignment. It's not a particularly straightforward problem to solve and will almost certainly require discussions with DBA's to grant you privileges you don't have and to install components that may not be installed currently. This isn't something I would tend to give to an intern that wasn't already pretty proficient with PL/SQL.

            Assuming that this really is your assignment, where is the Java application? On the database server where the data is being changed or on some other machine? Can you define "java application"-- is this an EJB, a web service, an executable JAR file, etc?

            Justin
            Distributed Database Consulting, Inc.
            http://www.ddbcinc.com/askDDBC

            • 3. Re: Sample update/ insert Trigger code
              428490
              Hi Justin,
              Thanks for your respond. Actually I'm doing my work on a test oracle database which is on a test server.I can connect to the db from my client machine.
              The SAP system is also on a different Test server but I can also connect to it from my client machine.

              The Java app. is a simple .java application on my client machine. you'll see it below.

              import com.sap.mw.jco.*;
              public class TestConnect {
                   JCO.Client mConnection;
                             
                   public TestConnect() {
                        try{
                   mConnection = JCO.createClient("00",//SAP Cleint
                   "test",//userid
                   "test",//password
                   "EN",//language
                   "testserver",//server host name
                   "00");// system number
              mConnection.connect();//connection is open
              System.out.println(mConnection.getAttributes());//
                   mConnection.disconnect();//     }
                   catch (Exception ex){
              ex.printStackTrace();
              System.exit(1);
                   }
              }     
                   public static void main(String args[]) {
                   TestConnect app = new TestConnect();
              }
              }

              What I wanna to do is using a trigger or a function or a procedure(which ever is best)to call the above java application to ping the SAP system when ever an UPDATE/INSERT/DELETE is done on these tables in the oracle DB(The tables are: test1, test2, test3).
              What is best(trigger or procedure or function) in solving this problem? Any sample code or a tutorial will be highly appreciated. Thanks
              • 4. Re: Sample update/ insert Trigger code
                6363
                Well you haven't really described what problem you are trying to solve. Your Java code that pings SAP when data is modified is part of a solution to <something>, and without that context it is not really possible for anyone to help you. However if you can describe the problem, it is quite possible that the answer will not require the Java ping method either.
                • 5. Re: Sample update/ insert Trigger code
                  428490
                  Hello,
                  this is my problem again but I think if one read my previous posting he'll understand what I really wanna do.
                  I'm working on a client machine. From this machine I can ping the SAP system in a server using a java Application I have posted above. I can also connect to the an oracle DB on a different server from my client machine using a small Java App. and JDBC.

                  My problem now is. I want that any time that an UPDATE OR INSERT OR DELETE is done on the oracle DB residing on the sever the Java Application should be called (invoked or triggered) to ping the SAP system. What is the best way in doing this?

                  Using a TRIGGER, OR A FUNCTION OR A PROCEDURE? Where should this trigger/fucntion/ procedure be stored? Where also should I place the Java App.?Do I need to convert the Java Appl. to a JAR file or what?

                  Any Sample Code or Tutorial on how to write some thing like that will be highly appreciated. I hope I'm alot more clear as to what the assignment is.

                  Thank you all.
                  • 6. Re: Sample update/ insert Trigger code
                    6363
                    I did read the previous postings, what I am getting at is pinging the SAP server is not the problem you are having, it is part of your current solution to a problem that you have not described. What problem will be solved by pinging the SAP server?
                    • 7. Re: Sample update/ insert Trigger code
                      428490
                      Oh I got you. This is just part of the problem actually. I'm an intern and trying to improve a connection Link from SAP and Oracle DB using Java.

                      To connect to SAP from outside you'll need SAPJCO (SAP Java connector) These are just like oracle JDBC if you wannt to connect to Oracle using a Java application.

                      The whole idea is for data exchange between SAP and Oracle DB. Both system are independent.

                      The problem is more much complex b/c calling table data from SAP is through SAP standards RFCs (Remote Function Calls) called BAPIs(Business Application Programming Interfaces)
                      Now as the Chinese says "A Journey of a 1000 miles beginnes with the first step."

                      I can ping the SAP system using a java App and I can connect to the Oracle DB using JDBC with a small Java App.

                      But I thought the best way to accomplish this will be when ever an UPDATE, INSERT, OR DELETE is done on those tables in the Oracle DB then a Java appl. will be called to transfer/ update the data in SAP using the SAP BAPIs because SAP needs these data.
                      Now what I want to do is first see if when the tables are update/insert/delete the java Appl. should be called to ping the SAP system.

                      The most difficult part of the job will be to programm the RFC BAPIs because here I'll really need to know the SAP system well and OOP.

                      So if you could give me some ideas or tutorial on how to call a Java App when ever some tables in Oracle are updated/ insert or delete then I would really appreciate it.

                      Thank you.
                      • 8. Re: Sample update/ insert Trigger code
                        6363
                        So its a replication issue, tricky at times made more complex by the proprietary SAP API's that need to be used.

                        Firstly while a trigger can be used I would not directly call Java or accomplish the whole data transfer via a trigger, this can have severe performance implications on DML within the Oracle DB, particularly if it involves access to an external system. What would happen if that system was not available?

                        I would do something like trigger copies modified data to a staging table and initiates an asynchronous process using DBMS_JOB package.

                        Your Java class can be loaded in the database using LOADJAVA utility and a PL/SQL wrapper can be created for it. The DBMS_JOB process calls a stored procedure that can then call your Java code, call the SAP API's, transfer the data and delete it from the staging area when done.

                        To see examples of calling a Java class within PL/SQL see the Java Stored Procedures Developer's Guide. For examples on DBMS_JOB see the Supplied PL/SQL Packages and Types Reference.

                        Good luck.
                        • 9. Re: Sample update/ insert Trigger code
                          JustinCave
                          Calling a simple Java application on your client machine from a trigger in an Oracle database is not, in general, possible. If the test database's file system could access files on your machine, you could, potentially, create a Java stored procedure that called out to a shell to invoke the Java application on your local machine. This would require the DBA's to install the Java in the database if they have not already done so. It would also require that they grant you some additional privileges to be able to call out to a shell, which should trigger a pretty serious security review. Not the easiest of options. Mapping to a client machine from the database file system will also require jumping through some hoops with the system admins. I have a hard time believing you'll get the necessary sign-off here...

                          If you can get the DBA's to install Java in the database, and the SAP Java connector is a pure Java component, you may be able to load the SAPJCO into the database and put your Java logic into a Java stored procedure.

                          Alternately, you could enable Streams on the Oracle database, use a J2EE application server to receive the Streams logical change records as JMS messages and use those messages to trigger your SAP classes to fire.

                          Neither of these approaches, though, strike me as something that would be appropriate for an intern's project. There are a lot of environment issues here that I have to believe an organization would want to architect in a more long-term manner...

                          Justin
                          Distributed Database Consulting, Inc.
                          http://www.ddbcinc.com/askDDBC
                          • 10. Re: Sample update/ insert Trigger code
                            428490
                            Thank you all for the responses. I think the best option as Justin said was to have the DBA to install the Java SDK on the Oracle server.I think he will do that if I tell him or I can do that my self when given the rights to do it.
                            And I will also install or configure the SAPJCO on the Oracle Server.

                            Now I will concentrate on how to build a procedure that will trigger the java application on the Oracle DB to ping the SAP system when ever there is an UPDATE, INSERT OR DELETE on one of the tables.
                            SAP is a complex System and the use of its API make it even more complex and very tricky and often difficult to understand.

                            Should I have further questions, I will post it here. Thank you all (Justin and User 3360) for the sugestions. I'll update you how I develop in the next hrs or days ahead.
                            • 11. Re: Sample update/ insert Trigger code
                              JustinCave
                              Note that there is a difference between "installing the Java SDK on the Oracle server" and "installing the JVM in the Oracle database".

                              The Oracle database itself can host a specially written JVM, which allows you to write Java code that runs inside Oracle. That is the approach that you will want to use here (assuming the SAP Java connector is a pure Java component and does not rely on Oracle client libraries).

                              Justin
                              Distributed Database Consulting, Inc.
                              http://www.ddbcinc.com/askDDBC
                              • 12. Re: Sample update/ insert Trigger code
                                6363
                                I would still strongly recommend not calling the Java to ping an external system directly from a trigger. This will cause the DML on the trable to wait until the Java call completes. If there is a delay accessing the remote system the Oracle database will appear to have hung for users attempting to modify data. A very dangerous situation.
                                • 13. Re: Sample update/ insert Trigger code
                                  428490
                                  Could you explain a little more on what you're talking here --> "there's a difference between installing the Java SDK on the Oracle server" and "installing the JVM in the Oracle database".

                                  How is it done? Is there a tutorial out there to explain how to install the JVM(Java Virtaul Machine) on the Oracle DB?

                                  No I do not wanna call the Java to ping an external system directly from a trigger. I want to use a Java stored procedure as Justine suggested. I do not wanna use a trigger since this might cause some problems as you earlier on explain. Thanks
                                  • 14. Re: Sample update/ insert Trigger code
                                    JustinCave
                                    Wherever the logic resides, 3360's point is that you probably need to ensure that the logic is executed asynchronously, not synchronously. You would generally not want to have a user's transaction waiting for the SAP update to complete unless you want that transaction to fail if the SAP update fails. A stored procedure or a Java stored procedure can be called synchronously or asynchronously, you need to ensure that your architecture makes the appropriate decision about which approach to use.

                                    3360 linked to the Java Stored Procedures Developer's Guide, which is a good place to start to understand the distinctions. The DBA may already have installed the JVM in the Oracle database, this is an installation option. He or she may not have allocated appropriate memory structures to accomodate Java stored procedures, though. Since the DBA will have to do this, you probably want to concentrate more on using the internal JVM, particularly understanding the differences between a normal JVM and the Oracle JVM.

                                    Justin
                                    Distributed Database Consulting, Inc.
                                    http://www.ddbcinc.com/askDDBC
                                    1 2 Previous Next