Cursor Expression

SET SERVEROUTPUT ON
DECLARE
  TYPE ref_emp_cursor IS REF CURSOR;
  emp_cur ref_emp_cursor;
  l_deptno  dept.deptno%TYPE;
  l_ename    emp.ename%TYPE;
  
  CURSOR c1 IS SELECT deptno, 
         CURSOR(SELECT e.ename FROM emp e 
         WHERE e.deptno = d.deptno) 
         FROM dept d where d.dname like 'A%'; 
BEGIN
   OPEN c1;
   LOOP
    FETCH c1 INTO  l_deptno, emp_cur;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line('Department No '||l_deptno);
    dbms_output.put_line('========================');
    LOOP
      FETCH emp_cur INTO l_ename;
      EXIT WHEN emp_cur%NOTFOUND;
      dbms_output.put_line(emp_cur%ROWCOUNT||' Employee name is '||l_ename);
    END LOOP;
   END LOOP;
   CLOSE c1;
END;
/
Department No 10
========================
1 Employee name is CLARK
2 Employee name is KING
3 Employee name is MILLER       

Weak Ref Cursor

DECLARE
     rcur_emp  SYS_REFCURSOR;
     PROCEDURE process_emp_cur(rcur_emp IN SYS_REFCURSOR) IS
       l_emp emp%ROWTYPE;
     BEGIN
        dbms_output.put_line('Emp table details');
        loop
            fetch rcur_emp into l_emp;
            exit when rcur_emp%notfound;
            dbms_output.put_line('Empno : '|| l_emp.empno);
            dbms_output.put_line('Ename : '|| l_emp.ename);
        end loop;
     END;
BEGIN
  OPEN rcur_emp FOR select * from emp where rownum < 2;
  process_emp_cur (rcur_emp);
  CLOSE rcur_emp;
END;

Weak Ref Cursor

DECLARE

    TYPE  REFCUR_EMP_TYPE IS REF CURSOR;

     rcur_emp  REFCUR_EMP_TYPE;

     PROCEDURE process_emp_cur(rcur_emp IN REFCUR_EMP_TYPE) IS

       l_emp emp%ROWTYPE;

     BEGIN

        dbms_output.put_line('Emp table details');

        loop

            fetch rcur_emp into l_emp;

            exit when rcur_emp%notfound;

            dbms_output.put_line('Empno : '|| l_emp.empno);

            dbms_output.put_line('Ename : '|| l_emp.ename);

        end loop;

     END;

BEGIN

  OPEN rcur_emp FOR select * from emp where rownum < 2;

  process_emp_cur (rcur_emp);

  CLOSE rcur_emp;

END;

Strong Ref cursor

DECLARE
    TYPE  REFCUR_EMP_TYPE IS REF CURSOR RETURN  emp%ROWTYPE;
     rcur_emp  REFCUR_EMP_TYPE;


     PROCEDURE process_emp_cur(rcur_emp IN REFCUR_EMP_TYPE) IS
       l_emp emp%ROWTYPE;
     BEGIN
        dbms_output.put_line('Emp table details'); 
        loop
            fetch rcur_emp into l_emp;
            exit when rcur_emp%notfound;
            dbms_output.put_line('Empno : '|| l_emp.empno);
            dbms_output.put_line('Ename : '|| l_emp.ename);
            
        End loop;
     END;
BEGIN
  OPEN rcur_emp FOR select * from emp where rownum < 2;
  process_emp_cur (rcur_emp);
  CLOSE rcur_emp;  
END;

Dynamic SQL - Execute Immediate & Bulk Collection

DECLARE
  TYPE emp_ref_cursor IS REF CURSOR;
  TYPE empno_tab IS TABLE OF NUMBER;
  TYPE ename_tab IS TABLE OF VARCHAR2(50);
 
  v_emp_cur  emp_ref_cursor;
  vc_empno   empno_tab;
  vc_sal     empno_tab;
  vc_ename   ename_tab;
  vc_ename1  ename_tab;
  vc_ename2  ename_tab;
  v_sql_stmt VARCHAR2(500);
  v_bonus    CONSTANT NUMBER := 500;
  a_null CHAR(1);
BEGIN
  -- example of ref cursor with bulk collect
  v_sql_stmt := 'SELECT empno, ename FROM emp_t WHERE deptno = 10';
  OPEN v_emp_cur FOR v_sql_stmt;
  FETCH v_emp_cur BULK COLLECT INTO vc_empno,vc_ename;
  CLOSE v_emp_cur;
 
  -- example of dynamic sql with bull collect
  v_sql_stmt := 'SELECT sal FROM emp_t';
  EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO vc_sal;
 
  -- example of dynamic sql with returning bulk collect
  v_sql_stmt := 'UPDATE emp_t SET sal = sal + :1
                 RETURNING ename INTO :2';
  EXECUTE IMMEDIATE v_sql_stmt
              USING v_bonus RETURNING BULK COLLECT INTO vc_ename1;
 
  --dynamic sql inside forall statement
  v_sql_stmt := 'UPDATE emp_t SET sal = sal - :1
                WHERE empno = :2 RETURNING ENAME INTO :3';
  FORALL i in vc_empno.first..vc_empno.last
     EXECUTE IMMEDIATE v_sql_stmt USING v_bonus, vc_empno(i)
                      RETURNING BULK COLLECT INTO vc_ename2;
    
  FOR i in vc_ename2.first..vc_ename2.last LOOP
     dbms_output.put_line(vc_ename2(i));
  END LOOP;
 
  -- passing null to dynamic sql
  EXECUTE IMMEDIATE 'UPDATE emp_t SET comm = :1' USING a_null;
  ROLLBACK;
 
EXCEPTION
 WHEN OTHERS THEN
  dbms_output.put_line('When others exception raised '|| sqlerrm);
END;
 

Dynamic SQL - Execute Immediate -that accept table name and where clause

CREATE OR REPLACE PROCEDURE delete_rows ( 

             v_table_name USER_OBJECTS.OBJECT_NAME%TYPE,
             v_where_clause VARCHAR2 ) 

IS
   l_table_name    USER_OBJECTS.OBJECT_NAME%TYPE;
   l_sql_stmt VARCHAR2(2000);
   l_count PLS_INTEGER;
BEGIN
   SELECT object_name INTO l_table_name
       FROM user_objects
       WHERE object_type = 'TABLE'
       AND object_name = UPPER(v_table_name);

   l_sql_stmt := 'DELETE FROM '|| l_table_name || ' WHERE '
                 ||v_where_clause;
   EXECUTE IMMEDIATE l_sql_stmt;
   l_count := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE (l_count||' Record(s) from '

         ||v_table_name ||' table has been deleted');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Table Name');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Other exception raised '||sqlerrm);
END;

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2   delete_rows('Emp_t','empno = 7654');
  3  end;
  4  /

1 Record(s) from Emp_t table has been deleted

PL/SQL procedure successfully completed

Dynamic SQL - Execute Immediate -1

CREATE OR REPLACE PROCEDURE raise_emp_sal
       (v_emp_column_name VARCHAR2,
        v_column_value NUMBER, v_amount NUMBER) IS

  l_sql_stmt VARCHAR2(2000);
BEGIN
  
  l_sql_stmt :='UPDATE emp_t SET sal = sal + :1'||
               'WHERE '|| v_emp_column_name || '= :3';
  EXECUTE IMMEDIATE l_sql_stmt USING v_amount,v_column_value;

  IF SQL%ROWCOUNT > 0 THEN
     DBMS_OUTPUT.PUT_LINE('Salary has been updated for '

              ||v_emp_column_name || ' = '||v_column_value||

              ' by '||v_amount||' Rupees');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Invalid column name '

                         ||v_emp_column_name);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Others exception raised '|| sqlerrm);
END;

SQL> SET SERVEROUTPUT ON
SQL>
SQL> BEGIN
  2    RAISE_EMP_SAL('EMPNO',7654,1200);
  3    RAISE_EMP_SAL('DEPTNO',20,100);
  4    COMMIT;
  5  END;
  6  /

Salary has been updated for EMPNO = 7654 by 1200 Rupees
Salary has been updated for DEPTNO = 20 by 100 Rupees

PL/SQL procedure successfully completed

Oracle Raise an error message when more than one match found in function overloading

SET SERVEROUTPUT ON

DECLARE
     FUNCTION getArea(v_radious NUMBER, v_precision NUMBER) RETURN NUMBER IS
     l_pi NUMBER := 3.14;
     BEGIN
          RETURN TRUNC((l_pi *(v_radious ** 2)),v_precision);
     END;
     FUNCTION getArea(v_length NUMBER ,v_width NUMBER) RETURN NUMBER IS
     BEGIN
          RETURN(v_length * v_width); 
     END;
BEGIN
     DBMS_OUTPUT.PUT_LINE('Area of Circle is '|| getArea(6.4, 2));
     DBMS_OUTPUT.PUT_LINE('Area of Rectangle is '|| getArea( 6, 4));
END;

/

ORA-06550: line 14, column 50:
PLS-00307: too many declarations of 'GETAREA' match this call
ORA-06550: line 14, column 6:
PL/SQL: Statement ignored
ORA-06550: line 15, column 53:
PLS-00307: too many declarations of 'GETAREA' match this call
ORA-06550: line 15, column 6:
PL/SQL: Statement ignored

Function overloading by datatype of parameters

SET SERVEROUTPUT ON

DECLARE
     FUNCTION getArea(v_radious NUMBER, v_precision NUMBER) RETURN NUMBER IS
     l_pi NUMBER := 3.14;
     BEGIN
          RETURN TRUNC((l_pi *(v_radious ** 2)),v_precision);
     END;
     FUNCTION getArea(v_radious NUMBER ,v_ignore CHAR) RETURN NUMBER IS
     BEGIN
          IF v_ignore = 'Y' THEN
             RETURN 0;
          ELSE
             RETURN (3.14 *(v_radious ** 2));
          END IF;
     END;
BEGIN
     DBMS_OUTPUT.PUT_LINE('Area of Circle is '|| getArea(6.4, 2));
     DBMS_OUTPUT.PUT_LINE('Area of Rectangle is '|| getArea(6,'N'));
END;

Function overloading by name of parameters

SET SERVEROUTPUT ON

DECLARE
     FUNCTION getArea(v_radious NUMBER, v_precision NUMBER) RETURN NUMBER IS
     l_pi NUMBER := 3.14;
     BEGIN
          RETURN TRUNC((l_pi *(v_radious ** 2)),v_precision);
     END;
     FUNCTION getArea(v_length NUMBER ,v_width NUMBER) RETURN NUMBER IS
     BEGIN
          RETURN(v_length * v_width);  
     END;
BEGIN
     DBMS_OUTPUT.PUT_LINE('Area of Circle is '|| 

                getArea(v_radious =>6.4, v_precision => 2));
     DBMS_OUTPUT.PUT_LINE('Area of Rectangle is '|| 

                getArea(v_length => 6,v_width => 4));
END;

Function overloading by number of parameters

SET SERVEROUTPUT ON

DECLARE
     FUNCTION getArea(v_radious NUMBER) RETURN NUMBER IS
     l_pi NUMBER := 3.14;
     BEGIN
          RETURN (l_pi *(v_radious ** 2));
     END;
     FUNCTION getArea(v_length NUMBER ,v_width NUMBER) RETURN NUMBER IS
     BEGIN
          RETURN(v_length * v_width);  
     END;
BEGIN
     DBMS_OUTPUT.PUT_LINE('Area of Circle is '|| getArea(6));
     DBMS_OUTPUT.PUT_LINE('Area of Rectangle is '|| getArea(6,4));
END;

FORALL with Non-Consecutive Index Values

CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 2;
CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 2 = 3;

DECLARE
-- Make collection to hold the customer name and order amount
   TYPE cust_name_t IS TABLE OF valid_orders.cust_name%TYPE;
   TYPE order_amount_t is TABLE OF valid_orders.amount%TYPE;
  
   cust_name_tab       cust_name_t;
   order_amount_tab    order_amount_t;
-- Make another collection to point into cust_name_tab collection
   TYPE pointer_t IS TABLE OF PLS_INTEGER;
   rejected_order_tab  pointer_t := pointer_t();
   big_order_tab       pointer_t := pointer_t();   
  
   PROCEDURE setup_data is
   BEGIN
         cust_name_tab    :=  cust_name_t('Customer1','Customer2','Customer3','Customer4','Customer5');
         order_amount_tab :=  order_amount_t(500.0,0,1000,2000,NULL);
   END;
BEGIN
   setup_data();
   --  Point all big orders from main collection

   --  Delete invalid records from the main collection
   --  Point all invalid orders from main collection
   FOR i IN cust_name_tab.FIRST..cust_name_tab.LAST
   LOOP
     IF order_amount_tab(i) > 1000 THEN
         big_order_tab.EXTEND;
         big_order_tab(big_order_tab.LAST) := i;
      END IF;
     IF order_amount_tab(i) = 0 OR order_amount_tab(i) IS NULL THEN
         cust_name_tab.delete(i);
         order_amount_tab.delete(i);
         rejected_order_tab.EXTEND;
         rejected_order_tab(rejected_order_tab.LAST) := i;
      END IF; 
    
   END LOOP;  

   FORALL i IN INDICES OF cust_name_tab
       INSERT INTO valid_orders VALUES(cust_name_tab(i),order_amount_tab(i));
   setup_data();
  
   FORALL i in VALUES OF rejected_order_tab
         INSERT INTO rejected_orders VALUES(cust_name_tab(i),order_amount_tab(i));
 
   FORALL i in VALUES OF big_order_tab
         INSERT INTO big_orders VALUES(cust_name_tab(i),order_amount_tab(i));
    COMMIT;
END;