Hi!
Can anyone help me with this error? I wanted to Execute Immediate a Select statement that concatenates a table name that will change everyday/month. Please see my query below and the error I am getting. Thank You!
DECLARE
v_mon VARCHAR2(5);
v_mon2 VARCHAR2(5);
v_table_name VARCHAR2(25);
v_get_fromdate VARCHAR2(200);
v_table_exists VARCHAR2(1);
BEGIN
SELECT SUBSTR(to_char(SYSDATE, 'ddmonyy'), 3, 5) INTO v_mon FROM DUAL;
SELECT SUBSTR(to_char(SYSDATE, 'mmddyy'), 1, 2) INTO v_mon2 FROM DUAL;
v_table_name := 'mpiat_after_bs_' || v_mon || '_t';
v_get_fromdate := 'select GET_FROMDATE(to_date(''01/'|| v_mon2 ||'/2017 00:00:00'',''dd/mm/yyyy hh24:mi:ss''),''R'') from dual;';
EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO v_table_exists FROM ' || v_table_name;
IF v_table_exists=1
THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || v_table_name;
ELSE
EXECUTE IMMEDIATE 'CREATE TABLE ' || v_table_name || ' AS SELECT column1
, column2
, column3
FROM table_name
WHERE column1 >=' || v_get_fromdate ||'
AND column3 like ''tbl_%''';
END IF;
EXECUTE IMMEDIATE 'SELECT DISTINCT program_name, COUNT(*) FROM ' || v_table_name ||' GROUP BY program_name;';
END;
---------------
Error report -
ORA-00905: missing keyword
ORA-06512: at line 17
00905. 00000 - "missing keyword"
*Cause:
*Action: