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]

I can't reproduce this. But the static SQL you've shown seems to be from the non-Running branch, despite have status Running; not really sure how that is significant but still... How are you calling the procedure, and are there any triggers on the table? Can you add the full exception stack to the question? — Alex Poole
Have you tried using the USING clause of EXECUTE IMMEDIATE? — Sal
AlexPoole The proc is being called by an Informatica workflow. No triggers on the table. Full exception added above, thanks. — Matthew Walk
The error says the procedure is in the AMEPSA schema; your code says it's in the MMW1164 schema. Are you just looking at the wrong code? (Also, agree with Sal, this would be simplified by using bind variables instead of concatenating in the values with escaped quotes; you also don't need to convert sysdate to a string and back - but those are probably off-topic). — Alex Poole
Sal Sorry, I'm not familiar with USING when it comes to executing a procedure. How does it compare to EXECUTE IMMEDIATE? — Matthew Walk

Alex Poole Alex Poole · Accepted Answer · 2017-08-15T18:41:21

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;

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

1 Answers