3 Replies Latest reply: May 28, 2013 10:53 AM by Jeff Smith Sqldev Pm-Oracle RSS

    How to create PL/SQL output identical to SQL query?

    1010848
      Hi!

      When I run a SQL query in SQL Developer, the result is displayed in a "query window". But output via PL/SQL is usually done via dbms_output. Which appears in a different window ("dbms output") and I do need to take care about formatting the output properly myself. If the PL/SQL code just creates the content of a database, it should be possible to use the same output facility as an ordinary SQL query. So that the output of the PL/SQL code appears in the query window. But how?

      As a simple example: If I have this PL/SQL code
      declare
        i integer;
      begin
        dbms_output.put_line('i square');
        for i in 0..9 loop
          dbms_output.put_line(i || ' ' || i*i);
        end loop;
      end;
      /
      How to get the resulting table not shown in the "dbms output" window, but in the "query result" window?

      Any pointer?
        • 1. Re: How to create PL/SQL output identical to SQL query?
          Jeff Smith Sqldev Pm-Oracle
          I understand what you want, however I don't think we have an answer that will give you what you want.

          PL/SQL blocks aren't SQL - you're not going to get a resultset back like what if you had run a query in SQL*Plus or SQL Developer.

          HOWEVER, if you have your program return the resultset as a REFCURSOR - SQL Developer will 'catch' that output and place it in a 'grid.'

          You're other option is to output the format to file or to the output buffer however which way you want - but that's code you're going to have to write yourself.

          Here's an example of what REFCURSOR output looks like in SQL Developer

          http://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/
          • 2. Re: How to create PL/SQL output identical to SQL query?
            rp0428
            Welcome to the forum!

            Whenever you post provide your full sql developer version.
            >
            But output via PL/SQL is usually done via dbms_output.
            >
            No - it isn't.

            The DBMS_OUTPUT package should NOT be used at all in production code. You should only enable and use it very sparingly and only then for debugging purposes when developing and testing your code.

            A line like this:
                dbms_output.put_line(i || ' ' || i*i);
            does nothing more than put text into a buffer in Oracle and uses expensive PGA memory to store it. There is a small default limit on the size of that buffer and you run the risk of an exception if you fill it up.

            The client software (sql*plus, sql developer) has to actually access that buffer to get the text or it will NEVER get displayed. And it normally only gets displayed AFTER the completion of the code, not during the execution.

            If you are using DBMS_OUTPUT as a general-purpose 'display' mechanism then you are misusing it and should find another alternative.
            >
            How to get the resulting table not shown in the "dbms output" window, but in the "query result" window?
            >
            There is no 'table' and there is no 'query result'. If you want a query result put the results into a table or use a REF CURSOR to return them.
            • 3. Re: How to create PL/SQL output identical to SQL query?
              Jeff Smith Sqldev Pm-Oracle
              No - it isn't.
              Good catch. Although unfortunately too many folks do rely on this. And of course it's never in production. Sigh.