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;