立即执行时出现无效字符错误

2021-12-30 00:00:00 oracle11g oracle oracle10g plsql 
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

推荐答案

那里没有字符串(我假设 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

相关文章