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
– Alex Poole, Commented Aug 15, 2017 at 18:02
Have you tried using the USING clause of EXECUTE IMMEDIATE? — Sal
– Sal, Commented Aug 15, 2017 at 18:07
AlexPoole The proc is being called by an Informatica workflow. No triggers on the table. Full exception added above, thanks. — Matthew Walk
– Matthew Walk, Commented Aug 15, 2017 at 18:18
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
– Alex Poole, Commented Aug 15, 2017 at 18:20
Sal Sorry, I'm not familiar with USING when it comes to executing a procedure. How does it compare to EXECUTE IMMEDIATE? — Matthew Walk
– Matthew Walk, Commented Aug 15, 2017 at 18:20

Alex Poole · Accepted Answer · 2017-08-15 18:47:23Z

2

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;

edited Aug 15, 2017 at 18:47

answered Aug 15, 2017 at 18:41

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jason Krs Over a year ago

I did not understand your answer. Did you solve his issue ?

Alex Poole Over a year ago

@JasonKrs - not exactly, but I believe I've shown the problem is in how Informatica is calling the procedure, rather than in the code that was shown. I don't know how to fix the Informatica side because we haven't seen that.

xQbert Over a year ago

Specifically look at the line: with innapropriate quotes; the BATCH_STATUS_'STAGE' construct is causing throwing the exception. stage in quotes seems problematic as alex indicated.

Jason Krs Over a year ago

If he uses to_varchar near ENVIRONMENT so that concat | | works on two specifically identified strings would it work ?

Alex Poole Over a year ago

Oracle doesn't have a to_varchar function, and calling to_char would try and fail to implicitly convert it to a number first. You could dynamically convert it I suppose....but that would be fixing the wrong problem IMO. The call is wrong. (Well,TBC, but I'm pretty sure...)

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related