invalid character error while executing immediate

Question

BEGIN    
    exception
         when others then
    sqltext2:='insert into ERROR_TABLE_SHREE select '||str||' from dual;';
    EXECUTE IMMEDIATE sqltext2;
    end;
    COMMIT;

I am getting the below error within Exception block

ORA-00911: invalid character

Answer 1

You don't want a semicolon in the character string you are building and passing to EXECUTE IMMEDIATE.

sqltext2:='insert into ERROR_TABLE_SHREE select '||str||' from dual';

I'm hoping that you're not really starting your block with a WHEN OTHERS exception handler. And I'm hoping that you have a valid reason for using dynamic SQL here since I'm not seeing one.

Answer 2

You don't have a string there (I assume str is declared as a character of some description). If you wanted to insert a string you need extra quotes otherwise it'll be interpreted as a column in this instance. Something like:

begin
   ...
exception
   when others then
     sqltext2 := 'insert into error_table_shree select '''||str||''' from dual';
     execute immediate sqltext2;
end;
commit;

Please note that I've removed the semi-colon from the end of your string; this is not required (and is probably the actual cause of your error).

It's also worth noting that this is a bit SQL-injectiony... you should be using bind variables rather than concatenation; this is all described in the documentation:

begin
   ...
exception
   when others then
     execute immediate 'insert into error_table_shree select :1 from dual' 
                  using str;
end;
commit;

However, there's no need to use dynamic SQL in this context; you could simply insert the variable value:

begin
   ...
exception
   when others then
     insert into error_table_shree values (str);
end;
commit;

Lastly, I am slightly concerned about your COMMIT; it's unusual to commit after handling an error in this manner. Without more context it's impossible to be certain but it would be more normal for error logging to be performed in an autonomous transaction