10 Replies Latest reply on Feb 26, 2018 5:49 PM by rp0428

    Could DBMS_OUTPUT cause this code to fail?

    dbassco

      Hello,

      I have inherited a script that I'm trying to touble shoot, .. excerpt below. It seems to be failing at the END IF; statement.  So I've been looking around and I'm wondering if there is supposed to be a server setting for DBMS_OUTPUT ???? Make sense ???

       

      Thanks!

       

      --------------------------  Start Cursor Loop -------------------------------

      BEGIN

      dbms_output.enable(10000000);

      OPEN emp_rec_cur;

      v_total_count :=0;

      LOOP

      FETCH emp_rec_cur INTO get_emp_rec;

      BEGIN EXIT WHEN emp_rec_cur%NOTFOUND;

      IF emp_rec_cur%FOUND

      THEN v_total_count:=v_total_count+1;

      -- This line to write data to file

      DBMS_OUTPUT.PUT_LINE(get_emp_rec.emp_ssn||','||get_emp_rec.spriden_pidm||','||

      get_emp_rec.last_name|| ',' || get_emp_rec.pyrpdtemp_plan|| ',' ||

      get_emp_rec.pyrpdtemp_description1|| ',' ||get_emp_rec.pyrpdtemp_description2|| ',' ||

      get_emp_rec.employer_amt|| ',' ||get_emp_rec.employee_amt|| ',' );

      END IF;

      END;

      END LOOP;

      CLOSE emp_rec_cur;

      --- dbms_output.put_line('RECORDCOUNT,'||v_total_count);

      END;

      /spool off

      quit;

        • 1. Re: Could DBMS_OUTPUT cause this code to fail?
          Frank Kulash

          hI,

              I don't see any obvious errors in the code you posted.   Of course, I can't run it myself.  Can you make a simpler example (using the scott.emp table, perhaps) that people on this forum can run to re-create the problem and test their ideas?

           

              Why do you think it's failing at the END IF?  Are you getting an error message?  If so, post the complete error message.

           

              Are you running this in SQL*Plus?  If so, you need to say   SET SERVEROUTPUT ON   at some point in the session before running your PL/SQL code.  Otherwise, you won't see any output.

           

              Does dbms_output.put_line work for you under any corcumstances?  Can you see the output from

          BEGIN
              dbms_output.enable (10000000);  -- not needed in SQL*Plus
              dbms_output.put_line ('Hello, world!');
          END;

          ?

          • 2. Re: Could DBMS_OUTPUT cause this code to fail?
            jaramill

            dbassco wrote:

             

            Hello,

            I have inherited a script that I'm trying to touble shoot, .. excerpt below. It seems to be failing at the END IF; statement. So I've been looking around and I'm wondering if there is supposed to be a server setting for DBMS_OUTPUT ???? Make sense ???

             

            Thanks!

             

            Don't post an excerpt of the code....post ALL OF THE CODE including the error you received.

            You don't show the CURSOR declaration so we can't see that.  We see you're calling this SQL script outside of the database, so what is your environment?  What database version.

             

            Please read this thread on --> Re: 2. How do I ask a question on the forums?

            • 3. Re: Could DBMS_OUTPUT cause this code to fail?

              It seems to be failing at the END IF; statement.

              That should suggest to you to put BEGIN ... EXCEPTION ... END blocks around EVERY LINE OF CODE to identify EXACTLY what line is failing.

                So I've been looking around and I'm wondering if there is supposed to be a server setting for DBMS_OUTPUT ???? Make sense ???

              No - that does NOT make sense. Please explain why you think it makes sense and what a 'server setting' has to do with anything.

               

              Also - please explain the

              LOOP

              FETCH emp_rec_cur INTO get_emp_rec;

              BEGIN EXIT WHEN emp_rec_cur%NOTFOUND;

              IF emp_rec_cur%FOUND

              First you say to exit on a NOTFOUND condition.

               

              Then the first statement INSIDE the loop tests to see if a FOUND condition exists.

               

              If the loop exits on a NOT found then how could a 'FOUND' test inside the loop possibly fail?

              • 4. Re: Could DBMS_OUTPUT cause this code to fail?
                John Thorton

                dbassco wrote:

                 

                Hello,

                I have inherited a script that I'm trying to touble shoot, .. excerpt below. It seems to be failing at the END IF; statement. So I've been looking around and I'm wondering if there is supposed to be a server setting for DBMS_OUTPUT ???? Make sense ???

                 

                Thanks!

                 

                -------------------------- Start Cursor Loop -------------------------------

                BEGIN

                dbms_output.enable(10000000);

                OPEN emp_rec_cur;

                v_total_count :=0;

                LOOP

                FETCH emp_rec_cur INTO get_emp_rec;

                BEGIN EXIT WHEN emp_rec_cur%NOTFOUND;

                IF emp_rec_cur%FOUND

                THEN v_total_count:=v_total_count+1;

                -- This line to write data to file

                DBMS_OUTPUT.PUT_LINE(get_emp_rec.emp_ssn||','||get_emp_rec.spriden_pidm||','||

                get_emp_rec.last_name|| ',' || get_emp_rec.pyrpdtemp_plan|| ',' ||

                get_emp_rec.pyrpdtemp_description1|| ',' ||get_emp_rec.pyrpdtemp_description2|| ',' ||

                get_emp_rec.employer_amt|| ',' ||get_emp_rec.employee_amt|| ',' );

                END IF;

                END;

                END LOOP;

                CLOSE emp_rec_cur;

                --- dbms_output.put_line('RECORDCOUNT,'||v_total_count);

                END;

                /spool off

                quit;

                I am befuddled why you wasted your time & our time by posting your question here,

                when you could have quickly answered it yourself by simply commenting out the DBMS_OUTPUT statement & retested.

                If you get/got the same error without the DBMS_OUTPUT statement, then the root cause is some other line of code.

                • 5. Re: Could DBMS_OUTPUT cause this code to fail?
                  Zlatko Sirotic

                  You have not provided the complete code and you did not give us a error code.

                  That's why we can only speculate. Eg. concatenation may be too large for DBMS_OUTPUT.PUT_LINE.

                   

                  Note: It is a good rule to close the (explicit) cursor in the EXCEPTION section - in your case, if an error occurs inside the loop, the CLOSE behind the loop will not be executed.

                   

                  Regards,

                  Zlatko

                  • 6. Re: Could DBMS_OUTPUT cause this code to fail?
                    AndrewSayer

                    This whole guess and grimace approach you are opting for here is utterly crazy. If it's not to do with that line, are you just going to comment out another line and keep going until it works? What happens if the failure is dependant on multiple things? 

                     

                    It can be extremely trivial to troubleshoot code that fails

                     

                    step 1) read the error message

                    step 2) understand the error message

                    step 3) adjust code so that condition which raised the error message doesn't happen, or accept it must legitimately error.

                     

                    Considering you haven't even posted the error you are getting perhaps you have fallen into a trap of either

                     

                    a) Not bothering to read the error message

                    b) Not posting the real code that you are running, the real code swallows the error message

                    c) You haven't run the code

                    d) The code doesn't error, it just doesn't do what you expected it to do

                    • 7. Re: Could DBMS_OUTPUT cause this code to fail?
                      BEDE

                      The classic error with dbms_output sounds like "ORU... buffer overflow". That is quite inevitable when there is lots of data and the calls to dbms_output.put or put_line are so many that you would need more than 1000000chars. Another one would be that what you wish to put or put_line is longer than what may fit in one line, the maximum length of a line being 255chars.

                      That is why I think dbms_output should generally be used only for some debugging and should be disabled when code is deployed in production.

                      • 8. Re: Could DBMS_OUTPUT cause this code to fail?
                        BluShadow

                        BEDE wrote:

                         

                        The classic error with dbms_output sounds like "ORU... buffer overflow". That is quite inevitable when there is lots of data and the calls to dbms_output.put or put_line are so many that you would need more than 1000000chars. Another one would be that what you wish to put or put_line is longer than what may fit in one line, the maximum length of a line being 255chars.

                        That is why I think dbms_output should generally be used only for some debugging and should be disabled when code is deployed in production.

                         

                         

                        Absolutely.  DBMS_OUTPUT shouldn't be considered appropriate for production code.  If logging is needed within production code, it should be done via a more appropriate means i.e. an autonomous transaction procedure writing to a log in a table... which also has the advantage that the data in that log can be viewed "during" the process from another session, unlike dbms_output.

                         

                        We have a community document about dbms_output, which discusses it in detail.... PL/SQL 101 - DBMS_OUTPUT  

                        • 9. Re: Could DBMS_OUTPUT cause this code to fail?
                          Cookiemonster76

                          rp0428 wrote:

                           

                          It seems to be failing at the END IF; statement.

                          That should suggest to you to put BEGIN ... EXCEPTION ... END blocks around EVERY LINE OF CODE to identify EXACTLY what line is failing.

                          Why would you do that when oracles default error handling will specify the problem line number anyway?

                          • 10. Re: Could DBMS_OUTPUT cause this code to fail?

                            Why would you do that when oracles default error handling will specify the problem line number anyway?

                            In particular because said this:

                            I have inherited a script that I'm trying to touble shoot

                            Scripts, anonymous blocks and code executed from a tool (sql developer, toad, etc) can have comments, whitespace and other content that is a 'line' as far as the tool is concerned but is NOT a 'line' to Oracle.

                             

                            1. Oracle reports ONE line number but complex queries can spread MANY lines.

                            2. The line number Oracle reports doesn't always match the line number in your code like you might think it will.

                             

                            I have found that 'newbies', especially with scripts, can't always associate the line number Oracle reports with the actual line of code.

                             

                            For similar reasons I usually train people how to use 'binary search' (when possible) to find problems in code. Comment out half the code and test if the problem still occurs.

                             

                            If not the problem is in the half you did NOT test so split that other half in two and test one half. Repeat until you have found the code with the problem.

                             

                            Not always necessary to use such an extreme approach but it does teach people how to methodically troubleshoot a problem.