Search code examples
oracle-databaseplsqlstdoutpretty-printpprint

Pretty print in Oracle PL/SQL


Is there a better way to print to STDOUT in Oracle PL/SQL?

DBMS_OUTPUT.PUT_LINE seems very basic and coarse. Should there be something like Python's pprint (Pretty Print)?


Solution

  • PL/SQL is designed to process data, not display it, or interact with the client calling it (so there's no mechanism for user input, for example).

    The DBMS_OUTPUT package "enables you to send messages from stored procedures and packages. The package is especially useful for displaying PL/SQL debugging information". It isn't designed for 'pretty' output because PL/SQL isn't designed for that kind of work. And it's important to realise that the client calling your procedure might not look at or display the DBMS_OUTPUT buffer, so what you write to it could be lost anyway.

    PL/SQL doesn't print anything to stdout; the DBMS_OUTPUT calls write to a buffer - if it's enabled at all - and then once the PL/SQL has finished executing, the client can read that buffer and display the contents somewhere (again if it's enabled). That also means you can't use it to track progress, since you don't see anything during execution, only when it's complete; so even for debugging it's not always the best tool.

    Depending on what you're trying to do you can make things look slightly better in SQL*Plus by having doing set serveroutput on format wrapped, which stops it losing whitespace at the start of buffer lines. But that's usually a minor benefit.

    More generally your procedure should pass the results of its processing to the caller via OUT parameters, or by making it a function that returns something useful.

    If you're currently trying to display the results of a query using dbms_output.put_line then that isn't a good idea. You could instead return a collection or a ref cursor to the client, and allow the client to worry about how to display it. You can easily display a ref cursor in SQL*Plus or SQL Developer using bind variables.