COLLECTION Vs CURSOR

COLLECTION Vs CURSOR

Example 1 (COLLECTION)

SET SERVEROUTPUT ON
DECLARE
    type ty_varchar_list is table of VARCHAR2(30);
lv_name_list   ty_varchar_list := ty_varchar_list ();
BEGIN
    SELECT ename
    BULK COLLECT INTO lv_name_list
    FROM emp
    WHERE deptno = 10;

    FOR i IN lv_name_list.first..lv_name_list.last LOOP
        dbms_output.put_line('lv_name_list ('||i||')= '||lv_name_list(i) );
    END LOOP;

dbms_output.put_line('Third Name = '||lv_name_list(3) );
dbms_output.put_line('First Name = '||lv_name_list(1) );
dbms_output.put_line('Second Name = '||lv_name_list(2) );
END;
/

----------------------------------------------------------------------------------------------------------------

Example 2 (CURSOR)

SET SERVEROUTPUT ON

DECLARE
    lv_name_list  sys_refcursor;
    lv_name       varchar2(30);
BEGIN
    get_emp_list_as_refcur(10,lv_name_list);
    LOOP
        FETCH lv_name_list into lv_name;
        EXIT WHEN lv_name_list%notfound;
        dbms_output.put_line('lv_name = '||lv_name);
    END LOOP;
        
END loop;
/   

----------------------------------------------------------------------------------------------------------------

Example 3 : (COLLECTION)

create or replace type ty_varchar_list is table of VARCHAR2(30);
/

CREATE OR REPLACE PROCEDURE get_emp_list_as_coll 
                              (pin_deptno     IN  NUMBER,
                               pout_name_list OUT ty_varchar_list) AS
    lv_varchar_list   ty_varchar_list := ty_varchar_list ();
BEGIN
    SELECT ename
    BULK COLLECT INTO lv_varchar_list
    FROM emp
    WHERE deptno = pin_deptno;

pout_name_list := lv_varchar_list;
END;
/

SET SERVEROUTPUT ON

DECLARE
    lv_name_list   ty_varchar_list := ty_varchar_list();
BEGIN
    get_emp_list_as_coll(10,lv_name_list);
    FOR i IN lv_name_list.first..lv_name_list.last LOOP
        dbms_output.put_line(lv_name_list(i) );
    END LOOP;
END loop;
/


Python Script to call the procedure

import cx_Oracle as orcl
con = orcl.connect('DEVUSER2/devuser2@ORCLPDB')  
dbObjType = con.gettype("TY_VARCHAR_LIST") 

pyobj = dbObjType.newobject()
cur.callproc("get_emp_list_as_coll", (10,pyobj,)) # Call the Procedure
cur.close() # Close the Cursor

con.close() # Close the Connection
print("Printing the values of collection as list:")
print(pyobj.aslist())

----------------------------------------------------------------------------------------------------------------

Example 4: (CUROSR)

CREATE OR REPLACE PROCEDURE get_emp_list_as_refcur 
                (pin_deptno NUMBER,pout_name_list OUT sys_refcursor) AS
BEGIN
    open pout_name_list for SELECT ename FROM emp 
                            WHERE deptno = pin_deptno;
END;
/


SET SERVEROUTPUT ON

DECLARE
    lv_name_list  sys_refcursor;
    lv_name       varchar2(30);
BEGIN
    get_emp_list_as_refcur(10,lv_name_list);
    LOOP
        FETCH lv_name_list into lv_name;
        EXIT WHEN lv_name_list%notfound;
        dbms_output.put_line('lv_name = '||lv_name);
    END LOOP;
        
END loop;
/


Python Script to call the procedure

import cx_Oracle as orcl
con = orcl.connect('DEVUSER2/devuser2@ORCLPDB')
cur = con.cursor()
refcur = con.cursor()
cur.callproc("get_emp_list_as_refcur", (10,refcur,))
print("Printing the values from REFCURSOR:")
for row in refcur:
    print(row)
cur.close()  # Close the Cursor

con.close()  # Close the Connection

----------------------------------------------------------------------------------------------------------------