立即执行时无效的字符错误 [英] invalid character error while executing immediate

问题描述

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

我在Exception块中收到以下错误

I am getting the below error within Exception block

ORA-00911: invalid character

推荐答案

那里没有字符串(我假设str被声明为某种描述的字符).如果要插入字符串，则需要额外的引号，否则在此实例中它将被解释为一列.像这样:

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).

还值得注意的是，这有点 SQL -injectiony ...，您应该使用绑定变量，而不是串联；这全部在文档中进行了描述:

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;

但是，在这种情况下无需使用动态SQL.您可以简单地插入变量值:

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;

最后，我对您的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

这篇关于立即执行时无效的字符错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！