Normally, in SQL Developer, if one does, let's say, INSERT statement, then there is an output in Script output:
insert into a (a) values (1);
1 rows inserted.
If, then, we compile it into procedure:
create or replace procedure testp as
begin
insert into a (a) values (1);
end;
PROCEDURE TESTP compiled
and make call to it:
execute testp
anonymous block completed
There is no output about how many rows were inserted. How then, can we turn on detailed output in procedure?
If you just want "detailed output" that will be printed out for users running a procedure interactively in an IDE with appropriate settings enabled, you could
create or replace procedure testp as
begin
insert into a (a) values (1);
dbms_output.put_line( sql%rowcount || ' row inserted. ' );
end;
Assuming that the caller is using an application that knows how to display what is written to dbms_output (i.e. an IDE), that they've enabled dbms_output (using the dbms_output window in SQL Developer), that they've allocated a sufficient buffer, etc., the caller will see "1 row inserted" (you could add more code if you want to handle the grammar of singular/ plural programmatically).
Why do you want to have that sort of output though? That usually implies that you should be doing some real logging to a log table of some sort rather than hoping that an interactive user is running your code and seeing the output.
There is no output about how many rows were inserted. How then, can we turn on detailed output in procedure?
SQL and PL/SQL are not same. In PL/SQL, you could use SQL%ROWCOUNT to get the total number of rows affected by the DML.
To get this in the output buffer, use DBMS_OUTPUT.PUT_LINE.
For example,
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows were inserted' );
Make sure you enable DBMS_OUTPUT in your client.
In SQL*Plus, do:
set serveroutput on
Other GUI based client tools have an option to enable it.
I wouldn't suggest DBMS_OUTPUT in production though. You could use it for logging purpose. Like you want to log the number of rows affected by a program on daily basis.
I would suggest that instead of going for dbms_output try to incorporate the OUT parameter and assign SQL%ROWCOUNT to this variable. Keeping dbms_output in prod env is a really bad idea.
create or replace procedure testp
(p_row_cnt_out OUT PLS_INTEGER)
as
begin
insert into a (a) values (1);
p_row_cnt_out:= SQL%ROWCOUNT;
--dbms_output.put_line( sql%rowcount || ' row inserted. ' );
end;