I using spring jdbc and oracle 12.

I can't get a response from the package function. But if I use exactly the same function but with the pipeline everything works.

Created a package and declared 2 functions in it. Both of them take a number as input, and output a table of pl/sql records. The difference in the function is that one is pipelined and the other is not

The declaration is:

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;

The body is:

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;

The first function is work correct:

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}

The second function is not work correct:

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());
}

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from sa_db_test.no_piplined_func(?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype

I tried to use SimpleJdbcCall in the same way, but also unsuccessfully.

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

Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call MY_SCHEMA.MY_CATALOG.NO_PIPLINED_FUNC(?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'NO_PIPLINED_FUNC' is not a procedure or is undefined

CodePudding user response：

A RECORD is a PL/SQL ONLY data-type that cannot be used in SQL statements.

A PIPELINED function is designed to be used in SQL statements and despite you declaring that it returns a table of RECORD data types, it does not actually do that and, instead, creates equivalent SQL data-types (i.e. OBJECT data-types) that can be used in SQL statements.

If you want both to work then declare o_client and t_client in the SQL scope using:

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

Then you can use SQL data-types and not PL/SQL data-types:

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;
/

and the body:

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;
/

Then:

SELECT * FROM pkg.no_pipelined_func();

Fails with:

ORA-00902: invalid datatype

But:

SELECT * FROM pkg.pipelined_func();

and

SELECT * FROM pkg.no_pipelined_func_obj();

Both output:

SUBS_ID
1
2
3
4
5
6
7
8
9
10

db<>fiddle here