1 2 Previous Next 15 Replies Latest reply on Jan 23, 2018 5:50 PM by Gary Graham-Oracle

    triggers don't show messages

    ziutek

      have two triggers, but they don't work properly. They don't show messages. when i try to insert into jobs table, rest of the code work fine.

      When I insert something messages don't show, but when I use dbms_output.put_line command to show something it shows missing messages and message that it suppose to show. It's like messages from triggers wait until some new message appears.

       

      create or replace trigger custom

        before insert on jobs

        for each row

        declare

        stara_nazwa varchar2(50);

        begin

        dbms_output.put_line('wstawion wiersz do tabeli jobs');

        stara_nazwa:=:new.job_title;

        :new.job_title:=upper(:new.job_title);

        dbms_output.put_line('stara nazwa ' || stara_nazwa || ' zmieniono na ' || :new.job_title);

        end;

       

        create or replace trigger custom3

        before insert on jobs

        for each row

        when (new.max_salary>10000)

        begin

        dbms_output.put_line('wstawion kasiaste stanowisko');

       

        end;

      When i execute

      insert into jobs values('aaa', 'bbbb', 12000, 15000);

      I get nothing, then I execute

      begin

      dbms_output.put_line('xyz');

      end;

      I get

      wstawion kasiaste stanowisko

      wstawion wiersz do tabeli jobs

      stara nazwa bbbb zmieniono na BBBB

      xyz

      I use sql developer.

        • 1. Re: triggers don't show messages
          John Thorton

          Trigger code is a shared resource & does not belong to any single session.

          When trigger is invoked there is no terminal associated with it.

           

          BTW, DBMS_OUTPUT should never exist in Production PL/SQL code.

          • 2. Re: triggers don't show messages
            Gaz in Oz

            sqlplus:

            SQL> insert into jobs values('aaa', 'bbbb', 12000, 15000);
            
            1 row created.
            
            SQL> set serverout on
            SQL> insert into jobs values('aaa', 'bbbb', 12000, 15000);
            wstawion kasiaste stanowisko
            wstawion wiersz do tabeli jobs
            stara nazwa aaa zmieniono na AAA
            
            1 row created.
            
            SQL>
            

            Line 05.

            • 3. Re: triggers don't show messages

              Oracle doesn't 'display' anything.

               

              When you use DBMS_OUT all it does is put that data into a buffer. Keep calling it and it keeps putting more data into that buffer.

               

              Until your client code takes the data out of that buffer the buffer will just keep getting fuller.

              I use sql developer.

              Well - there you go. By default DBMS_OUTPUT is NOT enabled.

               

              Go to the 'View' menu and select DBMS OUTPUT.

               

              Then in the DBMS OUTPUT pane that gets displayed click the PLUS button and select the connection you want to enable output for.

               

              If you need more help move your question to the Sql Developer forum.

              SQL Developer

              • 4. Re: triggers don't show messages
                ziutek

                rp0428 napisał(-a):

                Go to the 'View' menu and select DBMS OUTPUT.

                 

                Then in the DBMS OUTPUT pane that gets displayed click the PLUS button and select the connection you want to enable output for.

                 

                If you need more help move your question to the Sql Developer forum.

                SQL Developer

                I've done that before I ask this question.

                set serverout on doesn't work.

                PS It's not production code or any work related code.

                • 5. Re: triggers don't show messages
                  Gary Graham-Oracle

                  It seems that the DBMS_OUTPUT buffer is not being flushed, the same as in...

                  DBMS_OUTPUT not work in 17.3.1 with Call statement

                   

                  The issue with CALL not flushing the buffer has been fixed, but cannot recall if that made it into the 17.4 release.

                  Do you use 17.4?

                  • 6. Re: triggers don't show messages
                    ziutek

                    I use Version 17.3.1.279

                    • 7. Re: triggers don't show messages
                      Gary Graham-Oracle

                      So, in fact, the CALL fix did not make it into 17.4.  However, even in the 18.1 code base, that fix does nothing for your TRIGGER case.  Probably need to flush the buffer after every DML statement? 

                       

                      Best to log a Service Request with My Oracle Support.

                      • 8. Re: triggers don't show messages

                        So, in fact, the CALL fix did not make it into 17.4. However, even in the 18.1 code base, that fix does nothing for your TRIGGER case. Probably need to flush the buffer after every DML statement?

                         

                        Must be a regression bug since it all works fine in  Version 4.2.0.17.089 Build 17.089.1709

                        • 9. Re: triggers don't show messages
                          Gary Graham-Oracle

                          That must be why I logged a bug directly in that discussion rather than suggesting the poster open a SR. But do you mean the TRIGGER case works in 4.2 also?  I did not test in that release.

                          • 10. Re: triggers don't show messages

                            But do you mean the TRIGGER case works in 4.2 also? I did not test in that release.

                            Yep.

                            • 11. Re: triggers don't show messages
                              Gary Graham-Oracle

                              Thanks.  Confirmed regression.  However, upon reviewing comments in the previously noted bug (CALL DBMS_OUTPUT.PUT_LINE SHOULD FLUSH BUFFER ON EACH LINE LIKE EXEC), this comment appears...

                              For example we do not check dbms_output buffer on insert for performance reasons - even though a insert trigger could run arbitrary plsql including dbms_output

                              So on the one hand, as Gaz in Oz shows, this all works fine in SQL*Plus, but after 4.2 someone (the developer?) decided to tweak performance by eliminating the buffer check.  Perhaps the overhead is much higher in SQL Developer as compared to SQL*Plus?

                               

                              Bug logged so that this decision can be properly reviewed.

                              • 12. Re: triggers don't show messages

                                Thanks. Confirmed regression. However, upon reviewing comments in the previously noted bug (CALL DBMS_OUTPUT.PUT_LINE SHOULD FLUSH BUFFER ON EACH LINE LIKE EXEC), this comment appears...

                                For example we do not check dbms_output buffer on insert for performance reasons - even though a insert trigger could run arbitrary plsql including dbms_output

                                So on the one hand, as Gaz in Oz shows, this all works fine in SQL*Plus, but after 4.2 someone (the developer?) decided to tweak performance by eliminating the buffer check. Perhaps the overhead is much higher in SQL Developer as compared to SQL*Plus?

                                 

                                Bug logged so that this decision can be properly reviewed.

                                 

                                Definitely something that needs to be reviewed and an intentional design decision made about how to deal with it.

                                 

                                Some of the criteria (IMHO) for consideration:

                                 

                                1. A client tool should NOT be able to circumvent a developer's intent that wants messages displayed - regardless of any purported performance, or other, issues.

                                 

                                If a developer uses/intends/wants DBMS_OUTPUT it is assumed that there will be a performance and memory impact.

                                 

                                2. The comment you quoted above conflicts with the Oracle's DB documentation as to ENABLE/DISABLE

                                https://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS67302 

                                You should generally avoid having application code invoke either the DISABLE Procedure or ENABLE Procedure because this could subvert the attempt of an external tool like SQL*Plus to control whether or not to display output.

                                That leaves the developer at the mercy of the client tool to provide a mechanism for them to control the ENABLE/DISABLE of the output. The developer at least needs to know what rules the tool is going to use to make those decisions.

                                Probably need to flush the buffer after every DML statement?

                                3. There is no way for a developer to actually 'flush' the buffer as the paragraph after the one quoted above says

                                Note:

                                Messages sent using DBMS_OUTPUT

                                are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.

                                 

                                But, as noted in #1 and #2, if DBMS_OUTPUT is enabled in sql developer then the output should be shown - not just flushed to the bit bucket. If the developer has enabled the feature it should be assumed by the tool that they are willing to accept the performance/other consequences.

                                 

                                Especially since the general recommendation is to NOT USE/INCLUDE dbms_output calls in production code. I know of multiple requests over the years that I and others have made to Oracle to enhance the DBMS_OUTPUT mechanism to allow LOGGING of the output to a standard table (simliar to the tables that DML error logging and EXPLAIN PLAN use).

                                 

                                That would be pretty easy to do and could be done with AUTONOMOUS transaction code. Never had any response from Oracle on those suggestions.

                                 

                                Maybe Sql Dev needs to add MORE PREFERENCES? (mostly, but not entirely, tongue in cheek).

                                 

                                1. allow multiple named SETS of preferences to be stored/used

                                2. similar to how multiple named connections are handled

                                3. user selects which preference set to use and can select one of them as DEFAULT

                                 

                                That way developers could create one set of preferences for 'production', a different set for 'test', 'dev', etc.

                                 

                                Since that may not be enough work for the team how about adding a 'preference' dropdown on the connection dialog.

                                 

                                That way the preferences being used could be different for different connections.

                                 

                                After all it's a new year so it's not like your dev team is overloaded with more useful stuff to do. I'm assuming they closed out all of that backlog during the year-end cleanup! LOL!

                                • 13. Re: triggers don't show messages
                                  Gary Graham-Oracle

                                  Opinions always welcome ;-)

                                   

                                  Anyway, for the matter at hand, I think it unlikely this performance decision will be reversed.  The motivation came from an effort to reduce APEX install time.  It has a ton of INSERT statements and this extra processing required an extra DB round trip for each one.

                                   

                                  So one workaround would be to create a simple procedure similar to

                                  create or replace PROCEDURE ForceDbmsOutput AS
                                    BEGIN
                                    /* NOOP */ NULL;
                                    END ForceDbmsOutput;
                                  

                                  and exec that whenever you expect output but none is forthcoming.

                                   

                                  Edit:

                                  And to your point about preferences, the tendency is to avoid new preferences to the extent possible and have reasonable defaults for the existing ones.

                                  So if we had a preference (or a worksheet/command line set variable) to control the check for DBMS_OUTPUT, it might well default to FALSE / OFF.

                                  • 14. Re: triggers don't show messages

                                    Anyway, for the matter at hand, I think it unlikely this performance decision will be reversed. The motivation came from an effort to reduce APEX install time. It has a ton of INSERT statements and this extra processing required an extra DB round trip for each one.

                                    I don't work with APEX son I won't pretend to understand:

                                     

                                    1. what an 'APEX install' involves

                                    2. why that install is related to usage of Sql developer

                                    3. who is doing the install

                                    4. why that 'who' would even care if DBMS_OUTPUT was enabled or not if they were doing an APEX install.

                                    5. why an APEX install would involved user written code (functions, procedures, packages, triggers) that might have DBMS_OUTPUT statement in it.

                                     

                                    So to my 'apex naive' way of thinking it seems the solution would be for Sql Dev to just disable DBMS_OUTPUT before the install begun. .

                                     

                                    No need to comment on the above - sounds like you have it covered.

                                    1 2 Previous Next