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;