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

Question

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]

Answer 1

It looks like the parameter values being passed into the procedure have single quotes; specifically the first one, but possibly all of them. You can replicate by calling it directly:

exec USP_ETL_BATCH_MASTER_UPDATE('''STAGE''', '''Running''', '''someuser''', '''wf_TADM_Stage''');

which gets

Error report -
ORA-00927: missing equal sign
ORA-06512: at "AMEPSA.USP_ETL_BATCH_MASTER_UPDATE", line 14
ORA-06512: at line 1
00927. 00000 -  "missing equal sign"

or just

exec USP_ETL_BATCH_MASTER_UPDATE('''STAGE''', 'Running', 'someuser', 'wf_TADM_Stage');

which goes into the other branch and gets:

Error report -
ORA-00927: missing equal sign
ORA-06512: at "AMEPSA.USP_ETL_BATCH_MASTER_UPDATE", line 6
ORA-06512: at line 1
00927. 00000 -  "missing equal sign"

With debugs showing the code before it's executed you can see it ends up trying to run this:

UPDATE AMEPSA.ETL_BATCH_MASTER SET
                                        BATCH_STATUS_'STAGE' = 'Running',
                                        BATCH_JOB_NAME_'STAGE' = 'wf_TADM_Stage',BATCH_USER_ID_'STAGE' = 'someuser',BATCH_START_DATE_'STAGE' = TO_DATE('08/15/2017 19:37:00', 'MM/DD/YYYY HH24:MI:SS') ,BATCH_END_DATE_'STAGE' = NULL
                                   WHERE BATCH_NBR = (SELECT MAX(BATCH_NBR) FROM AMEPSA.ETL_BATCH_MASTER)

with inappropriate quotes; the BATCH_STATUS_'STAGE' construct is throwing the exception.

If called without those extra single quotes, what you've shown works, in either branch.

So you need to look into why Informatica is adding those single quotes - possibly just a developer's confusion about how to handle strings as arguments - and stop it doing that. The problem isn't in the code you showed, and without seeing the Informatica code, I can't be more specific about how it should be fixed.

---

Incidentally, in comments it was mentioned that you could use bind variables via the using clause, and not convert sysdate to a string and back; it's nothing to do with the error you're getting, but that might look like:

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 || ' = SYSDATE,'
                || ' BATCH_END_DATE_' || ENVIRONMENT || ' = NULL'
                || ' WHERE BATCH_NBR = (SELECT MAX(BATCH_NBR) FROM AMEPSA.ETL_BATCH_MASTER)'
            USING BATCH_STATUS, BATCH_JOB_NAME, BATCH_USER_ID;
    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 || ' = SYSDATE'
                || ' WHERE BATCH_NBR = (SELECT MAX(BATCH_NBR) FROM AMEPSA.ETL_BATCH_MASTER)'
            USING BATCH_STATUS, BATCH_JOB_NAME;
    END IF;

END;