9 Replies Latest reply on Nov 21, 2017 8:12 PM by rp0428

    DBMS_OUTPUT on 10g does not seem to work in 17.x versions

    Pavel_p

      Hello,

      I'm not able to get any dbms_output.put_line messages in 17.x versions when I'm connected to 10.2g instance (I know, long time not unsupported), however on >=11.2g it works like a charm. In versions prior to 17.x everything worked as expected (just verified on 4.2) but in 17.x I cannot get any dbms_output messages. It's also not related to any custom settings because I tried with the clean installation with the very same (no) result.

      Please, is there any way to get dbms_output on 10.2 (like enable some legacy settings...whatever)?

      Thanks a lot,

      Pavel

        • 1. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions
          thatJeffSmith-Oracle

          Duplicate thread.

           

          You have two options:

          1. upgrade your database
          2. downgrade your sql dev
          1 person found this helpful
          • 2. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions
            Gaz in Oz

            Please, is there any way to get dbms_output on 10.2 (like enable some legacy settings...whatever)?

            Yes.

             

            10g is no longer supported.

            Read this

            SQL Developer 17.2 doesn't show DBMS Output

            and this

            DBMS_OUTPUT DOES NOT WORKS

            1 person found this helpful
            • 3. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions
              Mike Kutz

              SQL*Developer 17.x is toggling the DBMS_OUTPUT package to cache the results.  Its not reading the buffer.

               

              You need to create a package to read (and clear) the cache if you want to stick with 17.x.

               

              I've included an example Package code in a prior response somewhere in this forum.

               

              MK

              1 person found this helpful
              • 4. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions
                Gary Graham-Oracle

                That must be in your next-to-last post in the discussion Jeff references as a duplicate thread above...

                Re: DBMS_OUTPUT DOES NOT WORKS

                1 person found this helpful
                • 5. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions
                  Pavel_p

                  Guys, thank you all for your suggestions, however my options (either to upgrade the DB or stay with 4.2.6) are quite obvious from the original post. Unfortunately it's a legacy system that is going to be replaced soon, so my only option is to stay with 4.2.6, which is in fact not that bad as it does pretty much everything I need (I'll be missing the significantly improved code completion and formatting though).

                  I'm just curious what had changed between 4.2.6 and 17.x because both versions come with the same ojdbc8.jar jdbc driver and if I check the Database=>Advanced=>Use Oracle Client, both versions use the same 12.1 client, so it seems that something must have changed internally in SQL Developer rather than in the driver.

                  • 6. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions
                    Pavel_p

                    Mike, please, could you elaborate a bit more on this "SQL*Developer 17.x is toggling the DBMS_OUTPUT package to cache the results. Its not reading the buffer."? What exactly has happened?

                    • 7. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions
                      Mike Kutz

                      JDBC version

                      "ojdbc8.jar" does not make sense.

                       

                      Oracle v8 clients can't connect to 11g

                      And I believe SQL*Developer 4.x used Java 7

                       

                      My statement - Quick Answer

                      SQL*Developer 17.x is toggling the DBMS_OUTPUT package to cache the results.  (ie it does make the call to DBMS_OUTPUT.ENABLE)

                      Its not reading the buffer. (ie it does not make a call to DBMS_OUTPUT.GET_LINES)

                       

                       

                      Long Answer

                      In order to use DBMS_OUTPUT package, somebody has to enable the package by calling DBMS_OUTPUT.ENABLE().

                       

                      Applications like SQL*Plus and SQL*Developer will interpret the command "SET SERVEROUTPUT ON" to mean "EXEC DBMS_OUTPUT.ENABLE()"

                       

                      Behind the scenes, when you call DBMS_OUTPUT.PUT_LINE, you are actually storing the value in a package variable (most likely of type SYS.DBMS_OUTPUT.dbmsoutput_linesarray);

                       

                      To "display to the screen", somebody has to make a call to retrieve the data from that package variable,  (DBMS_OUTPUT.GET_LINES) then send it to "STDOUT".

                      Per documentation, every time you make this call, the package variable is cleared.

                       

                      If you run the code

                      set serveroutput on
                      exec dbms_output.put_line('hello world');
                      

                       

                      You would expect to have the Package variable cleared after each time you run the script.

                       

                      However, according to my pipeline function, the call to DBMS_OUTPUT.GET_LINES() is never made from the application (because the data is never cleared).

                       

                      MK

                      1 person found this helpful
                      • 8. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions
                        Pavel_p

                        I don't know if it makes sense or not (I would expect ojdbc7 as well with 4.2), however as you can see, both versions come with the same jdbc driver.

                        sqldev_jdbc.jpg

                        I'm also not sure what exactly means "v8 clients", but I just verified that I'm able to connect with the above thin driver to 10.2 EE, 11.2 XE and 12.2 EE (developer VM) without any problems.

                        In order to use DBMS_OUTPUT package, somebody has to enable the package by calling DBMS_OUTPUT.ENABLE().

                        +

                        To "display to the screen", somebody has to make a call to retrieve the data from that package variable,  (DBMS_OUTPUT.GET_LINES) then send it to "STDOUT".

                        Yes, but if I'm not mistaken, the same "mechanics" can be applied to all 10, 11 and 12 db versions, so I'm just spinning my head why SQL Developer gets it from 11 and 12 while 10.2 not.

                        • 9. Re: DBMS_OUTPUT on 10g does not seem to work in 17.x versions

                          JDBC version

                          "ojdbc8.jar" does not make sense.

                           

                          Oracle v8 clients can't connect to 11g

                          And I believe SQL*Developer 4.x used Java 7

                          What 'Oracle v8 client' are you talking about?

                           

                          I think you are confusing Oracle, Java and JDBC.

                           

                          The JDBC jar file is NOT an 'Oracle v8 client'.