3 Replies Latest reply on Sep 3, 2016 5:04 PM by Gary Graham-Oracle

    Unable to print multiple output

    user466033

      Created a procedure to output two values but it is printing only the first output and not printing the next output, please check & let me know how to fix this issue. I'm using SQL Developer tool for developing stored procedures in MySQL.

      create procedure output()
      begin
      SELECT '1';
      SELECT '2';
      END;

      call output()

      1
      -
      1

        • 1. Re: Unable to print multiple output
          thatJeffSmith-Oracle

          not sure how many of us here are MySQL folks...you might get more luck on a MySQL board/space

          • 2. Re: Unable to print multiple output
            Gaz in Oz

            Hi

             

            Using the mysql command line, creating and running your code works as expected.

            mysql> delimiter /
            mysql> create procedure output()
                -> begin
                -> SELECT '1';
                -> SELECT '2';
                -> END;
                -> /
            Query OK, 0 rows affected (0.01 sec)
            
            mysql> call output()
                -> /
            +---+
            | 1 |
            +---+
            | 1 |
            +---+
            1 row in set (0.00 sec)
            
            +---+
            | 2 |
            +---+
            | 2 |
            +---+
            1 row in set (0.00 sec)
            
            Query OK, 0 rows affected (0.00 sec)
            
            mysql>
            

             

            I tried the 3 Coonector/J driver versions all with the same result: Within SQL Developer 4.1.3.20 only the first select is output to the "Script Output" tab.

            . mysql-connector-java-6.0.3-bin.jar // development release

            . mysql-connector-java-5.1.39-bin.jar // general release

            . mysql-connector-java-5.0.8-bin.jar // general release

             

            It is likely that your procedure is not being run as you expect within SQLDeveloper. (possible Connector/J,  SQL Developer bug).

             

            Cheers,

             

            Gaz.

            • 3. Re: Unable to print multiple output
              Gary Graham-Oracle

              Of course, third party support is intended for migration to Oracle rather than development on the other DB, but as a workaround you can use...

              create procedure output()

              begin

              SELECT '1'

              UNION

              SELECT '2';

              END;

              This works fine for me on MySQL 5.7 with the 5.1.27 driver -- no special reason, just what I have installed.