OPEN-FETCH-BULK COLLECT-CLOSE

===> One of the Right Methods-

OPEN C1;
LOOP
    EXIT WHEN C1%NOTFOUND;
    FETCH C1 BULK COLLECT INTO C1_TBL LIMIT 100;
    <>
END LOOP;
CLOSE C1;

===> One of Many Wrong Methods-

OPEN C1;
LOOP
    FETCH C1 BULK COLLECT INTO C1_TBL LIMIT 100;
    EXIT WHEN C1%NOTFOUND;
    <>
END LOOP;
CLOSE C1;

Problem is, In first method, when I have fetched the CURSOR, it moves ahead and when it will pass last record, then NOTFOUND will evaluate to true. So if I do exit after fetching like I am doing in 2nd method, then I wont be able to iterate through the loop in last execution. I will always miss this last cycle.

Making EXIT as the first statement as in first method above, I moved EXIT statement before the FETCH. Now next fetch in loop will give me back remaining rows and I can iterate through the loop this last time and will also move the cursor to EOF so that in next cycle I can EXIT the loop..

PL/SQL Ada a Love

PL/SQL is derived from Ada, which was originally designed for US Department of Defense by "Ada  Lovelace". Main idea to use Ada was to capitalize on Idea of STANDARD package, so that needs not be used with dot notation every time we want to use it. Oracle adopted it and created two default packages STANDARD and DBMS_STANDRD and these are the only standard packages available in Oracle, even users cannot define their own default standard packages unlike Ada.

DEFINE vs VARAIBLE

DEFINE is just a replacement of Strings and VARIABLE actually crates a bind variable -

SQL>DEFINE X='DEFINE X HERE';

SQL> BEGIN :X:='SET X HERE'; END;
/

SQL> SELECT :X,'&X' FROM DUAL ;
old   1: SELECT :X,'&X' FROM DUAL
new   1: SELECT :X,'DEFINE THE X HERE' FROM DUAL

:X 'DEFINETHEXHERE'
----------- -----------------
SET X HERE DEFINE THE X HERE

&X simply replace the String, see below, we have omitted the single quotes around &X and it errors out, because it simply replaces &X with string which SQL cannot parse.

SQL> SELECT &X , :X FROM DUAL ;
old   1: SELECT &X , :X FROM DUAL
new   1: SELECT DEFINE THE X HERE , :X FROM DUAL
SELECT DEFINE THE X HERE , :X FROM DUAL
                  *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected