EXECUTE IMMEDIATE v_sql_stmt is not executing

From: Nik Tek <niktek2005_at_gmail.com>
Date: Mon, 10 Aug 2015 15:51:56 -0700
Message-ID: <CAHySzWWsN5cEN-u1wEhqe+5tme3NJzZ9yyQ--4uGfWqFo4uy0Q_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 11 2015 - 00:51:56 CEST