11 Replies Latest reply on Mar 3, 2014 4:10 PM by Christian Erlinger

    Run  set of procedures  automatically

    user12923153

      i create auto_conn.fmb forms  that contains  a push button called start

      i also create  a set of procedures which are well executed, these procedures  are:

      p99_1_Exp_Metar_ClimData_Item1;
      p99_2_Exp_Metar_ClimData_Item2;   
      p99_3_Exp_Metar_ClimData_Item3;

       

      my question :

      i  need to write a trigger when button pressed under the start button so as these mentioned procedures will be executed automatically each 15 minute (one quarter hour) for  2 years.

       

      any help i appreciate it .

      thank you

       

      software : Forms [32 Bit] Version 9.0.2.9.0 , oracle JInitiator: 1.3.1.9, WebUtil 1.0.2(Beta), window , IE 8

        • 1. Re: Run  set of procedures  automatically
          O.Developer

          use your desired  scripts/procedures/action ----under  trigger -When-Button-Pressed...

          • 2. Re: Run  set of procedures  automatically
            user12923153

            I write them under when button pressed

             

            Declare

            Begin

              p1

              p2

              p3

            End

            when  I press start button I want these procedures to run automatically each 15 minutes how can get it .

            thank you if you give  suggestions


            • 3. Re: Run  set of procedures  automatically
              O.Developer

              No... in Forms  this will work only if user presses this button.

               

              In order  to schedule a task in Oracle , you have change your method. I never tried it...because it depend upon your  enviornment.

               

              However  you can Google and refer something...http://stackoverflow.com/questions/13833955/how-to-schedule-a-procedure-in-oracle-10g

               

              http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN034

              • 4. Re: Run  set of procedures  automatically
                user12923153

                first thank you Mr   i appreciate you help

                about the DBMS_SCHEDULER it did not go with Oracle9i Enterprise edition Release 9.2.0.1.0 that i am using, instead i use DBMS_JOB.


                when button pressed (start) i write the following block
                DECLARE
                start_app_auto NUMBER := 0;
                BEGIN
                DBMS_JOB.SUBMIT (start_app_auto,   'p99_9_strat_my_app_man;'  ,  CURRENT_TIMESTAMP,   'current_timestamp + 3/1440')  ;
                -- this job to get the procedure "p99_9_strat_my_app_man" executing each 3 minutes
                -- also i would like to declare that, through the mentioned procedure there are 3 other procedures that have been called.
                END;

                 

                i am getting the following error:
                FRM: 40735 - WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06550.

                 

                can you help me to solve this problem
                thank you again for your support;

                • 5. Re: Run  set of procedures  automatically
                  Christian Erlinger

                  ORA-6550 means you have compile errors:

                   

                  $[CHE_TEST@asterix1_impl] begin
                    2  ladida
                    3  end;
                    4  /
                  end;
                  *
                  ERROR at line 3:
                  ORA-06550: line 3, column 1:
                  PLS-00103: Encountered the symbol "END" when expecting one of the following:
                  := . ( @ % ;
                  The symbol ";" was substituted for "END" to continue.
                  

                   

                  if not otherwise told dbms_job.submit validates the procedure you are passing to it; if this anonymous block has errors, it throws an error. Looking at your call:

                   

                  DBMS_JOB.SUBMIT (start_app_auto,   'p99_9_strat_my_app_man;'  ,  CURRENT_TIMESTAMP,   'current_timestamp + 3/1440')  ;
                  

                   

                  p99_9_strat_my_app_man;
                  

                   

                  is not a valid PL/SQL block. Surround it with begin and end; for example.

                   

                  Also I wouldn't call dbms_job (or any other SYS object) directly from within forms but wrap it up in my very own stored procedure and call that from forms. You'd simply have to grant all the needed privilages from SYS to your schema owner, but you'd have to grant just your stored procedure to your application users instead giving them direct access to SYS objects.

                  For dbms_job this means the job will get executed with the privileges of the owner of your stored procedure which might be desireable as well (or maybe not, depending on your requirements)

                   

                  cheers

                  • 6. Re: Run  set of procedures  automatically
                    user12923153

                    Hi Mr i appreciate your support

                     

                    first I don't think there in an error in the procedure P99_9_STRAT_MY_APP_MAN because I try it under other button strat_Man
                    Declare
                    ..
                    Begin
                    ...
                    P99_9_STRAT_MY_APP_MAN
                    ..
                    End;

                    it is well executed without error and with true result.


                    secondly
                    i try form isqplus to wrap the procedure as follow:
                    CREATE OR REPLACE PROCEDURE p_submit_p99_job AS
                       start_app_auto   NUMBER := 0;
                    BEGIN
                       DBMS_JOB.SUBMIT (start_app_auto,
                                        'p99_9_strat_my_app_man;',
                                        CURRENT_TIMESTAMP,
                                        'current_timestamp + 3/1440');
                       COMMIT;
                    END;
                    COMMIT;

                     

                    after this
                    i call that procedure from the WHEN-BUTTON-PRESSED trigger:
                    begin
                      p_submit_p99_job;
                    end;

                     

                    i would like to declare from oracle enterprise manage, meteo user, procedures node, i check the procedure created and i found it.

                    but when I try to execute:
                    select * from user_jobs;

                    I ma getting no row selected as a result !! it's confused a little 

                    • 7. Re: Run  set of procedures  automatically
                      Christian Erlinger

                      I didn't say your procedure has compile errors. I said

                       

                      if this anonymous block has errors, it throws an error

                       

                      and furthermore this

                       

                      DBMS_JOB.SUBMIT (start_app_auto,
                                          'p99_9_strat_my_app_man;', -- <= this is *not* a valid PL/SQL block
                                          CURRENT_TIMESTAMP,
                                          'current_timestamp + 3/1440');
                      

                       

                       

                      should be replace to

                       

                      DBMS_JOB.SUBMIT (start_app_auto,
                                          'begin p99_9_strat_my_app_man; end;', -- <= this is a valid PL/SQL block
                                          CURRENT_TIMESTAMP,
                                          'current_timestamp + 3/1440');
                      

                       

                      cheers

                      • 8. Re: Run  set of procedures  automatically
                        user12923153

                        I correct  the block

                        DBMS_JOB.SUBMIT (start_app_auto  ,   'begin p99_9_strat_my_app_man; end; '   ,   CURRENT_TIMESTAMP,    'current_timestamp + 3/1440');

                         

                        same error !! when i try to call the procedure from my form ; i m getting the same error
                        FRM: 40735 - WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06550.

                         

                        I would like to recall about P99_9_STRAT_MY_APP_MAN is created in my form fmb at program unit level,  must it be created in my database?? if yes;

                        i m  afraid that i cant use dbms_job any more to run procedure in schedule time  because the mentioned procedure call many procedures which in turn call many procedures...  in addition all these procedures use a block item in the form, that mean  if i try to recreate all these procedure in
                        my database using oracle enterprise manage or isqlplus then the compiler always load error message each time an instruction in a procedure use block or item specified in my form call_web.fmb??  is there any solution for this case?? really i appreciate any suggestions you gave me thank you a lot Mr.

                        • 9. Re: Run  set of procedures  automatically
                          Christian Erlinger

                          I would like to recall about P99_9_STRAT_MY_APP_MAN is created in my form fmb at program unit level,  must it be created in my database??



                          Ah, well yes of course it has to be a stored procedure. If you want to make use of dbms_job then you'd need to port all involved program units to the database. As there can't be any user input at all in theory this should be possible; however it might be some effort as most certainly you are referencing forms built-ins or forms blocks.

                          another very ugly solution would be to spawn several forms sessions via web.show_document each executing your forms code, but I wouldn't go down that route.


                          cheers

                           

                          Message was edited by: Christian Erlinger "§$§=()"/&§!=$)/ editor messing around with formatting

                          • 10. Re: Run  set of procedures  automatically
                            user12923153

                            if you can give me a link leading to a  web.show  _document that manage this issue I will be very grateful for you.

                            • 11. Re: Run  set of procedures  automatically
                              Christian Erlinger

                              web.show_document simply calls a new browser instance, so you'd need to pass it the URL you already access your forms application plus a parameter indicating what's to do.

                               

                              like

                               

                              http://<your_host>/forms/frmservlet?config=<your_config>&form=your_form%20do_call=<some_identifier>&userid=....

                               

                              and in that form simply call your procedure when do_call=<something>

                               

                              take a look at the documentation for web.show_document on how it works.

                               

                              cheers

                               

                              Message was edited by: Christian Erlinger Ah forgot to say: that is a really really really ugly implementation, and I certainly don't recommend it. The far better aproach is to port your logic to the database as this not only would give you the benefit of being able to call it via dbms_job but from *any* application able to connect to your database