I have a function that return a refcursor (step 1).
When I execute from the DBManager, I get three lines as resulted (step 2).
When I run the java code, I don't have success (step 3). Throws the error:
No results were returned by the query.
Somebody knows what's happening?
Pgsql version 7.3.2 and JDBC2.
1. Function
CREATE OR replace function fn_cad_servico_listar(
cad_servico.num_ano_exercicio%TYPE
, cad_servico.num_mes_exercicio%TYPE
, cad_servico.cod_atividade%TYPE
, cad_servico.cod_servico%TYPE
, cad_servico.dsc_servico%TYPE
)
returns refcursor AS '
DECLARE
p_num_ano_exercicio alias FOR $1;
p_num_mes_exercicio alias FOR $2;
p_cod_atividade alias FOR $3;
p_cod_servico alias FOR $4;
p_dsc_servico alias FOR $5;
p_retorno refcursor;
BEGIN
OPEN p_retorno FOR
SELECT
srv.num_ano_exercicio
, srv.num_mes_exercicio
, srv.cod_atividade
, atv.dsc_atividade
, srv.cod_servico
, srv.dsc_servico
, srv.num_posicao
, srv.cod_unidade_valor
, mda.dsc_sigla
, srv.vlr_repasse
FROM
cad_servico srv
, cad_atividade atv
, gen_unidade_valor mda
WHERE
atv.num_ano_exercicio = srv.num_ano_exercicio
AND atv.num_mes_exercicio = srv.num_mes_exercicio
AND atv.cod_atividade = srv.cod_atividade
AND mda.cod_unidade_valor = srv.cod_unidade_valor
AND (srv.num_ano_exercicio = p_num_ano_exercicio
OR p_num_ano_exercicio IS NULL)
AND (srv.num_mes_exercicio = p_num_mes_exercicio
OR p_num_mes_exercicio IS NULL)
AND (srv.cod_atividade = p_cod_atividade
OR p_cod_atividade IS NULL)
AND (srv.cod_servico = p_cod_servico
OR p_cod_servico IS NULL)
AND (srv.dsc_servico LIKE p_dsc_servico
OR p_dsc_servico IS NULL)
ORDER BY
srv.num_ano_exercicio DESC
, srv.num_mes_exercicio DESC
, atv.num_posicao ASC
, srv.num_posicao ASC
;
return p_retorno;
END;
' LANGUAGE 'plpgsql';
2. Executing from DBManager
BEGIN;
SELECT fn_cad_servico_listar(NULL, NULL, NULL, NULL, NULL);
FETCH ALL IN "<unnamed cursor 1>";
resultly:
num_ano_exercicio num_mes_exercicio cod_atividade dsc_atividade cod_servico
dsc_servico num_posicao cod_unidade_valor dsc_sigla vlr_repasse
2004 1 1 Registro 1 Nascimento 1 1 R$ 50
2004 1 1 Registro 2 Obito 2 1 R$ 40
2004 1 1 Registro 3 Natimorto 3 2 UFESP 30
3) My Java code.
...
String sql = "select fn_cad_servico_listar(null, null, null, null, null)";
PreparedStatement ps = null;
ResultSet rsCall = null;
Statement st = null;
ResultSet rs = null;
try {
ps = this.conn.prepareStatement(sql);
rsCall = ps.executeQuery();
if(rsCall != null && rsCall.next()) {
sql = "fetch all in \"" + rsCall.getString(1) + "\"";
st = this.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = st.executeQuery(sql);
while(rs != null && rs.next()) {
System.out.println("Year: " + rs.getInt("num_ano_exercicio").toString());
System.out.println("Month: " + rs.getInt("num_mes_exercicio").toString());
System.out.println("Activity: " + rs.getInt("cod_atividade").toString());
System.out.println("Service: " + rs.getInt("cod_servico").toString());
}
}
}
catch(Exception ex) {
System.out.println(ex.getMessage());
}
...
Thanks.
Marcelo
