1 2 3 Previous Next 42 Replies Latest reply on Jul 20, 2018 1:24 PM by jaramill Go to original post
      • 30. Re: Commit data only if all procedures are successful
        jaramill

        Cookiemonster76 wrote:

         

        If people can run this procedure manually from sqlplus then you absolutely don't want to use dbms_output since you can't be sure they've run set serveroutput on.

         

        Very true because even I have to manually SET SERVEROUTPUT ON in my test scripts while running from TOAD out to SQL* Plus.

         

        Cookiemonster76 wrote:

         

        Use raise_application_error.

        Yes I'm adapting it to RAE now.  Thanks.

        • 31. Re: Commit data only if all procedures are successful

           

          Am i doing the correct implementation.

          Close

           

          You want to divide things into COMMAND_and_CONTROL and EXECUTION.

          1. create or replace procedure main_p as 
          2. begin 
          3. p1; 
          4. p2; 
          5. end; 
          6. begin 
          7. main_p; 
          8. commit; 

           

          The 'main_p' is the CONTROLLER. And 'p1' and 'p2' are slaves - each of which should do ONE THING only.

          (note - that 'one thing' could, of course, be act as a controller for other procedures)

           

          The commit/rollback should be done at the point at which the TRANSACTION is completed. That could be in 'main_p' or even not at all and left to the client to do.

           

          But you are missing instrumentation/logging. All such batch processes should be instrumented using a logging package to capture (at a minimum): 1) start time, 2) end time, 3) completion status (e.g. FATAL, ERROR, WARNING, INFO, DEBUG, other) and 4) number of rows affected.

           

          See example/prototype code here:

          Re: How to get a called procedure/function name within package?

           

          Write modular code and starting REALLY SIMPLE (your example with a single NULL statement in each code block.

           

          That lets you begin testing IMMEDIATELY even though some code blocks are just dummied up.

           

          It also lets you assign the actual writing of 'p1' and 'p2' to two different developers after you provide the API requirements (parameters and return datatype if a function).

          • 32. Re: Commit data only if all procedures are successful
            jaramill

            GregV wrote:

             

            Hi,

             

            If your procedures are somehow connected then place them in a package. You then have the main procedure calling p1 and p2 and committing at the end. Only one WHEN OTHERS block is necessary in the main procedure.

            Bingo!!   This actually how my code is structured.  Which is what I refactored my code after looking at RAISE_APPLICATION_ERROR per CookieMonster's suggestion.

            Package spec with 1 public procedure (i.e. main),  Package body has main plus "x" number of other private sub-programs.

            So now my only exception section is in the public main procedure and I reduced it to just one exception clause "OTHERS".

            • 33. Re: Commit data only if all procedures are successful
              jaramill

              Cookiemonster76 wrote:

               

              And really what are you trying to accomplish with those handlers?

              You're logging to dbms_output, which means some client process has to go and retrieve that from the buffer, so why not just let the error propagate? The client will get the error stack anyway

              Thanks Cookie.  Code cleaner now.  Above in other procedures I do have RAE such as this one below

               

              RAISE_APPLICATION_ERROR(-200001, 'User-defined exception raised...No Date value passed', TRUE);
              

               

              and at the end of the main proc in package body I have

               

                 COMMIT;
              
                 EXCEPTION
              
                    WHEN OTHERS THEN
                    
                       IF(cur_apps_by_region%ISOPEN) THEN
                          CLOSE cur_apps_by_region;
                       END IF;
              
                       IF(cur_regions_by_app%ISOPEN) THEN
                          CLOSE cur_regions_by_app;
                       END IF;
              
                       DBMS_OUTPUT.PUT_LINE('OTHERS exception raised...' ||
                                            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
                       RAISE;
              
                 END prc_main;
                  
              END pkg_batch_load_eod;  -- End package body
              /
              
              • 34. Re: Commit data only if all procedures are successful

                1. using dbms_output is NOT instrumenting your code.

                2. dbms_output isn't accessible until the transaction ends - things just keep piling up in the buffer

                3. dbms_output won't be of any use unless it is actually enabled

                 

                Instrument your code properly.

                • 35. Re: Commit data only if all procedures are successful
                  jaramill

                  rp0428 wrote:

                   

                  1. using dbms_output is NOT instrumenting your code.

                  2. dbms_output isn't accessible until the transaction ends - things just keep piling up in the buffer

                  3. dbms_output won't be of any use unless it is actually enabled

                   

                  Instrument your code properly.

                  1. Please explain "instrumenting".........I know what it means but an example   nevermind, I had a brain freeze, as to this is what I am doing in the other procedures:

                   

                           IF(prc_generate_timestamps.ts_dt_time > SYS_EXTRACT_UTC(SYSTIMESTAMP)) THEN
                  
                              RAISE_APPLICATION_ERROR(
                                                      -20002
                                                     ,'User-defined exception raised...Date time passed ' ||
                                                      'is greater than current UTC time ' ||
                                                      CHR(10) || CHR(10) ||
                                                      'Incoming    Date Time: ' ||
                                                      TO_CHAR(prc_generate_timestamps.ts_dt_time
                                                             ,c_timestamp_fmt_mask) || CHR(10) ||
                                                      'Current UTC Date Time: ' ||
                                                      TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP)
                                                             ,c_timestamp_fmt_mask)                                   
                                                     ,TRUE
                                                     );
                  
                           END IF;
                  
                  

                   

                  or

                   

                                 RAISE_APPLICATION_ERROR(
                                                         -20003
                                                        ,'User-defined exception raised...NO region and application ' ||
                                                         'found for region ' || prc_main.region ||
                                                         ' and application ' || prc_main.app
                                                        ,TRUE
                                                        );
                  

                   

                  2. This I already know but as soon as an exception is raised it aborts.  The exceptions I'm raising via RAE are input parameter exceptions

                  3. If you have SET SERVEROUTPUT ON it is enabled (as Cookiemonster reminded me and in the documentation)....

                   

                  Usage Notes

                  • It is not necessary to call this procedure when you use the SET SERVEROUTPUT option of SQL*Plus.

                   

                  but yes using DBMS_OUTPUT.ENABLE is missing and will add it to be safe.

                  • 36. Re: Commit data only if all procedures are successful
                    jaramill

                    Since we're on this topic of instrumentation, Mr. ORACLE-BASE himself (Tim Hall) as an excellent write-up and package about this (which I'm reading) in case the OP (original poster) and others are interested:

                     

                    https://oracle-base.com/articles/misc/instrumenting-your-plsql-code

                    • 37. Re: Commit data only if all procedures are successful

                      Doing it your way VIOLATES the main tenets of modular programming.

                       

                      By using dbms_output you are tying things to a particular client like sql*plus. The only place that should be done is in development when you are doing unit testing.

                       

                      It should NEVER be done in production code. Production code, with rare exceptions, should NOT be run from a command line or from a client using sql*plus.

                       

                      You need to LOG info to an actual log table. That way the code units can be executed stand-alone or incorporated into larger modules.

                       

                      The reason is the same as even YOU already mentioned about why you don't want to put COMMIT/ROLLBACK in most code blocks. It prevents/detracts from modularity and control.

                       

                      The code blocks that do the work should NEVER be written with specific logging in mind. They should use a general-purpose logging package. That logging packages is what should control: 1) WHETHER logging even happens (log FATAL only or INFO/DEBUG/other), 2) WHAT gets logged (different log levels may log at different granularities and 3) HOW logging occurs (FATAL errors may log to a table and generate an email to get URGENT action - DEBUG may log to a table and/or use dbms_output as you seem to favor).

                       

                      Write modular code - that is the key point. Don't put non-core details (logging, exception handling) in the small, single-purpose code blocks that do the actual work. That stuff belongs in the CONTROLLER modules.

                      • 38. Re: Commit data only if all procedures are successful
                        jaramill

                        rp0428 wrote:

                         

                        Doing it your way VIOLATES the main tenets of modular programming.

                         

                        By using dbms_output you are tying things to a particular client like sql*plus. The only place that should be done is in development when you are doing unit testing.

                         

                        It should NEVER be done in production code. Production code, with rare exceptions, should NOT be run from a command line or from a client using sql*plus.

                         

                        You need to LOG info to an actual log table. That way the code units can be executed stand-alone or incorporated into larger modules.

                         

                        The reason is the same as even YOU already mentioned about why you don't want to put COMMIT/ROLLBACK in most code blocks. It prevents/detracts from modularity and control.

                         

                        The code blocks that do the work should NEVER be written with specific logging in mind. They should use a general-purpose logging package. That logging packages is what should control: 1) WHETHER logging even happens (log FATAL only or INFO/DEBUG/other), 2) WHAT gets logged (different log levels may log at different granularities and 3) HOW logging occurs (FATAL errors may log to a table and generate an email to get URGENT action - DEBUG may log to a table and/or use dbms_output as you seem to favor).

                         

                        Write modular code - that is the key point. Don't put non-core details (logging, exception handling) in the small, single-purpose code blocks that do the actual work. That stuff belongs in the CONTROLLER modules.

                        Well production code in the past (at other clients) I did exactly that, which is call a Unix shell script which then called executed a SQL script via Oracle's SQL* Plus.  In said script, I then used UTL_FILE to write out to a log file as and used a lot of instrumentation).  Other times we're not allowed to write to files but the DBMS_OUTPUT is captured via the job controller.  So saying that tying it to SQL* Plus somehow violates modular programming is a bit extreme as it is part of Oracle's basic toolset (like "vi" is part of Unix, unlike emacs).

                         

                        But for right now my DBMS_OUTPUT messages are for development purposes and then I have to see what the client wants (i.e. logging, etc..)

                         

                        Unfortunately being a consultant here at this current client, I do not have the privileges to write to a log file from Oracle per se (though I am going to get more clarifications on requirements as right now it literally is all just my manager telling me, nothing formally written down in some document).  There is no "general-purpose" logging package here...or at least that I know of.

                         

                        As for NEEDING to log into a table, these are things that I have to discuss and see whether or not the previous process is doing that and if so then yes, no need for DBMS_OUTPUT and I would just log it into a table.

                         

                        I agree with your last point in that I have my code as such in that the controller module (i.e. the procedure "main" in my package is the only one with an exception section).

                         

                        What this discussion has spurred on is that I need (as you like to say)........more detailed information on requirements.

                        • 39. Re: Commit data only if all procedures are successful
                          BluShadow

                          rp0428 wrote:

                           

                          Doing it your way VIOLATES the main tenets of modular programming.

                           

                          By using dbms_output you are tying things to a particular client like sql*plus. The only place that should be done is in development when you are doing unit testing.

                           

                          It should NEVER be done in production code. Production code, with rare exceptions, should NOT be run from a command line or from a client using sql*plus.

                           

                          You need to LOG info to an actual log table. That way the code units can be executed stand-alone or incorporated into larger modules.

                           

                           

                           

                           

                          "We know a song about that, don't we children?"....

                           

                          PL/SQL 101 - DBMS_OUTPUT

                           

                          The last section covers the principles of whether DBMS_OUTPUT should be used for tracing code execution/debugging and what the alternative(s) are.

                          • 40. Re: Commit data only if all procedures are successful
                            Cookiemonster76

                            Calling dbms_output.enable isn't particularly useful since it won't make sqlplus read the buffer at the end and display the results.

                            You need to use set serveroutput on for that and it calls enable itself.

                            • 41. Re: Commit data only if all procedures are successful
                              jaramill

                              Cookiemonster76 wrote:

                               

                              Calling dbms_output.enable isn't particularly useful since it won't make sqlplus read the buffer at the end and display the results.

                              You need to use set serveroutput on for that and it calls enable itself.

                              Agreed, as I rarely use it, and that's why I have SQL* Plus options in a separate script that gets called first within the main SQL script.

                              • 42. Re: Commit data only if all procedures are successful
                                jaramill

                                BluShadow wrote:

                                 

                                rp0428 wrote:

                                 

                                Doing it your way VIOLATES the main tenets of modular programming.

                                 

                                By using dbms_output you are tying things to a particular client like sql*plus. The only place that should be done is in development when you are doing unit testing.

                                 

                                It should NEVER be done in production code. Production code, with rare exceptions, should NOT be run from a command line or from a client using sql*plus.

                                 

                                You need to LOG info to an actual log table. That way the code units can be executed stand-alone or incorporated into larger modules.

                                 

                                 

                                 

                                 

                                "We know a song about that, don't we children?"....

                                 

                                PL/SQL 101 - DBMS_OUTPUT

                                 

                                The last section covers the principles of whether DBMS_OUTPUT should be used for tracing code execution/debugging and what the alternative(s) are.

                                Sometimes even veterans need the documentation   I'll re-read.

                                1 2 3 Previous Next