Line break in dbms_output.put_line

Question

I run some Oracle procedure with the help of cursor and get output in logfile via dbms_output.put_line.

What I would like to do is break line on server_name, is it possible with dbms_output.put_line?

currently it list everything together which doesn't look tidy.

Also some server id appears as 1234.9 or 1234.88, is it possible to set numformat like 999.99? somehow I can't do col server_id for 999.99 within procedure.

create procedure proc (vServer IN VARCHAR2, vServerID IN NUMBER)

IS
CURSOR curTable
IS

SELECT server_name, server_id
FROM tab1
WHERE server_name = vServer
and server_id = vServerID;

BEGIN

FOR rec1 IN curTable
LOOP

dbms_output.put_line(rec1.server_name || '   '|| rec1.server_id);

END LOOP;

END proc;

Sample required output:

Is there a reason you're doing this in PL/SQL rather than as a simple SQL commad - with SQL*Plus formatting commands, perhaps? — Alex Poole, Jan 15 '15 at 21:58
I agree and also proposed simple SQL but for multi user access it needed to be in procedure. — homer, Jan 15 '15 at 22:01
Not sure I understand what you mean... But also, you're passing in a single server name and ID, so how will you get different servers in the output? — Alex Poole, Jan 15 '15 at 22:16

score 2 · Accepted Answer · answered Jan 15 '15 at 22:07

2

Doing a break on server_id only makes sense if you specify that column as the sort order. Then, you have to code the break logic yourself. Also, you can use TO_CHAR to format the number as you like.

Here is the code that should do what you want:

create procedure proc (vServer IN VARCHAR2, vServerID IN NUMBER)

IS
CURSOR curTable
IS

SELECT server_name, server_id
FROM tab1
WHERE server_name = vServer
and server_id = vServerID
ORDER BY server_id ;

l_last_server_id tab1.server_id%TYPE := 0;

BEGIN

FOR rec1 IN curTable
LOOP

-- Test for break:
IF last_server_id != rec1.server_id THEN
  -- Break detected:
  dbms_output.put_line('---'); -- DBMS_OUTPUT will not print a blank line.
END IF ;

dbms_output.put_line(rec1.server_name || '   '|| TO_CHAR(rec1.server_id,'999.99');

l_last_server_id := rec1.server_id ;

END LOOP;

END proc;

answered Jan 15 '15 at 22:07

Scott K.

46
1

K, not sure why this is not working for me. I'm providing schemaname.tab1.server and also tried without schemaname but still no luck. User which runs the query can access table tab1 which belongs to another user. `l_last_server_id schemaname.tabl1.sever_id%TYPE := 0;` also is it possible to have lpad used with TO_CHAR ? `to_char(lpad(rec1.server_id, '999.99',7))` – homer Jan 15 '15 at 23:11
Ok it's working now, I was trying to print blank line so it failed initially. How can we use lpad and to_char together? – homer Jan 15 '15 at 23:13
"DBMS_OUTPUT will not print a blank line" isn't correct; the client might suppress it. In SQL*Plus, if you do `set serveroutput on format wrapped` rather than the default `word_wrapped` then you will see the blank line, as well as leading spaces. [Read more](http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#sthref2164). But you can also use `dbms_output.new_line`. – Alex Poole Jan 16 '15 at 11:40
To get blank line we can also use `dbms_output.put(CHR(10));` , thanks Alex P. for format wrapped note. – homer Jan 16 '15 at 15:12

Alex Poole · Answer 2 · 2015-01-15T22:13:09.237

This seems like something you'd do with plain SQL, but assuming this is a PL/SQL exercise, you can use a variable to track the last value seen and add an extra line if it changes:

...
  last_server_name tab1.server_name%type;

BEGIN

  FOR rec1 IN curTable
  LOOP
    if last_server_name is not null
        and rec1.server_name != last_server_name then
      dbms_output.new_line;
    end_if;

    dbms_output.put_line(rec1.server_name
      || '   '|| to_char(rec1.server_id, '99990.00'));

    last_server_name := rec1.server_name;
  END LOOP;

END proc;

You can use to_char() inside the dbms_output call, with whatever format model is suitable - you're doing an implit conversion anyway; or in the cursor if you don't ned the ID as a number within the loop.

Remember that you'll only see the output if your client is set up for it - with set serveroutput on, for example. You can't generally rely on that, so it's not a good idea to use dbms_output in real code for anything except debugging.

And as Scott K. pointed out, you need to order your cursor results for this to work, so add order by server_name to the cursor query. Except, you're only looking for a single server name and ID anyway as you're filtering on vServer and vServerID, so your original query can't produce your original output anyway...

Yes, probably sample query was not the best example when filtering out with vServerName, I do accept different value to list all servers but in order to keep it simple I used ServerName but forgot I'm accepting that with IN parameter. I will test it out with your suggestion. — homer, Jan 15 '15 at 22:18

Line break in dbms_output.put_line

2 Answers2