Execute immediate error in Oracle

2
votes

I want to execute the following command through a cursor where I have column name and column values in a different table.

However the script fails with following error message.

ORA-00933: SQL command not properly ended

execute immediate 'UPDATE EMP_DETAILS_T SET ' || EMP_REC.COLUMN_NAME || ' = ' || '''' || EMP_REC.VALUE ||''' ' || ' where employee_id = ' || ''''|| EMP_REC.EMPLOYEE_ID || ''' '  ;

entire command is as follow.

DECLARE 
CURSOR CR_EMP_ATT IS
SELECT COLUMN_NAME,
       VALUE,
       EMPLOYEE_ID
FROM  UPDATE_DATA_T
WHERE EMPLOYEE_ID = P_EMP_ID;

BEGIN


FOR CR_EMP IN CR_EMP_ATT LOOP

    BEGIN
    EXECUTE IMMEDIATE 'Update EMP_DETAILS_T set ' || CR_EMP.COLUMN_NAME || ' = ' ||''''||CR_EMP.VALUE||''''|| '  where employee_id  =  ' ||''''|| P_EMP_ID||'''';
    DBMS_OUTPUT.PUT_LINE('temp table updated');
    END;

END LOOP;

END;

I have placed this query in a procedure to run when needed by employee.

2
oracleplsqlplsqldeveloper
can't debug your statement, but here some ideas: 1. try adding a semicolon to your statement - 2. try removing the quotes sorrounding the employee_id (i assume it is an integer, not a varchar) 3. if this doesnt work, try saving the statement string to a constant and use placeholders and the "using"-Syntax for execute immediate (keyword: bind variables) - UniversE
Don't add a semicolon for an execute immediate call, that's not valid, and the quotes won't cause this error even if your ID is a number. Do use bind variables for the employee ID and value. Can you add a dbms_output call before the execute immediate to show the command that's actually going to be executed, to see which values it fails on? Are any of your column_name values reserved words, perhaps? - Alex Poole
@Alex Poole : when removed semicolon it gives error. - user3494938
Or do any of your value or ID values have a single quote in them - maybe if you have a column with names in you have an O'Brien or something? That would cause this error; and would be revealed by displaying the statement being executed. And would be fixed by using bind variables... - Alex Poole
@user3494938 - I didn't mean to remove the semicolon you have after the call now; I was disagreeing with the advice to add a semicolon inside the dynamic statement. - Alex Poole

2 Answers

3
votes

Your value column includes values which contain a single quote. (The ampersand isn't going to be a problem here). You can see how that breaks the statement by displaying it before execution:

dbms_output.put_line('Update EMP_DETAILS_T set ' || CR_EMP.COLUMN_NAME
  || ' = ' ||''''||CR_EMP.VALUE||''''|| '  where employee_id  =  '
  ||''''|| P_EMP_ID||'''');

... which reveals:

Update EMP_DETAILS_T set SOME_COLUMN = 'Standard & Poor's'  where employee_id  =  'ABC123'

... which has unbalanced single quotes; you can even see that in the syntax highlighting.

The simplest fix is to use bind variables for the two values - you can't for the column name - and pass the actual values with the USING clause:

EXECUTE IMMEDIATE 'Update EMP_DETAILS_T set ' || CR_EMP.COLUMN_NAME
    || ' = :VALUE where employee_id = :EMP_ID'
    USING CR_EMP.VALUE, P_EMP_ID;
2
votes

Does your value CR_EMP.VALUE contain any ' character?

That would be another reason to use bind variables, i.e.

EXECUTE IMMEDIATE 'Update EMP_DETAILS_T set '||CR_EMP.COLUMN_NAME||' = ' :val WHERE employee_id = :id' 
USING CR_EMP.VALUE, P_EMP_ID;