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

Mutating PARALLEL Operation

Table T1 has parallel parameter set to DEFAULT, so parallel operation was running

INSERT /*+append*/ INTO T1

SELECT * FROM T2

         AND NOT EXISTS (

                        SELECT 'x'

                          FROM T1 inn_t1

                         WHERE T2.id= inn_t1.ih_id

                           );

ORA-12838:	cannot read/modify an object after modifying it in parallel
Cause:	Within the same transaction, an attempt was made to add read or modification statements on a table after it had been modified in parallel or with direct load. This is not permitted.
Action:	Rewrite the transaction, or break it up into two transactions: one containing the initial modification and the second containing the parallel modification operation.

My Solution for this Problem – I removed the /*append*/ hint, because you cannot query a table after direct pathing into it until you commit

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484

Pipelined Functions

1. create object (t_row )
     CREATE TYPE t_row AS OBJECT (
         id           NUMBER,
         name         VARCHAR2(100)
      );

2. create table type (t_tab ) using object created in step1
        --  CREATE TYPE t_tab IS TABLE OF t_row;

3. CREATE FUNCTION fn() RETURN typ_created_above_in_2 PIPELINED and PIPE the ROWS

        CREATE OR REPLACE FUNCTION get_students (p_rows IN NUMBER) RETURN t_tab PIPELINED AS
        BEGIN
          FOR i IN 1 .. p_rows LOOP
            PIPE ROW(t_row(i, 'Student '||i));  
          END LOOP;
          RETURN;
        END;

   P.S. - MUST use a blank RETURN call in the end

4. Test it.
    SELECT *
    FROM   TABLE(get_students(10))
    ORDER BY id DESC;