Bull's Eye.
Robert, February 03, 2005 - 10:38 am UTC
Exactly what I was looking for... second on my result list. Thanks.
Row count for a SELECT while using Native Dynamic SQL - 9.2.0.6.0
A reader, May 03, 2005 - 11:21 am UTC
Hi Tom,
We have SQL statements stored as strings in a column of a table. We will be writing a cursor to select from the table.column and use the Native Dynamic SQL (execute immediate) command within the cursor loop to execute the SQL query string for each row.
Each SQL query needs to return a count greater than 0 to be able for us to know that the query was successful.
We tried using sql%rowcount, but looks like it does not work for SELECTs. Is there any other way to get around this problem.
Thanks in advance.
May 03, 2005 - 2:27 pm UTC
need simple example of your processing, small and concise
(not sure i like the design but, show me what you do...)
Row count for a SELECT while using Native Dynamic SQL - 9.2.0.6.0
A reader, May 03, 2005 - 4:33 pm UTC
Hi Tom,
Below is an example I specifically created for you to see.
SQL> select *
2 from emp;
EMPNO ENAME DEPTNO HIREDATE
---------- ------------------------------ ---------- ---------
1 JOHN 10 31-DEC-00
2 GARY 10 12-MAY-03
3 MICHAEL 20 28-JUL-00
4 THOMAS 30 14-JUN-00
5 NORMAN 30 18-SEP-03
6 LOUISE 40 02-FEB-05
7 RACHEL 50 20-MAR-01
8 GAVIN 50 12-NOV-04
8 rows selected.
SQL> select *
2 from user_query;
USERID SQL_QRY
---------- --------------------------------------------------
7 select e.empno, e.ename, e.deptno, d.dname, d.loc
from emp e
, dept d
where e.deptno = d.deptno
8 select e.empno, e.ename, e.deptno, d.dname, d.loc
from emp e
, dept d
where e.deptno = d.deptno
and e.deptno = 30
8 select e.empno, e.ename, e.deptno, d.dname, d.loc
from emp e
, dept d
where e.deptno = d.deptno
and e.deptno in (10,20)
7 update emp
set deptno = 20
where empno = 2
8 update emp
set deptno = 30
where empno = 3
7 update emp
set deptno = 20
where empno = 5
SQL> CREATE OR REPLACE PACKAGE qry_tst is
2
3 Procedure testQuery ;
4
5 END qry_tst ;
6 /
SQL> CREATE OR REPLACE PACKAGE BODY qry_tst is
2
3 PROCEDURE testQuery is
4 cnt pls_integer := 0 ;
5 Begin
6 for rec in (select sql_qry
7 from user_query ) loop
8 cnt := cnt + 1 ;
9 execute immediate rec.sql_qry ;
10 dbms_output.put_line('Executed Row '||cnt||', '||'rows affected -> '||sql%rowcount) ;
11 commit ;
12 end loop ;
13 END testQuery ;
14
15 END qry_tst ;
16 /
SQL> set serveroutput on;
SQL> exec qry_tst.testQuery;
Executed Row 1, rows affected -> 0
Executed Row 2, rows affected -> 0
Executed Row 3, rows affected -> 0
Executed Row 4, rows affected -> 1
Executed Row 5, rows affected -> 1
Executed Row 6, rows affected -> 1
PL/SQL procedure successfully completed.
In our case, we DO NOT have UPDATEs, we only have SELECT scenarios. However, just created the UPDATE scenario here because, I wanted to show that the SQL%ROWCOUNT works for UPDATEs but not SELECTs.
Thanks for you help!
May 03, 2005 - 7:12 pm UTC
execute a select basically "does nothing". it doesn't even need to actually run the query.
since no rows are fetched and no rows are "affected", why does this "matter"?
the statement was parsed, the statement was executed (opened) but you never fetched anything anywhere so it said "so what"
begin
execute immediate 'select * from dual';
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 0 1
********************************************************************************
select *
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Row count for a SELECT while using Native Dynamic SQL - 9.2.0.6.0
A reader, May 04, 2005 - 11:44 am UTC
Thanks for your reply Tom.
I completely understand that since no rows are fetched, no rows are "affected". Actually, instead of saying "the query was successful" in my initial question above, I should have said "the query returned atleast one row"
So in this case, I guess, we will have to somehow fetch the query found on each row to find the number of row(s) each one returns.
Thanks again!
May 04, 2005 - 1:43 pm UTC
I hope you are not counting rows to see if you should update them?
If so, you should just update and then see if you did any updates. no point in running a query to update, just update.
Johny Alex, October 17, 2007 - 4:14 am UTC
Create table test_case (col1 varchar2(10));
set serveroutput on
declare
l_sql varchar2(1000);
begin
l_sql := 'insert into test_case (col1) select ''1'' from dual union all select ''2'' from dual ';
execute immediate l_sql;
-- 1st output comes here
dbms_output.put_line('Rows = '||TO_CHAR(SQL%ROWCOUNT));
execute immediate 'BEGIN '||l_sql||'; END;';
-- second output comes here
dbms_output.put_line('PLSQL Rows = '||TO_CHAR(SQL%ROWCOUNT));
end;
/
Tom,
Based on the above scenario, why did my second output display 1? Which is not correct. I was expecting 2.
Any explanation would be appreciated.
Thanks in advance
Johny Alex