Hi Oracle gurus,
I have the below pl/sql code, which works fine, but for some reason the
"EXECUTE IMMEDIATE v_sql_stmt" statement is not executing, I'm not sure
why, is there an alternative approach to solve this?
===================== BEGIN =====================
DECLARE
v_hisstat VARCHAR2(50);
v_sql_stmt VARCHAR2(32000) := '';
v_begin_datetime DATE := TO_DATE('2015-08-08 00:00:00', 'YYYY-MM-DD
HH24:MI:SS');
v_end_datetime DATE := TO_DATE('2015-08-09 00:00:00', 'YYYY-MM-DD
HH24:MI:SS');
v_sat_id VARCHAR2(32000);
v_entid_list VARCHAR2(32000);
v_rowcount INT;
v_entid_sql VARCHAR2(32000);
v_satid_sql VARCHAR2(32000);
CURSOR hs_curr IS
SELECT DISTINCT 'XP_HISSTAT1_' || TO_CHAR(PART_INDEX)
FROM XP_SAM_TIME1 sm
WHERE SM.SAM_TIME >= v_begin_datetime AND
SM.SAM_TIME <= v_end_datetime
ORDER BY 1;
BEGIN
OPEN hs_curr;
BEGIN
-- Get ent list
SELECT WM_CONCAT(ENT_ID) INTO v_entid_list
FROM (SELECT DISTINCT ENT_ID
FROM XP_COUNTER)
WHERE ROWNUM < 50; ---------------------> Gets only 20
ent_ids (adjust accordingly)
-- Get sat_id list
SELECT WM_CONCAT(SAT_ID) INTO v_sat_id
FROM (SELECT DISTINCT SAT_ID
FROM XP_COUNTER)
WHERE ROWNUM < 50; ---------------------> Gets only 20
sat_ids (adjust accordingly)
-- Get row count
SELECT COUNT(DISTINCT PART_INDEX) INTO v_rowcount
FROM XP_SAM_TIME1 sm
WHERE SM.SAM_TIME >= v_begin_datetime AND
SM.SAM_TIME <= v_end_datetime;
--DBMS_OUTPUT.PUT_LINE( 'ROWCOUNT: ' || v_rowcount);
END;
LOOP
FETCH hs_curr INTO v_his_stat;
EXIT WHEN hs_curr%NOTFOUND;
BEGIN
v_sql_stmt := v_sql_stmt || ' SELECT SC.ENT_ID, SM.SAM_TIME,
ST.SAT_VAL
FROM XP_COUNTER SC, XP_SAM_TIME1 SM, XP_DEV D, ' ||
v_his_stat || ' ST
WHERE SM.SAM_TIME >= ''' || TO_CHAR(v_begin_datetime,
'YYYY-MM-DD HH24:MI:SS') || ''' AND
SM.SAM_TIME <= ''' || TO_CHAR(v_end_datetime,
'YYYY-MM-DD HH24:MI:SS') || ''' AND
ST.T_ID = SM.T_ID AND
SC.SAT_ID IN (' || TO_CHAR(v_sat_id) || ') AND
D.DEV_NAME = '''' AND
SC.ENT_ID IN (' || v_entid_list || ') AND
SC.COUNTER_ID = ST.COUNTER_ID AND
SC.DEV_ID = D.DEV_ID';
v_sql_stmt := v_sql_stmt || CASE WHEN hs_curr%ROWCOUNT !=
v_rowcount THEN ' UNION '
WHEN hs_curr%ROWCOUNT =
v_rowcount THEN ''
END;
END;
END LOOP;
v_sql_stmt := v_sql_stmt || ' ORDER BY ENT_ID, SAM_TIME;';
DBMS_OUTPUT.PUT_LINE('v_sql_stmt: ' || v_sql_stmt);
execute immediate v_sql_stmt;
DBMS_OUTPUT.PUT_LINE('v_sql_stmt: ------------------------ ' );
CLOSE hs_curr;
EXCEPTION
WHEN OTHERS THEN
IF hs_curr%ISOPEN THEN
CLOSE hs_curr;
END IF;
END;
/
===================== END =====================
--
Thank you
NikTeki