Siva Academy: 2019

Saturday, December 14, 2019

query to find the consecutive numbers

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

Data Setup

create table t(c number);

insert into t values(6);

insert into t values(10);

insert into t values(11);

insert into t values(12);

insert into t values(57);

insert into t values(77);

insert into t values(98);

insert into t values(99);

insert into t values(100);

insert into t values(120);

insert into t values(125);

commit;

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

Method 1

select c

from (

select c,

lag(c,1) over(order by c) lag,

lead(c,1) over(order by c) lead

from t)

where c=lag+1 or c=lead-1;

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

Method 2

select c from (

select c,count(1) over(partition by c-rownum) g

from t ) where g > 1;

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

Method 3

select c.c

from t c, t prev

where c.c = prev.c +1

union

select c.c

from t c, t next

where c.c = next.c -1;

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

Saturday, November 16, 2019

Examples on SQL to find group range of Sequence Numbers

drop table t;

create table t(c number);

insert into t values(1);

insert into t values(2);

insert into t values(3);

insert into t values(5);

insert into t values(7);

insert into t values(8);

insert into t values(9);

insert into t values(11);

insert into t values(12);

insert into t values(15);

insert into t values(16);

insert into t values(20);

commit;

select min(c) start_range,max(c) end_range

from t

group by c-rownum

order by 1;

create table t(START_RANGE number, end_range number);

Insert into t (START_RANGE,END_RANGE) values (1,3);

Insert into T (START_RANGE,END_RANGE) values (5,5);

Insert into T (START_RANGE,END_RANGE) values (7,9);

Insert into T (START_RANGE,END_RANGE) values (11,12);

Insert into T (START_RANGE,END_RANGE) values (15,16);

Insert into T (START_RANGE,END_RANGE) values (20,20);

SELECT start_range,

end_range,

r-1 seq,

start_range+r-1 result

FROM t,

lateral (select rownum r

from dual

connect by level <= (end_range-start_range)+1);

select start_range,end_range,r

from t,

(select rownum r

from

dual connect by level <=(

select max(end_range)

from t)) d

where start_range <= r and end_range >=r;

Below Answers Received from subscribers.... Thanks for answering :-)

SELECT DISTINCT (LEVEL + START_RANGE)-1 NUM

FROM T

CONNECT BY (LEVEL +START_RANGE ) <= END_RANGE+1

ORDER BY NUM;

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

create table cars (
name varchar2(100),
start_year number,
end_year number);

insert into cars values('Swift',2007,2009);
insert into cars values('Swift',2009,2011);
insert into cars values('Swift',2011,2013);
insert into cars values('Maruti',2009,2011);
insert into cars values('Maruti',2013,2015);

commit;

with r as (select distinct name,start_year+r year
from cars,
lateral(select rownum-1 r
from dual connect by level <=end_year-start_year+1)
order by 1,2)
select name,min(year) s_year,max(year) e_year
from r
group by name,year-rownum;

Saturday, September 14, 2019

Expand and Fill the missing dates with last not null value

Input and Expected Output:

Data Setup:

create table tx_data(dt date, amnt number);

insert into tx_data values(to_date('20-JUL-2019','DD-MON-YYYY'),100);
insert into tx_data values(to_date('25-JUL-2019','DD-MON-YYYY'),200);
insert into tx_data values(to_date('30-JUL-2019','DD-MON-YYYY'),300);

commit;

Query:

with d1 as (
select min(dt) mi,max(dt) mx, max(dt)-min(dt) num
from tx_data),
dt_range as (select level-1+mi dt
from d1
connect by level <=num+1)
select dt_range.dt,
last_value(amnt ignore nulls) over(order by dt_range.dt) amnt
from dt_range left outer join tx_data
on (dt_range.dt = tx_data.dt)

order by 1;

Wednesday, September 11, 2019

sql to get combination of all the given values

create table t1(c varchar2(1));

insert into t1 values('A');
insert into t1 values('B');
insert into t1 values('C');

create table t2(c varchar2(1));

insert into t2 values('D');
insert into t2 values('E');
insert into t2 values('F');

commit;

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

with t as (select c from t1 union select c from t2)
select a1.c||'-'||a2.c output
from t a1, t a2
where a1.c < a2.c;

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

with t as (select c from t1 union select c from t2)
select distinct least(a1.c,a2.c), greatest(a1.c,a2.c) output
from t a1, t a2
where a1.c <> a2.c
order by 1;

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

Sunday, September 8, 2019

oracle compound trigger

What is compound trigger?

A compound trigger combines the following triggers into one single trigger:

Before statement trigger
Before row trigger
After row trigger
After statement trigger

Advantages of “Compound trigger”?

Consolidating the individual triggers into single compound trigger
Global variables to share across timing events
Effectively load the changes to another table using bulk collect
To avoid mutating table error

CREATE OR REPLACE TRIGGER emp_comp_trigger
FOR INSERT OR UPDATE OR DELETE ON EMP COMPOUND TRIGGER

g_variable VARCHAR2(50) := 'DEMO ON COMPOUND TRIGGER';

BEFORE STATEMENT IS
l_variable VARCHAR2(50) := ' BEFORE STATEMENT ';
BEGIN
dbms_output.put_line(l_variable||g_variable);
END BEFORE STATEMENT;

BEFORE EACH ROW IS
l_variable VARCHAR2(50) := ' BEFORE EACH ROW ';
BEGIN
dbms_output.put_line(l_variable||g_variable);
END BEFORE EACH ROW;

AFTER EACH ROW IS
l_variable VARCHAR2(50) := ' AFTER EACH ROW ';
BEGIN
dbms_output.put_line(l_variable||g_variable);
END AFTER EACH ROW;

AFTER STATEMENT IS
l_variable VARCHAR2(50) := ' AFTER STATEMENT ';
BEGIN
dbms_output.put_line(l_variable||g_variable);
END AFTER STATEMENT;

END emp_comp_trigger;
/

CREATE OR REPLACE TRIGGER emp_comp_trigger
FOR INSERT OR UPDATE OR DELETE ON EMP COMPOUND TRIGGER

g_variable VARCHAR2(50) := 'DEMO ON COMPOUND TRIGGER';

BEFORE STATEMENT IS
l_variable VARCHAR2(50) := ' BEFORE STATEMENT ';
BEGIN
if INSERTING then
dbms_output.put_line(l_variable||g_variable||' INSERT ');
elsif UPDATING then
dbms_output.put_line(l_variable||g_variable||' UPDATE ');
elsif DELETING then
dbms_output.put_line(l_variable||g_variable||' DELETE ');
end if;
END BEFORE STATEMENT;

END emp_comp_trigger;
/

Monday, August 12, 2019

Grade based on employee salary

Grade based on employee salary(Subscriber Comments Reply Video 20 Grade based on employee salary)

CREATE TABLE emp_t (
empno number,
ename varchar2(100),
sal number
);

insert into emp_t values (1,'KING',100);
insert into emp_t values (2,'BLAKE',500);
insert into emp_t values (3,'CLARK',1200);
insert into emp_t values (4,'JONES',2500);
insert into emp_t values (5,'SCOTT',3000);
insert into emp_t values (6,'FORD',700);
insert into emp_t values (7,'SMITH',1700);
insert into emp_t values (8,'ALLEN',2600);
insert into emp_t values (9,'WARD',400);
insert into emp_t values (10,'MARTIN',1500);
commit;

----------------------------------------------------------------
select EMPNO, ENAME, SAL,
case when sal >0 and sal <=1000 then 'A'
when sal >1000 and sal <=2000 then 'B'
else 'C' end grade
from emp_t;
----------------------------------------------------------------

select EMPNO, ENAME, SAL,
decode(ceil(SAL/1000),1,'A',2,'B','C')
from emp_t;
----------------------------------------------------------------
select EMPNO, ENAME, SAL,r.g
from emp_t,(select 0 mi,1000 mx,'A' g from dual
union
select 1001 mi,2000 mx,'B' g from dual
union
select 2001 mi,999999 mx,'C' g from dual) R
where sal >=mi and sal <=mx
order by 1;
----------------------------------------------------------------

Find the last two recharge amount for each customer

Find the last two recharge amount for each customer ( Subscriber Comments Reply Video 19 Get the last two recharged amount)

Data Setup Scripts:

CREATE TABLE recharge_detail (
recharge_date DATE,
cust_name VARCHAR2(100),
recharge_amount NUMBER
);

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Ragu',100);
insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Ragu',150);
insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Ragu',120);
insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Ragu',170);

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Ravi',299);
insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Ravi',399);
insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Ravi',150);
insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Ravi',199);

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Siva',100);
insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Siva',200);
insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Siva',400);
insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Siva',200);

commit;

-------------------------------------------------------------------------------------------------------------
select RECHARGE_DATE, CUST_NAME, RECHARGE_AMOUNT,
decode(rank() over(partition by cust_name order by Recharge_Date desc ),1,RECHARGE_AMOUNT,2,RECHARGE_AMOUNT) last_two_recharge_amount
from recharge_detail
order by CUST_NAME,RECHARGE_DATE;
-------------------------------------------------------------------------------------------------------------
select RECHARGE_DATE, CUST_NAME, RECHARGE_AMOUNT,
case when rank() over(partition by cust_name order by Recharge_Date desc ) in(1,2) then RECHARGE_AMOUNT end
from recharge_detail
order by CUST_NAME,RECHARGE_DATE;
-------------------------------------------------------------------------------------------------------------
SELECT
cust_name,
MAX(DECODE(r,1,recharge_amount) ) last_recharge,
MAX(DECODE(r,2,recharge_amount) ) last_2nd_recharge
FROM
(
SELECT
cust_name,
recharge_amount,
RANK() OVER(PARTITION BY cust_name ORDER BY recharge_date DESC ) r
FROM
recharge_detail
)
GROUP BY
cust_name;

Monday, July 8, 2019

Insert two rows from one table into single row of another table

create table emp1(empno number, ename varchar2(100));

create table emp2(empno1 number, ename1 varchar2(100),
empno2 number, ename2 varchar2(100));

insert into emp1 values(1,'KING');
insert into emp1 values(2,'BLAKE');
insert into emp1 values(3,'CLARK');
insert into emp1 values(4,'JONES');
insert into emp1 values(5,'SCOTT');
insert into emp1 values(6,'FORD');
insert into emp1 values(7,'SMITH');
insert into emp1 values(8,'ALLEN');
insert into emp1 values(9,'WARD');
insert into emp1 values(10,'MARTIN');
insert into emp1 values(11,'SIVA');

commit;

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

insert into emp2
with d as (select empno,ename,
round(count(1) over()/2) - (row_number() over(order by empno)) r
from emp1),
d1 as (select empno,ename,r
from d
where r >=0),
d2 as (select empno,ename,abs(r) r
from d
where r <0)
select d1.empno,d1.ename,d2.empno,d2.ename
from d1 full outer join d2
on (d1.r = d2.r)
order by d1.r desc;

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

insert into emp2
with d as (select empno,ename,
round(count(1) over()/2) - (row_number() over(order by empno)) r
from emp1),
d1 as (select empno,ename,r
from d
where r >=0),
d2 as (select empno,ename,abs(r) r
from d
where r <0)
select d1.empno,d1.ename,d2.empno,d2.ename
from d1 left outer join d2
on (d1.r = d2.r)
order by d1.r desc;

Saturday, June 22, 2019

Reverse the String without using reverse function

WITH t AS (
SELECT 'WELCOME TO SQL CLASS' d
FROM dual
) SELECT LISTAGG(s) WITHIN GROUP(ORDER BY l DESC)
FROM
(
SELECT level l,substr(d,level,1) s
FROM t
CONNECT BY level <= length(d)
);

Sort the NUMBERS in the comma separated list

select regexp_substr('A,BB,CCC,DDDD','[^,]+',1,1) from dual;
select regexp_substr('A,BB,CCC,DDDD','[^,]+',1,2) from dual;
select regexp_substr('A,BB,CCC,DDDD','[^,]+',1,3) from dual;

select regexp_substr('A,BB,CCC,DDDD','[^,]+',1,4) from dual;

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

WITH t AS (
SELECT '3,10,5,1,7,55,75,100,42,12,17' d
FROM dual
)
SELECT LISTAGG(s,',') WITHIN GROUP(
ORDER BY to_number(s))
FROM(
SELECT regexp_substr(d,'[^,]+',1,level) s
FROM t
CONNECT BY level <= regexp_count(d,',') + 1
);

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

WITH t AS (
SELECT '1 5 3 6 2 4 7 99 10 76 101 55 15 33 18' d
FROM dual
) SELECT LISTAGG(s,' ') WITHIN GROUP(ORDER BY to_number(rtrim(ltrim(s) ) ))
FROM(
SELECT regexp_substr(regexp_replace(d,'\D+',','),'[^,]+',1,level) s
FROM t
CONNECT BY level <= regexp_count(regexp_replace(d,'\D+',','),',') + 1
);

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

WITH t AS (
SELECT 'WELCOME' d
FROM dual
) SELECT LISTAGG(d) WITHIN GROUP( ORDER BY d )
FROM(
SELECT substr(d,level,1) d
FROM t

CONNECT BY level <= length(d));

Wednesday, June 19, 2019

SQL to find list of combinations where the third dice roll equal to sum of first and second dice roll

Write a SQL to find the list of combinations, where the third rolled dice value is equal to sum of first and second rolled value

with t as (
select level r
from dual
connect by level <=6)
select t1.r ROLL1,t2.r ROLL2,t1.r + t2.r ROLL3
from t t1,t t2
where t1.r + t2.r in( 6,5,4,3,2)
order by t1.r + t2.r desc;

with t as (select level r
from dual
connect by level<=6)
select t1.r as ROLL1, t2.r as ROLL2, t3.r as ROLL3
from t t1, t t2, t t3
where (t3.r = t1.r+t2.r)
order by 3 desc;

with t as (
select level r
from dual
connect by level <=6)
select t1.r ROLL1,t2.r ROLL2,t1.r + t2.r ROLL3
from t t1,t t2
where t1.r + t2.r <= 6
order by t1.r + t2.r desc;

Write a SQL Find the list of combinations, where any roll value is equal to sum of other two rolled values

with t as (select level r

from dual
connect by level<=6)
select t1.r as ROLL1, t2.r as ROLL2, t3.r as ROLL3
from t t1, t t2, t t3
where (t3.r = t1.r+t2.r or t2.r = t1.r+t3.r or t1.r = t2.r+t3.r)
order by 3 desc;

Saturday, June 8, 2019

profile PLSQL code using DBMS_PROFILER

Create the profiler tables from the table creation script available in oracle home directory

@C:\orclee18c\WINDOWS.X64_180000_db_home_ORACLE18C\rdbms\admin\proftab.sql

** Search for "proftab.sql" in your oracle home directory

Scripts to clear the profiler tables

truncate table plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;

Invoke "dbms_profiler.start_profiler" to start the profiler and "dbms_profiler.stop_profiler" to stop the profiler.Sample code give below,

exec dbms_profiler.start_profiler ('MY_TEST_PERFORMANCE_RUN1');

exec proc_a;

exec dbms_profiler.stop_profiler();

Query to find the line wise time taken

SELECT

plsql_profiler_runs.RUN_DATE,

plsql_profiler_runs.RUN_COMMENT,

plsql_profiler_units.UNIT_TYPE,

plsql_profiler_units.UNIT_NAME,

plsql_profiler_data.LINE#,

plsql_profiler_data.TOTAL_OCCUR,

plsql_profiler_data.TOTAL_TIME,

plsql_profiler_data.MIN_TIME,

plsql_profiler_data.MAX_TIME,

round(plsql_profiler_data.total_time/1000000000) total_time_in_sec,

trunc(((plsql_profiler_data.total_time)/(sum(plsql_profiler_data.total_time) over()))*100,2) pct_of_time_taken

FROM

plsql_profiler_data,plsql_profiler_runs,plsql_profiler_units

where plsql_profiler_data.total_time > 0

and plsql_profiler_data.runid = plsql_profiler_runs.runid

and plsql_profiler_units.UNIT_NUMBER = plsql_profiler_data.UNIT_NUMBER

and plsql_profiler_units.runid = plsql_profiler_runs.runid

ORDER BY

plsql_profiler_data.total_time DESC;

Saturday, June 1, 2019

Named Cursor Vs REF Cursor in Oracle

Named Cursor Vs REF Cursor in Oracle

--------------------------------------------------------------------
set serveroutput on
DECLARE
CURSOR lc_emp_name IS SELECT ename
FROM emp
WHERE deptno=10;

lv_emp_name varchar2(30);
BEGIN
OPEN lc_emp_name;
LOOP
FETCH lc_emp_name into lv_emp_name;
EXIT WHEN lc_emp_name%notfound;
dbms_output.put_line(lv_emp_name);
END LOOP;
CLOSE lc_emp_name;
END;
/

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

CREATE OR REPLACE PROCEDURE PRINT_LIST(p_in varchar2) as
TYPE REF_CUR_TYPE IS REF CURSOR;
lv_ref_cur REF_CUR_TYPE;
lv_name VARCHAR2(30);
BEGIN

IF p_in='ENAME' THEN
OPEN lv_ref_cur FOR SELECT ename FROM emp;
ELSIF p_in='DNAME' THEN
OPEN lv_ref_cur FOR 'SELECT dname FROM dept';
ELSIF p_in='JOB' THEN
OPEN lv_ref_cur FOR SELECT distinct job FROM emp;
ELSE
OPEN lv_ref_cur FOR SELECT 'INVALID INPUT' FROM dual;
END IF;

LOOP
FETCH lv_ref_cur INTO lv_name;
EXIT WHEN lv_ref_cur%notfound;
dbms_output.put_line(lv_name);
END LOOP;
END;
/

set serveroutput on
EXEC PRINT_LIST('ENAME');
EXEC PRINT_LIST('DNAME');
EXEC PRINT_LIST('JOB');
EXEC PRINT_LIST('ABC');

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

CREATE OR REPLACE PROCEDURE PROC_GET_LIST(p_in varchar2,p_out_cur out SYS_REFCURSOR) as
lv_name VARCHAR2(30);
BEGIN

IF p_in='ENAME' THEN
OPEN p_out_cur FOR SELECT ename FROM emp;
ELSIF p_in='DNAME' THEN
OPEN p_out_cur FOR 'SELECT dname FROM dept';
ELSIF p_in='JOB' THEN
OPEN p_out_cur FOR SELECT distinct job FROM emp;
ELSE
OPEN p_out_cur FOR SELECT 'INVALID INPUT' FROM dual;
END IF;

END;
/

CREATE OR REPLACE PROCEDURE PROC_PRINT_LIST(p_in varchar2) AS
TYPE lc_ref_cur_type IS REF CURSOR;
lc_ref_cur lc_ref_cur_type;
lv_name VARCHAR2(30);
BEGIN
PROC_GET_LIST(p_in,lc_ref_cur);

LOOP
FETCH lc_ref_cur INTO lv_name;
EXIT WHEN lc_ref_cur%notfound;
dbms_output.put_line(lv_name);
END LOOP;
END;
/

set serveroutput on
exec PROC_PRINT_LIST('ENAME');
exec PROC_PRINT_LIST('DNAME');
exec PROC_PRINT_LIST('JOB');
exec PROC_PRINT_LIST('ABC');

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

CREATE OR REPLACE PACKAGE emp_pkg AS
CURSOR emp_list IS SELECT *
FROM emp;

CURSOR emp_list_10 IS SELECT *
FROM emp
WHERE deptno = 10;

CURSOR emp_list_20 IS SELECT *
FROM emp
WHERE deptno = 20;

END emp_pkg;
/

DECLARE
lv_emp_rec emp%rowtype;
BEGIN
OPEN emp_pkg.emp_list;
FETCH emp_pkg.emp_list into lv_emp_rec;
dbms_output.put_line('ENAME = '||lv_emp_rec.ename);
dbms_output.put_line('SAL = '||lv_emp_rec.sal);
END;

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

Wednesday, May 29, 2019

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

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