Why does manually running SQL block work but using EXECUTE IMMEDIATE on the same string doesn't?

Viewed 436

The following procedure is failing with a "ORA-0097: missing equal sign error".

CREATE PROCEDURE AMEPSA.USP_ETL_BATCH_MASTER_UPDATE
    (ENVIRONMENT in VARCHAR2, BATCH_STATUS in VARCHAR2, BATCH_USER_ID VARCHAR2, BATCH_JOB_NAME VARCHAR2)

AS

BEGIN
    IF (BATCH_STATUS = 'Running') THEN
        EXECUTE IMMEDIATE  'UPDATE AMEPSA.ETL_BATCH_MASTER SET
                            BATCH_STATUS_' || ENVIRONMENT || ' = ' || '''' || BATCH_STATUS || '''' || ',
                            BATCH_JOB_NAME_' || ENVIRONMENT || ' = ' || '''' || BATCH_JOB_NAME || '''' || ',' ||
                            'BATCH_USER_ID_' || ENVIRONMENT || ' = ' || '''' || BATCH_USER_ID || '''' || ',' ||
                            'BATCH_START_DATE_' || ENVIRONMENT || ' = TO_DATE(' || '''' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || '''' || ', ' || '''' || 'MM/DD/YYYY HH24:MI:SS' || '''' || ') ' || ',' ||
                            'BATCH_END_DATE_' || ENVIRONMENT || ' = ' || 'NULL' || '
                            WHERE BATCH_NBR = (SELECT MAX(BATCH_NBR) FROM AMEPSA.ETL_BATCH_MASTER)';
    ELSE
        EXECUTE IMMEDIATE   'UPDATE AMEPSA.ETL_BATCH_MASTER SET
                            BATCH_STATUS_' || ENVIRONMENT || ' = ' || '''' || BATCH_STATUS || '''' || ',
                            BATCH_JOB_NAME_' || ENVIRONMENT || ' = ' || '''' || BATCH_JOB_NAME || '''' || ',' ||
                            'BATCH_END_DATE_' || ENVIRONMENT || ' = ' || 'TO_DATE(' || '''' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || '''' || ', ' || '''' || 'MM/DD/YYYY HH24:MI:SS' || '''' || ')
                            WHERE BATCH_NBR = (SELECT MAX(BATCH_NBR) FROM AMEPSA.ETL_BATCH_MASTER)';
    END IF;

    COMMIT;

END;
GO

While debugging the issue, we replaced the EXECUTE IMMEDIATE statements with DBMS_OUTPUT.PUT_LINE statements. After doing so, the procedure returned a string (below) that executed successfully with no syntax errors.

UPDATE AMEPSA.ETL_BATCH_MASTER 

SET BATCH_STATUS_STAGE = 'Running',
    BATCH_JOB_NAME_STAGE = 'wf_TADM_Stage',
    BATCH_END_DATE_STAGE = TO_DATE('08/14/2017 15:42:00', 'MM/DD/YYYY HH24:MI:SS')
WHERE BATCH_NBR = (SELECT MAX(BATCH_NBR) FROM AMEPSA.ETL_BATCH_MASTER)

Why would the EXECUTE IMMEDIATE statement think it's missing an equals sign when the resulting string returned from the DBMS_OUTPUT.PUT_LINE statement is syntactically correct?

Full exception from Informatica:

Severity: ERROR Timestamp: 8/15/2017 9:46:10 AM Node: didre2007 Thread: TRANSF_1_1_1 Process ID: 9072 Message Code: CMN_1022 Message: Database driver error... CMN_1022 [ ORA-00927: missing equal sign ORA-06512: at "AMEPSA.USP_ETL_BATCH_MASTER_UPDATE", line 14 ORA-06512: at line 2

Database driver error... Function Name : ExecuteSP

Oracle Fatal Error Database driver error... Function Name : ExecuteSP

Oracle Fatal Error]

1 Answers
Related