Pretty print in Oracle PL/SQL

Question

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)?

"Should there", or "is there"? You're welcome to write your own procedures. — mason, May 23 '16 at 17:26
where are you using DBMS_OUTPUT, in a sql*plus report? otherwise, get the text, and format it in your app code — thatjeffsmith, May 23 '16 at 17:31

score 5 · Accepted Answer · edited May 23 '17 at 12:13

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.

Nice lecture. I just need pretty print. Python is designed to process data, not to display it. Still they have pprint. — olekb, May 23 '16 at 19:06
But Python is designed to be interactive as well; PL/SQL is not. Perhaps another distinction is that PL/SQL runs in the server, inside the database, not in a client. The client - or a scheduled job, or another bit of PL/SQL - asks the server to execute the code. — Alex Poole, May 23 '16 at 19:18

score 2 · Answer 2 · answered May 23 '16 at 21:00

Pretty enough?

 --Pretty print
CREATE OR REPLACE PROCEDURE PP(input varchar2, p_pn varchar2:='PP')
AS
   pos   INTEGER;
   len   INTEGER      := 4000;
   nl    VARCHAR2 (2) := CHR (10);
   r_    VARCHAR2 (2) := CHR (13);
   v_padded varchar2(64):=p_pn;--rpad(p_pn, 5, ' ');
BEGIN
   IF LENGTH (input) > len
   THEN
      pos := INSTR (input, nl, 1, 1);
      IF pos > 0 AND pos < len
      THEN
         DBMS_OUTPUT.put_line (v_padded||': '||REPLACE (SUBSTR (input, 1, pos - 1), r_, ''));
         pp (SUBSTR (input, pos + 1),p_pn);
      ELSE
         IF pos = 0 AND LENGTH (input) <= len
         THEN
  DBMS_OUTPUT.put_line (v_padded||': '||REPLACE (SUBSTR (input, 1, len), r_, ''));
         ELSE
  DBMS_OUTPUT.put_line (v_padded||': '||REPLACE (SUBSTR (input, 1, len), r_, ''));
  pp (SUBSTR (input, len + 1),p_pn);
         END IF;
      END IF;
   ELSE
      DBMS_OUTPUT.put_line (v_padded||': '||REPLACE (SUBSTR (input, 1, len), r_, ''));
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END PP;

Sample output:

SQL>
FUN_ENCRYPT_PERSON_ID: Input  = 123456789123
FUN_ENCRYPT_PERSON_ID: Suffix = 123
FUN_ENCRYPT_PERSON_ID: Base   = 876543210
FUN_ENCRYPT_PERSON_ID: 2->5 1 10000     10000
FUN_ENCRYPT_PERSON_ID: 3->9 2 200000000 200010000
FUN_ENCRYPT_PERSON_ID: 4->2 3 30        200010030
FUN_ENCRYPT_PERSON_ID: 5->1 4 4         200010034
FUN_ENCRYPT_PERSON_ID: 6->3 5 500       200010534
FUN_ENCRYPT_PERSON_ID: 7->6 6 600000    200610534
FUN_ENCRYPT_PERSON_ID: 8->4 7 7000      200617534
FUN_ENCRYPT_PERSON_ID: 9->8 8 80000000  280617534
FUN_ENCRYPT_PERSON_ID: Output = 280617534123

score 1 · Answer 3 · answered May 23 '16 at 20:28

There is not a builtin that I'm aware of. But I wrote a proc to pretty-print a resultset from within a stored procedure.

The proc is called print_out_resultset. Example usage:

....
cursor mycur (mynum in number) is
select * from mytable where mycol >= mynum; 

myrow mytable%rowtype;
myvar number;
...
begin
....

print_out_resultset(query_string =>
  'select * from mytable where mycol >= :b1',
  col1 => 'SAM', col2 => 'FRED', col3 => 'ERIC',
  b1 => myvar,
  maxrows => 4);

open mycur(myvar);
fetch mycur into myrow;
while mycur%found loop
....

example result

SQL> set serverout on;
SQL> execute myproc;
SAM         FRED        ERIC       
-----------------------------------
32A         49B         15C
34A         11B         99C
11F         99A         887
77E         88J         976

the code is here: http://toolkit.rdbms-insight.com/print_out_resultset.php

I used this only for troubleshooting, many years ago; test before deploying in production of course

score 1 · Answer 4 · answered May 24 '16 at 08:54

Actually there is a standard approach, but it works well only with varchar variables:

declare
  v_str varchar2(100):= 'formatted';
  v_int int := 10 ;
begin
  dbms_output.put_line(
     UTL_LMS.FORMAT_MESSAGE('Somebody told that "%s" output works well with ' 
                            ||'varchar variables, but not with int variables: "%d". '
                            ||'Only with constant integers (%d) it works.'
                            , v_str, v_int, 100)                            
  );
end;

score 1 · Answer 5 · answered Apr 08 '20 at 20:42

If you are trying to print json object, there is a better way to print using the JSON_UTIL_PKG and JSON_PRINTER.

  L_JSON_LIST         JSON_LIST;
  L_JSON              JSON;
  L_CLOB              CLOB;
BEGIN
  --JSON_UTIL_PKG.SQL_TO_JSON returns a list, so storing it in a JSON_LIST.
  L_JSON_LIST := JSON_UTIL_PKG.SQL_TO_JSON('SELECT
                                      ''abc'' "Address1",
                                      ''def'' "Address2",
                                      ''gh'' "City"
                                     FROM
                                       DUAL');
  DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);                            
  --get the first item in the list
  L_JSON :=       JSON(L_JSON_LIST.GET(1));
  --It takes a json object and will return it in a clob.
  JSON_PRINTER.PRETTY_PRINT(OBJ => L_JSON, BUF => L_CLOB); 

  --output  
  DBMS_OUTPUT.PUT_LINE(L_CLOB);                                    
END;

And, the output looks like the following,

{
  "Address1" : "abc"
,
  "Address2" : "def"
,
  "City" : "gh"

}

score 0 · Answer 6 · answered Apr 10 '20 at 21:09

0

Maybe you are looking for fnd_file function

fnd_file.put_line(fnd_file.output,:message);

where :message is the value u want to print.

answered Apr 10 '20 at 21:09

Eduardo Sly

1
1

Looks like fnd_file is a PL/SQL package just available in the [Oracle E-Business Suite](https://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T458258.htm). – Mark Stewart Apr 10 '20 at 21:43

Pretty print in Oracle PL/SQL

6 Answers6