Posts

Showing posts from 2014

Problems in: FETCH FIRST n PERCENT ROWS ONLY

Wanting to count how many rows in emp table

SQL> SELECT COUNT (*) FROM emp;

COUNT(*)
----------
100

Cool... now let me see how much is 5 percent of emp

SQL> SELECT COUNT (*) FROM emp
2 FETCH FIRST 5 PERCENT ROWS ONLY;

COUNT(*)
----------
100
Mmmmm.... it can't be also 100 ... somthing is wierd ..
Let's try other way

SQL> SELECT COUNT (*)
2 FROM ( SELECT empno
3 FROM emp
4 FETCH FIRST 5 PERCENT ROWS ONLY);

COUNT(*)
----------
5
Cool, now it is working :)
Now let's try in PL/SQL

SQL> DECLARE
2 l_Percent_to_fetch PLS_INTEGER;
3 l_cnt PLS_INTEGER;
4 BEGIN
5 SELECT COUNT (*)
6 INTO l_cnt
7 FROM ( SELECT empno
8 FROM emp
9 FETCH FIRST l_Percent_to_fetch PERCENT ROWS ONLY);
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4480
Session ID: 196 Serial number:…