Re: EXECUTE IMMEDIATE v_sql_stmt is not executing

  • From: Nik Tek <niktek2005@xxxxxxxxx>
  • To: Andrew Kerber <andrew.kerber@xxxxxxxxx>
  • Date: Mon, 10 Aug 2015 15:58:44 -0700

Hi Andrew,

a) there is no output printing from the v_sql_stmt
b) DBMS_OUTPUT.PUT_LINE('v_sql_stmt: ------------------------ ' ); didn't
print
c) If I copy/paste the output of DBMS_OUTPUT.PUT_LINE('v_sql_stmt: ' ||
v_sql_stmt);, I see the query working just fine.

Thank you
Nik

On Mon, Aug 10, 2015 at 3:56 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:

What are you trying to do? And how can you tell it's not executing?

Sent from my iPhone

On Aug 10, 2015, at 5:51 PM, Nik Tek <niktek2005@xxxxxxxxx> wrote:

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




--
Thank you
NikTeki

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2015