永久的ORA-06550错误,在使用JDBC从Java应用程序调用存储函数时发生。

huangapple go评论158阅读模式
英文:

Permanent ORA-06550 error, calling stored function from java application using jdbc

问题

我正在尝试从我的Java应用程序中调用多个存储函数,但无论我调用哪个函数,都会出现相同的错误。
例如,给定这个函数:

function insert_value (input_name varchar2) return number;

我尝试使用以下方式调用它:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
    .withCatalogName("MY_PACKAGE_NAME")
    .withFunctionName("insert_value")
    .withoutProcedureColumnMetaDataAccess()
    .declareParameters(
        new SqlParameter("input_name", Types.VARCHAR));
SqlParameterSource parameterMap = new MapSqlParameterSource()
    .addValue("input_name", "John Doe");
int idNumber = call.executeFunction(Integer.class, parameterMap);

我总是得到相同的错误:

java.sql.SQLException: ORA-06550: 第1行第7列:
PLS-00306: 调用 'INSERT_VALUE' 时的参数数目或类型错误
ORA-06550: 第1行第7列:
PL/SQL: 忽略语句

正如您所看到的,参数的名称是正确的,我已经检查过我的JDBC驱动程序支持命名参数,并且不知道如何在SimpleJdbcCall中传递索引而不是参数名称。

有什么建议吗?请记住,我还有一些更复杂的函数,我以相同的方式调用它们,也会返回相同的错误。

英文:

i'm trying to call several stored functions from my java app, but whatever function i call i got the same error.
For example, given this function:

   function insert_value (input_name varchar2) return number;

I'm trying to call it using:

   JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
   SimpleJdbcCall call= new SimpleJdbcCall(jdbcTemplate)
    				.withCatalogName("MY_PACKAGE_NAME")
     				.withFunctionName("insert_value")
     				.withoutProcedureColumnMetaDataAccess()
                    .declareParameters(
     						new SqlParameter("input_name", Types.VARCHAR));
   SqlParameterSource parameterMap = new MapSqlParameterSource()
                 .addValue("input_name", "John Doe");
   int idNumber = call.executeFunction(Integer.class,parameterMap);

I always get the same error:

    java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'INSERT_VALUE'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

As you can see the name of the parameter is correct, i've already checked that my jdbc driver supports Named Parameters and i do not how i can pass indexes instead of parameters names on SimpleJdbcCall.

Any advise? Keep in mind that i have few more complex functions which i invoke in the same way which return the same error.

答案1

得分: 1

首先确保数据库上的对象规范与Java应用程序中定义的规范匹配,通过检查元数据表来执行此操作。
执行查询(如果您没有权限,最好从DBA负责人那里获取)

select object_name, argument_name, position, data_type, data_length, in_out
from user_arguments
where OBJECT_NAME = 'MY_PROCEDURE_OR_FUNCTION'
and Package_name = 'MY_PACKAGE';

对象的示例输出,

永久的ORA-06550错误,在使用JDBC从Java应用程序调用存储函数时发生。

对于函数,您需要返回参数,并且它应该是第一个参数。修改代码以添加输出参数如下。

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
    .withCatalogName("MY_PACKAGE_NAME")
    .withFunctionName("insert_value")
    .withoutProcedureColumnMetaDataAccess()
    .declareParameters(new SqlOutParameter("return", Types.INTEGER),
    new SqlParameter("input_name", Types.VARCHAR));
SqlParameterSource parameterMap = new MapSqlParameterSource()
    .addValue("input_name", "John Doe");
int idNumber = call.executeFunction(Integer.class, parameterMap);

但是,当处理存储过程时,情况略有不同,

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(springTemplate)
    .withCatalogName("PACKAGE_NAME").withProcedureName("PROCEDURE_NAME")
    .withReturnValue().withoutProcedureColumnMetaDataAccess()
    .declareParameters(new SqlParameter("INPUT_PARAMETER", Types.VARCHAR),
    new SqlOutParameter("OUTPUT_PARAMETER_NAME", Types.VARCHAR));

如果您已经重写了函数/过程,请使用useInParameterNames来指定要包括在给定签名中的输入参数名称的列表。

英文:

Firstly ensure that the object specification on the database matches to what is defined in the java app by checking from metadata tables.
Execute the the query(if you don't have permissions better to get it from DBA in-charge)

select object_name,argument_name,position,data_type,data_length,in_out 
from   user_arguments
where  OBJECT_NAME ='MY_PROCEDURE_OR_FUNCTION' 
and    Package_name='MY_PACKAGE'

Sample output for an object,

永久的ORA-06550错误,在使用JDBC从Java应用程序调用存储函数时发生。

For Function you need return parameter and it should be the first argument.Modify the code to add the out parameter as below.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
   SimpleJdbcCall call= new SimpleJdbcCall(jdbcTemplate)
                    .withCatalogName("MY_PACKAGE_NAME")
                    .withFunctionName("insert_value")
                    .withoutProcedureColumnMetaDataAccess()
                    .declareParameters(new SqlOutParameter("return",Types.INTEGER),
                            new SqlParameter("input_name", Types.VARCHAR));
   SqlParameterSource parameterMap = new MapSqlParameterSource()
                 .addValue("input_name", "John Doe");
   int idNumber = call.executeFunction(Integer.class,parameterMap);

However when dealing with stored procedure it is slightly different,

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(springTemplate) 
.withCatalogName("PACKAGE_NAME").withProcedureName("PROCEDURE_NAME")
.withReturnValue().withoutProcedureColumnMetaDataAccess()
.declareParameters(new SqlParameter("INPUT_PARAMETER", Types.VARCHAR),
new SqlOutParameter("OUTPUT_PARAMETER_NAME", Types.VARCHAR));

If you have over-rided functions/procedures, then use useInParameterNames to specify the list of IN parameter names to include for a given signature.

答案2

得分: 0

尝试以下(仍然可能有其他方法)

如果我看到失败消息,那么input_name未绑定到实际调用。

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate).withCatalogName("MY_PACKAGE_NAME")
    .withFunctionName("insert_value");

SqlParameterSource parameterMap = new MapSqlParameterSource().addValue("input_name", "John Doe",
    Types.VARCHAR);

BigDecimal idNumber = call.executeFunction(BigDecimal.class, parameterMap);

或者

使用您的方法,我们必须指定返回参数,因为在函数的情况下,第一个参数被视为返回值,因此在实际调用时始终省略第一个参数。

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate).withCatalogName("MY_PACKAGE_NAME")
    .withFunctionName("insert_value").withoutProcedureColumnMetaDataAccess().declareParameters(
        new SqlOutParameter("return", Types.INTEGER), new SqlParameter("input_name", Types.VARCHAR));

SqlParameterSource parameterMap = new MapSqlParameterSource().addValue((String) "input_name", "John Doe",
    Types.VARCHAR);

Integer idNumber = call.executeFunction(Integer.class, parameterMap);
英文:

Try below, (there could be other way still)

If I see the failure message, the input_name is not bind to the actual call.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate).withCatalogName("MY_PACKAGE_NAME")
                .withFunctionName("insert_value");

        SqlParameterSource parameterMap = new MapSqlParameterSource().addValue("input_name", "John Doe",
                Types.VARCHAR);

        BigDecimal idNumber = call.executeFunction(BigDecimal.class, parameterMap);

OR

With your approach we have to specify the return parameter as in case of function the first argument is considered being the return value as a result in this case the first parameter is always omitted during the actual call.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate).withCatalogName("MY_PACKAGE_NAME")
                .withFunctionName("insert_value").withoutProcedureColumnMetaDataAccess().declareParameters(
                        new SqlOutParameter("return", Types.INTEGER), new SqlParameter("input_name", Types.VARCHAR));

        SqlParameterSource parameterMap = new MapSqlParameterSource().addValue((String) "input_name", "John Doe",
                Types.VARCHAR);

        Integer idNumber = call.executeFunction(Integer.class, parameterMap);

huangapple
  • 本文由 发表于 2020年7月29日 20:45:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/63153938.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定