当命名参数在JDBC PL/SQL块中多次使用时出错 [英] Error when a named parameter used multiple times in JDBC PL/SQL block

问题描述

当使用命名参数调用PL/SQL块时，当所有命名参数仅使用一次时，我的代码可以正常工作，但是当我复制标有"//SQL"的SQL时出现错误.然后所有命名参数(以冒号:q开头)都被使用了两次，现在我得到一个SQL异常，它说:参数名称的数量与已注册的参数表的数量不匹配. 似乎JDBC驱动程序或DB认为有2个参数，但是仅注册了1个参数?为什么我们不能多次使用命名参数?不需要JDBC驱动程序来支持这种情况吗? 如何获得替代方法(将PL/SQL块重写为存储过程)?

I get an error when use named parameter to call PL/SQL block, when all named parameters are used only once, then my code works fine, but when I dupplicate the SQL marked with "// the SQL". then all named parameters (starts with colon, :q) are used twice, now I get a SQL Exception, it says: The number of parameter names does not match the number of registered praremeters. It seems that JDBC driver or DB think there is 2 parameters, but only 1 parameters are registered? why we cannot use a named parameter multiple times? is JDBC driver don't required to support this case? How I get an alternative (exeption rewriting PL/SQL block to stored procedure)?

我的Oracle JDBC驱动程序是最新版本11.2.0.3.0.

My Oracle JDBC Driver is latest version 11.2.0.3.0.

因为我的项目有许多PL/SQL块，所以我尽力避免将SQL重写到存储过程中，最好运行带有命名参数的原始PL/SQL块(将其视为过程).我测试过将PL/SQL块转换为存储过程，然后仅导出1个参数，但是我不想重写所有PL/SQL块，这需要更多的努力.

Because my project have many PL/SQL block, I try my best to avoid rewrite SQL to a stored procedure, running raw PL/SQL block with named parameter (just treat it as a procedure) is preferred. I tested convert the PL/SQL block to a stored procedure, then only 1 parameters exported, but I don't want to rewrite all PL/SQL blocks, it takes more efforts.

感谢任何提示.

我的Java代码:

CallableStatement stmt = ...;
stmt.registerOutParameter("q", Types.VARCHAR);
stmt.execute();
String v1 = stmt.getString("q");

SQL如下:

BEGIN
    select DUMMY into :q from dual where dummy = 'X';
    select DUMMY into :q from dual where dummy = 'X';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;

我刚刚发现"Execute Instant"可以用于我的情况，当动态SQL是一个PL/SQL块(以begin和end引用)时，带有名称的命名参数可以被所有实例引用.即下面附加的SQL，在此块中，参数"q"仅使用一次， 但现在我还有两个问题， 问题1:我不知道参数'q'是I​​N，OUT还是IN和OUT.如果我输入/输出模式错误，则会出现错误，我们如何测试参数是输入/输出还是两者都输入?我想在SQL中扫描':q:='和'into:q'，看来这不是一个好方法. 问题2:为什么在将参数'q'分配为IN OUT模式时无法获取结果?只有在OUT的情况下，我才能获得其价值.当它们都是IN OUT时，我得到NULL.

I just found that "Execute Immediate" can be used for my case, when the dynamic SQL is a PL/SQL block (quoted with begin and end), then the named parameters with a name can be referenced by all occurrences. i.e. SQL attached below, in this block, parameter 'q' is used only once, but now I have another 2 questions, Q1: I don't know if the parameter 'q' is IN, OUT or both IN and OUT. If I give wrong IN/OUT mode, error got, how we test if the parameter is IN/OUT or both of them? I want to scan the SQL for ':q :=' and 'into :q', it seems that it is not good method. Q2: Why I can't fetch result of parameter 'q' when it is assigned IN OUT mode? only if it is OUT, I can get its value. when it is both IN OUT, I get NULL.

begin
  execute immediate 'begin select dummy into :q from dual where :q is not null; end;'
  using in out :q;
end;

哦，当参数为IN OUT模式时，我得到了NULL的解决方法，我只是将其视为Oracle JDBC驱动程序的错误，我将命名参数'q'的IN/OUT角色分为两部分，首先是IN ，第二个是OUT，使用变量来保持其值，方法是使用'in in out:q'子句，然后将变量分配给第二个角色，如下面所述，在JDBC中，我们将它们都视为IN OUT，仅使用完全IN，扫描':q:='和'到:q'之后，在USING子句中使用OUT或IN OUT.

Oh, I get a workaround for NULL when parameter is IN OUT mode, I just treat it is a bug of Oracle JDBC driver, I split IN/OUT role of the named parameter 'q' into 2 parts, first is IN, second is OUT, using a variable to keep its value returned by 'using in out :q' clause, and then assign variable to 2nd role, like below-attached, in JDBC we treat it both IN OUT, only use exact IN,OUT or IN OUT in USING clause after scanning ' :q := ' and ' into :q '.

declare 
    p varchar2(100);
    q varchar2(100);
begin
    p := ?;
    q := ?;
    execute immediate 'begin if :p is null then :p := ''X''; else :p := ''Y''; :q := ''Z''; end if; end;' using in out p, out q;
    ? := p;
    ? := q;
end;

推荐答案

您不能在SQL语句中多次使用一个绑定参数.您必须为每次出现的参数提供一个值.这是因为Oracle忽略绑定参数名称，而只考虑冒号.
Oracle文档

You can't use one bind parameter multiple times in SQL statement. You must provide a value for each occurrence of parameter. This is because Oracle ignores bind parameter name and only a colon symbol is taken into account.
Oracle docs

这篇关于当命名参数在JDBC PL/SQL块中多次使用时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！