Home » SQL & PL/SQL » SQL & PL/SQL » How to get sql query output
How to get sql query output [message #403362] Fri, 15 May 2009 01:08 Go to next message
Messages: 78
Registered: March 2006
Location: Dubai
If i ran this statement in sql, i got one result (output )
where <condition>;

Output (Result is) : 50 Rows updated. (which we are seeing in sql window)

My question is, if i ran this update statement inside a procedure, i am not able to know how many rows updated, because conditon may be vary if i run into cursor .

I want to know how can i display/get the output (" 50 Rows updated."). Is there any dbms pkg, which store the result output, so that i can call and display using dbms_output.put_line.
If cursor is running at 10 times i want output 10 times...

Thanks in advance

Re: How to get sql query output [message #403363 is a reply to message #403362] Fri, 15 May 2009 01:13 Go to previous messageGo to next message
Messages: 21148
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL%ROWCOUNT might help:
SQL> begin
  2    update emp set comm = 1000 where deptno = 30;
  3    dbms_output.put_line(sql%rowcount || ' rows updated');
  4  end;
  5  /
6 rows updated

PL/SQL procedure successfully completed.

Re: How to get sql query output [message #403368 is a reply to message #403362] Fri, 15 May 2009 02:52 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The output you see is generated by SQL*Plus, not by the database.
As @Littlefoot demonstrates, the database maintains a set of cursor attributes for each explicit or implicit cursor.

All SQL*Plus does is to take some of these details and give you nicely formatted details about the SQL you just ran.
Previous Topic: Oracle_XMLTYPE
Next Topic: ORA-00932: inconsistent datatypes: expected - got CLOB
Goto Forum:

Current Time: Wed Aug 23 01:03:17 CDT 2017

Total time taken to generate the page: 0.05385 seconds