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
----------------------------------------------------------------------------------------------------------------
