DBMS_OUTPUT.NEW_LINE and DBMS_OUTPUT.NEW_LINE() difference?

Vineet picture Vineet · Apr 7, 2010 · Viewed 12.4k times · Source

What is the difference between these two statements?

dbms_output.new_line(); // with no parameters.
dbms_output.new_line;    // with no parameters,no round brackets

If there is function overloading,even for that close and open brackets are required after function name.

Answer

APC picture APC · Apr 7, 2010

Well the difference is that the first formulation fails and the second one succeeds:

SQL> begin
  2      dbms_output.put_line('some text');
  3      dbms_output.put('about to new_line with no parameters');
  4      dbms_output.new_line;
  5  end;
  6  /
some text
about to new_line with no parameters

PL/SQL procedure successfully completed.

SQL> begin
  2      dbms_output.put_line('some text');
  3      dbms_output.put('about to new_line with a parameter');
  4      dbms_output.new_line('');
  5  end;
  6  /
    dbms_output.new_line('');
    *
ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00306: wrong number or types of arguments in call to 'NEW_LINE'
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored


SQL>

edit

What does work is the empty brackets...

SQL> begin
  2      dbms_output.put_line('some text');
  3      dbms_output.put('about to new_line with a parameter');
  4      dbms_output.new_line();
  5  end;
  6  /
some text
about to new_line with a parameter

PL/SQL procedure successfully completed.

SQL>

I don't know when Oracle actually started supprting this convention but I only became aware of it when they introduced the OO stuff. Some member functions (i.e. methods) on Types won't work unless we include the empty brackets e.g. XMLType's getClobVal(). But the brackets are strictly optional for the standard procedural calls.