CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
griboedov
griboedov

Reputation: 886

Getting output in PLSQL procedure

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?

Upvotes: 2

Views: 1392

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

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;

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 3

Related Questions