No Data Found Error
Ankit Jaiswal, February 16, 2018 - 6:51 pm UTC
Below query is only working when i assign signle name in variable "Table_Name" but when trying to execute it for all tables using cursor , giving No data Found Error.
Can you help me to know why applying cursor changing it's behavior.
DECLARE
CURSOR cursor_Table_Name IS Select SCRIP_SYMBOL FROM CREATE_TABLE_SCRIP_LIST;
Table_Name CREATE_TABLE_SCRIP_LIST.SCRIP_SYMBOL%type;
SERIES_EQ VARCHAR(3);
V_THPO Number(10,2); V_TOPO Number(10,2); V_POTL Number(10,2); V_POTC Number(10,2);
V_THPC Number(10,2); V_TOPC Number(10,2); V_PCTL Number(10,2); V_PCTC Number(10,2);
V_THPH Number(10,2); V_TOPH Number(10,2); V_PHTL Number(10,2); V_PHTC Number(10,2);
V_THPL Number(10,2); V_TOPL Number(10,2); V_PLTL Number(10,2); V_PLTC Number(10,2);
V_HO Number(10,2); V_OL Number(10,2); V_OC Number(10,2); V_HL Number(10,2);
V_CGU Number(10,2); V_CGD Number(10,2); V_SGU Number(10,2); V_SGD Number(10,2);
--Table_Name Varchar(30);
SQLUpdate Varchar(1000);
BEGIN
Table_Name:='NMDC';
/*
OPEN cursor_Table_Name;
LOOP
FETCH cursor_Table_Name into Table_Name;
EXIT WHEN cursor_Table_Name%notfound; */
EXECUTE IMMEDIATE 'SELECT TH_PO FROM ( SELECT Distinct( TH_PO ),dense_rank() over (order by TH_PO asc) THPO FROM '|| Table_Name||' WHERE Rownum<=30) WHERE THPO =2' INTO V_THPO;
EXECUTE IMMEDIATE 'SELECT TO_PO FROM ( SELECT Distinct( TO_PO ),dense_rank() over (order by TO_PO asc) TOPO FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE TOPO =2' INTO V_TOPO ;
EXECUTE IMMEDIATE 'SELECT PO_TL FROM ( SELECT Distinct( PO_TL ),dense_rank() over (order by PO_TL asc) POTL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE POTL =2' INTO V_POTL ;
EXECUTE IMMEDIATE 'SELECT PO_TC FROM ( SELECT Distinct( PO_TC ),dense_rank() over (order by PO_TC asc) POTC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE POTC =2' INTO V_POTC ;
EXECUTE IMMEDIATE ' SELECT TH_PC FROM ( SELECT Distinct( TH_PC ),dense_rank() over (order by TH_PC asc) THPC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE THPC =2' INTO V_THPC ;
EXECUTE IMMEDIATE ' SELECT TO_PC FROM ( SELECT Distinct( TO_PC ),dense_rank() over (order by TO_PC asc) TOPC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE TOPC =2' INTO V_TOPC ;
EXECUTE IMMEDIATE ' SELECT PC_TL FROM ( SELECT Distinct( PC_TL ),dense_rank() over (order by PC_TL asc) PCTL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PCTL =2' INTO V_PCTL ;
EXECUTE IMMEDIATE ' SELECT PC_TC FROM ( SELECT Distinct( PC_TC ),dense_rank() over (order by PC_TC asc) PCTC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PCTC =2' INTO V_PCTC ;
EXECUTE IMMEDIATE ' SELECT TH_PH FROM ( SELECT Distinct( TH_PH ),dense_rank() over (order by TH_PH asc) THPH FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE THPH =2' INTO V_THPH ;
EXECUTE IMMEDIATE ' SELECT TO_PH FROM ( SELECT Distinct( TO_PH ),dense_rank() over (order by TO_PH asc) TOPH FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE TOPH =2' INTO V_TOPH ;
EXECUTE IMMEDIATE ' SELECT PH_TL FROM ( SELECT Distinct( PH_TL ),dense_rank() over (order by PH_TL asc) PHTL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PHTL =2' INTO V_PHTL ;
EXECUTE IMMEDIATE ' SELECT PH_TC FROM ( SELECT Distinct( PH_TC ),dense_rank() over (order by PH_TC asc) PHTC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PHTC =2' INTO V_PHTC ;
EXECUTE IMMEDIATE ' SELECT TH_PL FROM ( SELECT Distinct( TH_PL ),dense_rank() over (order by TH_PL asc) THPL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE THPL =2' INTO V_THPL ;
EXECUTE IMMEDIATE ' SELECT TO_PL FROM ( SELECT Distinct( TO_PL ),dense_rank() over (order by TO_PL asc) TOPL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE TOPL =2' INTO V_TOPL ;
EXECUTE IMMEDIATE ' SELECT PL_TL FROM ( SELECT Distinct( PL_TL ),dense_rank() over (order by PL_TL asc) PLTL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PLTL =2' INTO V_PLTL ;
EXECUTE IMMEDIATE ' SELECT PL_TC FROM ( SELECT Distinct( PL_TC ),dense_rank() over (order by PL_TC asc) PLTC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PLTC =2' INTO V_PLTC ;
EXECUTE IMMEDIATE ' SELECT High_Open FROM ( SELECT Distinct( High_Open ),dense_rank() over (order by High_Open asc) HO FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE HO =2' INTO V_HO ;
EXECUTE IMMEDIATE ' SELECT Open_Low FROM ( SELECT Distinct( Open_Low ),dense_rank() over (order by Open_Low asc) OL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE OL =2' INTO V_OL ;
EXECUTE IMMEDIATE ' SELECT Open_Close FROM ( SELECT Distinct( Open_Close ),dense_rank() over (order by Open_Close asc) OC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE OC =2' INTO V_OC ;
EXECUTE IMMEDIATE ' SELECT High_Low FROM ( SELECT Distinct( High_Low ),dense_rank() over (order by High_Low asc) HL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE HL =2' INTO V_HL ;
EXECUTE IMMEDIATE ' SELECT C_Gap_up FROM ( SELECT Distinct( C_Gap_up ),dense_rank() over (order by C_Gap_up asc) CGU FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE CGU =2' INTO V_CGU;
EXECUTE IMMEDIATE ' SELECT C_Gap_dwn FROM ( SELECT Distinct( C_Gap_dwn ),dense_rank() over (order by C_Gap_dwn asc) CGD FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE CGD =2' INTO V_CGD ;
EXECUTE IMMEDIATE ' SELECT S_Gap_up FROM ( SELECT Distinct( S_Gap_up ), dense_rank() over (order by S_Gap_up asc) SGU FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE SGU =2' INTO V_SGU ;
EXECUTE IMMEDIATE ' SELECT S_Gap_dwn FROM ( SELECT Distinct( S_Gap_dwn ),dense_rank() over (order by S_Gap_dwn asc) SGD FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE SGD =2' INTO V_SGD ;
UPDATE NSE_SCRIP_MIN_VALUE_TRADED_REP
SET TH_PO_30= V_THPO ,TO_PO_30=V_TOPO ,PO_TL_30=V_POTL ,PO_TC_30=V_POTC ,
TH_PC_30=V_THPC ,TO_PC_30=V_TOPC ,PC_TL_30=V_PCTL ,PC_TC_30=V_PCTC ,
TH_PH_30=V_THPH ,TO_PH_30=V_TOPH ,PH_TL_30=V_PHTL ,PH_TC_30=V_PHTC,
TH_PL_30=V_THPL,TO_PL_30=V_TOPL ,PL_TL_30=V_PLTL,PL_TC_30=V_PLTC,
High_Open_30=V_HO ,Open_Low_30=V_OL ,Open_Close_30=V_OC ,High_Low_30=V_HL,
C_Gap_up_30=V_CGU ,C_Gap_dwn_30=V_CGD ,S_Gap_up_30=V_SGU ,S_Gap_dwn_30=V_SGD
WHERE NSE_SCRIP_MIN_VALUE_TRADED_REP.SCRIP_SYMBOL=Table_Name;
/* END LOOP;
CLOSE cursor_Table_Name; */
END;
February 17, 2018 - 1:58 am UTC
Run the routine and show us *exactly* the full error stack