我无法使用jdbc调用该函数，但如果它是流水线的，那么一切都可以正常工作

Question

我使用Spring jdbc和oracle 12。

我无法从package函数获得响应。但如果我使用完全相同的函数，但使用管道，一切都会正常。

创建了一个包并在其中声明了2个函数。它们都以一个数字作为输入，并输出一个pl/sql记录表。函数的不同之处在于，一个是流水线的，另一个不是

该宣言是：

type o_client is record(subs_id     NUMBER);
type t_client is table of o_client;
    
function piplined_func   (p_subs_id in NUMBER)  return t_client pipelined;

function no_piplined_func   (p_subs_id in NUMBER)  return t_client;

主体为：

function piplined_func(p_subs_id in NUMBER) return t_client PIPELINED AS 
v_pipe o_client;
BEGIN 
    FOR ids IN 1..10 LOOP
        v_pipe.subs_id := ids;
        PIPE ROW(v_pipe); 
    END LOOP;
    return ;
END;

function no_piplined_func   (p_subs_id in number)  return t_client  AS 
    l_tab t_client;
    v_pipe o_client;
BEGIN
    l_tab := t_client();
    FOR ids IN 1..10 LOOP
        v_pipe.subs_id := ids;
        l_tab.extend;
        l_tab(l_tab.last) := v_pipe;
    END LOOP;
    return l_tab;
END;

第一个功能是工作正确：

System.out.println("Pipelined");
Processor processor = new Processor(oracleDataSource);
final String query = "select * MY_CATALOG.piplined_func(:p_subs_id)";
SqlParameterSource inputParams = new MapSqlParameterSource().addValue("p_subs_id", 1);
List<Wrapper> result = namedParameterJdbcTemplate.query(query, inputParams, BeanPropertyRowMapper.newInstance(Wrapper.class));
for (Wrapper wrapper : result) {
    System.out.println(wrapper.toString());
}

Out:
Wrapper{subs_id=1}
Wrapper{subs_id=2}
Wrapper{subs_id=3}

第二个函数工作不正确：

System.out.println("Not Pipelined");
Processor processor = new Processor(oracleDataSource);
final String query = "select * from MY_CATALOG.no_piplined_func(:p_subs_id)";
SqlParameterSource inputParams = new MapSqlParameterSource().addValue("p_subs_id", 1); 
List<Wrapper> result = namedParameterJdbcTemplate.query(query, inputParams, BeanPropertyRowMapper.newInstance(Wrapper.class));
for (Wrapper wrapper : result) {
    System.out.println(wrapper.toString());
}

组织。springframework。jdbc。BadSqlGrammarException：PreparedStatementCallback；错误的SQL语法[从sa\U db\U测试中选择*无管道功能（？）；嵌套异常为java。sql。SQLSyntaxErrorException:ORA-00902：无效的数据类型

我尝试以同样的方式使用SimpleJDBCall，但也没有成功。

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(oracleDataSource)
    .withSchemaName("MY_SCHEMA")
    .withCatalogName("MY_CATALOG")
    .withProcedureName("no_piplined_func")
    .withoutProcedureColumnMetaDataAccess()
    .declareParameters( new SqlParameter("p_subs_id", Types.NUMERIC),);
SqlParameterSource in = new MapSqlParameterSource().addValue("p_subs_id", 1);
Map<String, Object> out = simpleJdbcCall.execute(in);

线程“main”组织中出现异常。springframework。jdbc。BadSqlGrammarException：CallableStatementCallback；错误的SQL语法[{调用MY\u SCHEMA.MY\u CATALOG.NO\u PIPLINED\u FUNC（？）}]；嵌套异常为java。sql。SQLException：ORA-06550：第1行，第7列：PLS-00221：“NO\u PIPLINED\u FUNC”不是过程或未定义

Answer 1

记录是仅限PL/SQL的数据类型，不能在SQL语句中使用。

管道化函数设计用于SQL语句中，尽管您声明它返回一个包含数据类型的表，但它实际上并没有这样做，而是创建可以在SQL语句中使用的等效SQL数据类型（即对象数据类型）。

如果您希望两者都起作用，请在SQL范围内声明o_client和t_client，使用：

CREATE TYPE o_client IS OBJECT (subs_id NUMBER);
CREATE TYPE t_o_client IS TABLE OF o_client;

然后，您可以使用SQL数据类型而不是PL/SQL数据类型：

CREATE PACKAGE pkg IS
  type r_client is record(subs_id     NUMBER);
  type t_client is table of r_client;
    
  function pipelined_func    return t_client pipelined;

  function no_pipelined_func return t_client;

  function no_pipelined_func_obj return t_o_client;
END;
/

和身体：

CREATE PACKAGE BODY pkg IS
  FUNCTION pipelined_func
  RETURN t_client PIPELINED
  AS 
    v_pipe r_client;
  BEGIN 
    FOR ids IN 1..10 LOOP
        v_pipe.subs_id := ids;
        PIPE ROW(v_pipe); 
    END LOOP;
  END;

  FUNCTION no_pipelined_func
  RETURN t_client
  AS 
    l_tab  t_client;
    v_pipe r_client;
  BEGIN
    l_tab := t_client();
    FOR ids IN 1..10 LOOP
      v_pipe.subs_id := ids;
      l_tab.extend;
      l_tab(l_tab.last) := v_pipe;
    END LOOP;
    RETURN l_tab;
  END;

  FUNCTION no_pipelined_func_obj
  RETURN t_o_client
  AS 
    l_tab  t_o_client := t_o_client();
  BEGIN
    FOR ids IN 1..10 LOOP
      l_tab.extend;
      l_tab(l_tab.last) := o_client(ids);
    END LOOP;
    RETURN l_tab;
  END;
END;
/

然后：

SELECT * FROM pkg.no_pipelined_func();

失败原因：

ORA-00902: invalid datatype

但是：

SELECT * FROM pkg.pipelined_func();

和

SELECT * FROM pkg.no_pipelined_func_obj();

两种输出：