0

Procedure test:

create or replace PROCEDURE test(
   num IN NUMBER,
   res out SYS_REFCURSOR
)
AS
   query varchar2(500);
BEGIN   
   query := 'SELECT 1,2 FROM dual';
   OPEN res FOR query;
   exception WHEN others   then
       DBMS_OUTPUT.PUT_LINE('ERROR '||SUBSTR(SQLERRM, 1 , 200));
end test;

It works, when calling from SQLDeveloper:

DECLARE
   num NUMBER;
   resCur SYS_REFCURSOR;
   type r_individual is record (
       A NUMBER, 
       B NUMBER
   );
   p_individual r_individual;
BEGIN
   num := 1;
   dbms_output.put_line('LANZA');
   test(num, resCur);
   loop
       dbms_output.put_line('LOOP');
       fetch resCur into p_individual;
       exit when resCur%notfound;
       dbms_output.put_line(p_individual.A);
       dbms_output.put_line(p_individual.B);
   end loop;
END;

The problem is when calling from Java:

CallableStatement sql = connection.prepareCall('{call test( ?, ?)}')
sql.setInt("num", 1)
sql.registerOutParameter("res", OracleTypes.CURSOR)        
sql.execute()

ResultSet rs = (ResultSet) sql.getObject("resCur")
while (rs.next()) {
   log.debug(rs.getInt(1))
}
rs.close()

I try to call a similar procedure with an out parameter type NUMBER, and it works. The problem seems to be when the out parameter is SYS_REFCURSOR.

Can you help me? Thanks a lot.

Drazhan
  • 1
  • 1
  • The code you've posted isn't valid; with single quotes replaced with double quotes, consistent names, and missing semicolons, then it works. So you seem to be running something different, and you've maybe lost crucial information rewriting it to post. Please edit your question to include a [mre] - one that we can run. – Alex Poole Aug 31 '22 at 13:19
  • @AlexPoole My code is in grails, so that single quotes works and that was not the problem. – Drazhan Sep 06 '22 at 08:50

0 Answers0