9 Replies Latest reply: Sep 5, 2012 1:03 PM by rp0428 RSS

    How to call a Stored Function with OUT parameter of %rowType from Java

    960161

      Hi everyone,
      I'm getting crazy trying to make this work.
      I have a function (not developed by me) in Oracle 10g declared in this way:
      -------------------------------------------------------------------------------------------
      [...]
      type tab_RLSSP is table of TB_RLSSP_STOSTPRPAR_CL%ROWTYPE index by binary_integer;
      FUNCTION DBF_PERL_LISTA_PRATICHE (
      p_id_va IN NUMBER,
      p_seq_partita IN NUMBER,
      p_trattamento IN CHAR,
      lrec_RLSSP OUT tab_RLSSP ) RETURN NUMBER;
      [...]
      --------------------------------------------------------------------------------------------

      And here is the code snipplet of my java method:

      -------------------------------------------------------------------------------------------
      sql="{? = call "+SCHEMA+PACKAGE+"."+FUNCTION_LIST+"(?,?,?,?)}";
      cs=connection.prepareCall(sql);

      cs.registerOutParameter(1, OracleTypes.NUMBER);
      cs.setLong(2,idVATitolare);
      cs.setLong(3,seqPartita);
      cs.setString(4,trattamento);// Caso Decesso
      cs.registerOutParameter(5, OracleTypes.OTHER);

      cs.executeQuery();
      result = (ResultSet) cs.getObject(5);
      if (result.next())
      listaPratiche.add(readPraticaPartita(result));


      -------------------------------------------------------------------------------------------

      The result (exception thrown at executeQuery with statement generated as
      SQL : {? = call PEDBA.DBK_PERL_RATEI_SUPPLETIVI.DBF_PERL_LISTA_PRATICHE(?,?,?,?)}:
      -------------------------------------------------------------------------------------------

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

      Changing to :

      sql="{call ? := "+SCHEMA+PACKAGE+"."+FUNCTION_LISTA+"(?,?,?,?)}";

      leading to

      SQL : {call ? := call PEDBA.DBK_PERL_RATEI_SUPPLETIVI.DBF_PERL_LISTA_PRATICHE(?,?,?,?)}

      don't change anything.




      What's wrong? Any suggestion?

      Edited by: 957158 on 5-set-2012 9.06

        • 1. Re: How to call a Stored Function with OUT parameter of %rowType from Java
          Tolls
          957158 wrote:
          [...]
          type tab_RLSSP is table of TB_RLSSP_STOSTPRPAR_CL%ROWTYPE index by binary_integer;
          FUNCTION DBF_PERL_LISTA_PRATICHE (
          ...
          lrec_RLSSP OUT tab_RLSSP ) RETURN NUMBER;
          [...]
          You're trying to use a PL/SQL type in JDBC.
          The driver only recognises SQL types.
          • 3. Re: How to call a Stored Function with OUT parameter of %rowType from Java
            gimbal2
            You'd have to check the OJDBC driver manual for driver specific features.
            • 4. Re: How to call a Stored Function with OUT parameter of %rowType from Java
              960161
              Here is an excerpt taken from another method not written by me - I couldn't test it but it is on CVS, so I think should be fine...

              ---------------------------------------------------------------------------------------------------------
              ResultSet rs = null;
                        CallableStatement cs = null;
                        StringBuffer s = new StringBuffer();
                        
                   
                             log.debug("***** metodo findVociCedolino di ServiziDAOImpl"+ " seqPartita: " + seqPartita+ " dataDa: " + DateUtility.dateToDateString(dataDa) + " dataFineRecupero: " + DateUtility.dateToDateString(dataFineRecupero));
                             
                             String anno = CessPensUtil.format(new Integer(CessPensUtil.getAnno(dataDa)),"0000");
                             String mese = CessPensUtil.format(new Integer(CessPensUtil.getMese(dataDa)),"00");
                             
                             
                             
                             
                             s.append("{ call ?:= "+schema("PEEPAPPL")+"DBK_PEEP_CESS_RECUP_RATEI.DBF_PEEP_PAGAMENTI(?,?,?,?) }");
                             log.debug("***** SQL: " + s.toString());
                             
                             cs = conn.prepareCall(s.toString());
                             cs.registerOutParameter(1,Types.NUMERIC);
                             cs.setLong(2,seqPartita);
                             cs.setString(3,anno);
                             cs.setString(4,mese);
                             cs.registerOutParameter(5,OracleTypes.CURSOR);
                             cs.execute();

              --------------------------------------------------------------------------------------------------------

              Taking for granted that it works, I wonder what's different, probably the output is defined as Cursor...
              • 5. Re: How to call a Stored Function with OUT parameter of %rowType from Java
                960161
                Sounds like there's no way to read a RowType as output? Should I define the stored procedure output as a Cursor?
                • 6. Re: How to call a Stored Function with OUT parameter of %rowType from Java
                  Joe Weinstein-Oracle
                  The difference is in calling execute(), not executeQuery() which has to return a result set,
                  and Oracle JDBC can't return a result set from a stored procedure, except as an output
                  parameter. And yes, whoever wrote that other example should get rid of the unneeded
                  StringBuffer, which is simply built with hard-coded strings. One hardcoded string is enough.
                  • 7. Re: How to call a Stored Function with OUT parameter of %rowType from Java
                    960161
                    Sorry, I'm calling a function...
                    I already changed to
                    "cs.execute()"
                    but nothing happens...
                    The fact is: how is the other example supposed to work? Maybe the output is defined ad a cursor? I can't check the stored function code...
                    • 8. Re: How to call a Stored Function with OUT parameter of %rowType from Java
                      Joe Weinstein-Oracle
                      Yes, in that other code, the procedure being called certainly defines the output parameter as a CURSOR.
                      • 9. Re: How to call a Stored Function with OUT parameter of %rowType from Java
                        rp0428
                        >
                        Taking for granted that it works, I wonder what's different, probably the output is defined as Cursor...
                        >
                        You mean because of this line?
                        cs.registerOutParameter(5,OracleTypes.CURSOR);
                        You can either use a cursor or use a function that returns a SQL type instead of the PL/SQL type.

                        Here is sample code using a cursor
                        CREATE OR REPLACE TYPE SCOTT.local_type IS OBJECT (
                            empno   NUMBER(4),
                            ename   VARCHAR2(10));
                        /
                        
                        CREATE OR REPLACE TYPE SCOTT.local_tab_type IS TABLE OF local_type;
                        /
                        
                        
                        CREATE OR REPLACE PACKAGE SCOTT.test_refcursor_pkg
                         AS
                            TYPE my_ref_cursor IS REF CURSOR;
                             -- add more cursors as OUT parameters
                             PROCEDURE   test_proc(p_ref_cur_out OUT test_refcursor_pkg.my_ref_cursor); 
                         END test_refcursor_pkg;
                        /
                        
                        CREATE OR REPLACE PACKAGE BODY SCOTT.test_refcursor_pkg
                         AS
                             PROCEDURE  test_proc(p_ref_cur_out OUT test_refcursor_pkg.my_ref_cursor)
                             AS
                                l_recs local_tab_type;
                             BEGIN
                             
                                 -- Get the records to modify individually.
                                 SELECT local_type(empno, ename) BULK COLLECT INTO l_recs
                                 FROM EMP;
                             
                                 -- Perform some complex calculation for each row.
                                 FOR i IN l_recs.FIRST .. l_recs.LAST
                                 LOOP
                                     DBMS_OUTPUT.PUT_LINE(l_recs(i).ename); 
                                 END LOOP;
                            
                                 -- Put the modified records back into the ref cursor for output.   
                                 OPEN p_ref_cur_out FOR 
                                 SELECT * from TABLE(l_recs);       
                             
                                 -- open more ref cursors here before returning
                                 
                             END test_proc;
                        
                         END;
                        /
                        
                        
                        SET SERVEROUTPUT ON SIZE 1000000
                        DECLARE
                          l_cursor  test_refcursor_pkg.my_ref_cursor;
                          l_ename   emp.ename%TYPE;
                          l_empno   emp.empno%TYPE;
                        BEGIN
                          test_refcursor_pkg.test_proc (l_cursor);
                                    
                          LOOP 
                            FETCH l_cursor
                            INTO  l_empno, l_ename;
                            EXIT WHEN l_cursor%NOTFOUND;
                            DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno);
                          END LOOP;
                          CLOSE l_cursor;
                        END;
                        /