1 2 Previous Next 24 Replies Latest reply: May 4, 2012 12:36 PM by rp0428 RSS

    CallableStatement problem

    user606303
      The code below creates a connection to the Oracle database. In that database there is a package named: LSCMS_AUDIT_PKG that has a stored procedure named: RETURN_SUPPLIER_CODE which is listed below:
         procedure RETURN_SUPPLIER_CODE (p_cursor in out REF_CURSOR_TYPE.cursor_type)
         is
         begin
            OPEN p_cursor for
               select supplier_code
                 from LSCMS_SUPPLIER_CODE
                 ORDER BY supplier_code;
      When I execute this code at this line:

      cs.registerOutParameter(3, OracleTypes.CURSOR);

      It throws this exception:

      Exception: Invalid column index [Ljava.lang.StackTraceElement;@fdf8ca


      If I change the above line from 2 to 3 "?"s I get this exception:

      Exception: ORA-06550: line 1, column 7:
      PLS-00306: wrong number or types of arguments in call to 'RETURN_SUPPLIER_CODE'
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored
      [Ljava.lang.StackTraceElement;@11261b1

      Any clues how to make this all work? Thanks in advance.
      package DBConnect;
      
      import java.sql.CallableStatement;
      import java.sql.Connection;
      import oracle.jdbc.OracleTypes;
      import oracle.jdbc.pool.OracleDataSource;
      
      public class DBConnect3 
      {
        public static void main(String[] args) 
        {
          try 
          {
              OracleDataSource ds = new OracleDataSource();
              ds.setDriverType("thin");
              ds.setServerName("157.184.108.226");
              ds.setPortNumber(1521);
              ds.setDatabaseName("FVDEV"); // sid
              ds.setUser("IRMWS");
              ds.setPassword("password");
              Connection conn = ds.getConnection();
              if (conn.isClosed()) {System.out.println("Conn closed");}
      //the connection to the database is made
              CallableStatement cs = conn.prepareCall
                   ("{CALL LSCMS_AUDIT_PKG.RETURN_SUPPLIER_CODE" +
                   "(?,?)}");
              cs.setString(1, "5470039");
              cs.setString(2, "PROD"); 
              cs.registerOutParameter(3, OracleTypes.CURSOR);  
              cs.execute();
      Edited by: user606303 on May 2, 2012 11:34 AM
        • 1. Re: CallableStatement problem
          mycoffee
          Try this

          ? = call function(?,?)

          cs.registerOutParameter(1, ***);
          cs.setString(2,**);
          cs.setString(3,**);
          • 2. Re: CallableStatement problem
            Tolls
            It could be because it's late in the day, but for the life of me I can only count the one parameter to your procedure, so where you are finding the input parameters from I don't know.
            • 3. Re: CallableStatement problem
              mycoffee
              Tolls wrote:
              It could be because it's late in the day, but for the life of me I can only count the one parameter to your procedure, so where you are finding the input parameters from I don't know.
              Oh yeah. I didn't pay attention on it
              (p_cursor in out REF_CURSOR_TYPE.cursor_type)

              only 1 in and 1 out
              • 4. Re: CallableStatement problem
                Tolls
                mycoffee wrote:
                Tolls wrote:
                It could be because it's late in the day, but for the life of me I can only count the one parameter to your procedure, so where you are finding the input parameters from I don't know.
                Oh yeah. I didn't pay attention on it
                (p_cursor in out REF_CURSOR_TYPE.cursor_type)

                only 1 in and 1 out
                Oh good. I'm not going mad!
                :)
                And, of course, both are cursors.
                • 5. Re: CallableStatement problem
                  user606303
                  I have modified the code as some have suggested. It requires "cs.registerOutParameter(3, OracleTypes.CURSOR);" to be the third parameter.

                  I get this message:

                  Exception: ORA-06550: line 1, column 13:
                  PLS-00306: wrong number or types of arguments in call to 'RETURN_ORDERS_SRF'
                  ORA-06550: line 1, column 7:
                  PL/SQL: Statement ignored
                  [Ljava.lang.StackTraceElement;@e90891

                  at the cs.execute; line.

                  Java documentation says that I should use an in parameter of String when the type in Oracle in varchar2, which is my case. I'm a little surprised I don't have to name the IN OUT parameter precicly as Oracle names them before I pass them to the stored procedure.

                  Any more clues would be appreciated.
                  package DBConnect;
                  
                  import java.sql.CallableStatement;
                  import java.sql.Connection;
                  import java.sql.ResultSet;
                  
                  import oracle.jdbc.OracleTypes;
                  import oracle.jdbc.pool.OracleDataSource;
                  
                  public class DBConnect3 
                  {
                    public static void main(String[] args) 
                    {
                      try 
                      {
                           
                  //   procedure RETURN_ORDERS_SRF (p_source_ref_number IN varchar2,
                  //           p_sys_name          IN VARCHAR2,
                  //           p_cursor            in out REF_CURSOR_TYPE.cursor_type)     
                          OracleDataSource ds = new OracleDataSource();
                          ds.setDriverType("thin");
                          ds.setServerName("157.184.108.226");
                          ds.setPortNumber(1521);
                          ds.setDatabaseName("FVDEV"); // sid
                          ds.setUser("IRMWS");
                          ds.setPassword("password");
                          Connection conn = ds.getConnection();
                          if (conn.isClosed()) {System.out.println("Conn closed");}
                          CallableStatement cs = conn.prepareCall
                               ("{? = call LSCMS_AUDIT_PKG.RETURN_ORDERS_SRF(?,?)}");
                          cs.setString(1, "5481469");
                          cs.setString(2, "PROD");
                          cs.registerOutParameter(3, OracleTypes.CURSOR);
                          cs.execute();   
                          ResultSet rs = (ResultSet) cs.getObject(1);
                  • 6. Re: CallableStatement problem
                    mycoffee
                    Read post 3, 4, 5

                    You need to corect your store procedure to accept what you want to send

                    The Procedure accepts 1 in parm only and send 1 parm out only (look the PL/SQL code, not java)
                    • 7. Re: CallableStatement problem
                      user606303
                      My bad. I changed the stored procedure. It is listed in my last post as:

                      // procedure RETURN_ORDERS_SRF (p_source_ref_number IN varchar2,
                      // p_sys_name IN VARCHAR2,
                      // p_cursor in out REF_CURSOR_TYPE.cursor_type)

                      This has 2 IN parameters, which I pass in as Strings, and one IN OUT parameter which I believe I have handled properly. There is some discussion on the web about how to handle IN OUT parameters. It said that each IN OUT parameter needed a CallableStatementObject.setXXX and a CallableStatementObject.registerOutParameter but this got me no where.

                      I tried this:

                      cs.registerOutParameter(1, ***);
                      cs.setString(2,**);
                      cs.setString(3,**);

                      and it threw an error.


                      So, I feel like I am close, but not there yet.

                      Any insight would be appreciated.
                      • 8. Re: CallableStatement problem
                        mycoffee
                        user606303 wrote:
                        My bad. I changed the stored procedure. It is listed in my last post as:

                        // procedure RETURN_ORDERS_SRF (p_source_ref_number IN varchar2,
                        // p_sys_name IN VARCHAR2,
                        // p_cursor in out REF_CURSOR_TYPE.cursor_type)

                        This has 2 IN parameters, which I pass in as Strings, and one IN OUT parameter which I believe I have handled properly. There is some discussion on the web about how to handle IN OUT parameters. It said that each IN OUT parameter needed a CallableStatementObject.setXXX and a CallableStatementObject.registerOutParameter but this got me no where.

                        I tried this:

                        cs.registerOutParameter(1, ***);
                        cs.setString(2,**);
                        cs.setString(3,**);

                        and it threw an error.


                        So, I feel like I am close, but not there yet.

                        Any insight would be appreciated.
                        Yes, it is close.
                        right now you have 4 parms , not 3 parms
                        IN
                        IN
                        IN OUT
                        You need to change it to
                        IN
                        IN
                        OUT
                        and then change you cs.set*** to match them
                        setString 1
                        setString 2
                        registerOut 3

                        not registerOutParameter(1, ...

                        Edited by: mycoffee on May 2, 2012 12:57 PM
                        • 9. Re: CallableStatement problem
                          rp0428
                          >
                          right now you have 4 parms , not 3 parms
                          >
                          NO! This code
                          // procedure RETURN_ORDERS_SRF (p_source_ref_number IN varchar2,
                          // p_sys_name IN VARCHAR2,
                          // p_cursor in out REF_CURSOR_TYPE.cursor_type)
                          has THREE parms - p_source, p_sys_name and p_cursor
                          The third parm is of type 'IN OUT'.
                          • 10. Re: CallableStatement problem
                            rp0428
                            >
                            My bad. I changed the stored procedure. It is listed in my last post as:

                            // procedure RETURN_ORDERS_SRF (p_source_ref_number IN varchar2,
                            // p_sys_name IN VARCHAR2,
                            // p_cursor in out REF_CURSOR_TYPE.cursor_type)

                            This has 2 IN parameters, which I pass in as Strings, and one IN OUT parameter which I believe I have handled properly.
                            >
                            You need to edit your previous posts to correct the code or post the corrected code.

                            Because both you and mycoffee seem to be having trouble counting from 1 to 3.
                            If this is your Java code
                                    CallableStatement cs = conn.prepareCall
                                         ("{CALL LSCMS_AUDIT_PKG.RETURN_SUPPLIER_CODE" +
                                         "(?,?)}");
                                    cs.setString(1, "5470039");
                                    cs.setString(2, "PROD"); 
                                    cs.registerOutParameter(3, OracleTypes.CURSOR);  
                            Then it shows your procedure call having TWO parameters but you are trying to set THREE values.
                            So you have 'wrong number or types of arguments'.

                            If the top code shows that your procedure has THREE parms then you have to change the java code to have three '?' placeholders for those parms.

                            Either edit your previous posts to correct the code or post the corrected code for both the procedure and Java.
                            • 11. Re: CallableStatement problem
                              ++sja
                              Oh. My. Jeebus.
                              • 12. Re: CallableStatement problem
                                user606303
                                I appreciate all the input on this!!

                                When I change my code thus:
                                        OracleDataSource ds = new OracleDataSource();
                                        ds.setDriverType("thin");
                                        ds.setServerName("157.184.108.226");
                                        ds.setPortNumber(1521);
                                        ds.setDatabaseName("FVDEV"); // sid
                                        ds.setUser("IRMWS");
                                        ds.setPassword("qw2$3rpt08df");
                                        Connection conn = ds.getConnection();
                                        if (conn.isClosed()) {System.out.println("Conn closed");}
                                        CallableStatement cs = conn.prepareCall
                                             ("{? = call LSCMS_AUDIT_PKG.RETURN_ORDERS_SRF(?,?,?)}");
                                        cs.setString(1, "5481469");
                                        cs.setString(2, "PROD");
                                        cs.registerOutParameter(3, OracleTypes.CURSOR);
                                        cs.execute();  
                                it throws this exception:

                                Exception: Missing IN or OUT parameter at index:: 4 [Ljava.lang.StackTraceElement;@1944ae

                                I'm not having any success with adding a 3rd setXXX IN parameter and a 4th registerOutParameter parameter.

                                Also, my CallableStatementObject only has a registerOutParameter method, not a registerOut method.
                                • 13. Re: CallableStatement problem
                                  mycoffee
                                  You keep confusing ... me too ... I add the number next to the question marks for easy reference and assume you are using the same procedure

                                  "{?(1)= call LSCMS_AUDIT_PKG.RETURN_ORDERS_SRF(?(2),?(3),?(4))}"

                                  (1) is out should register out for 1,( not for 3)

                                  2,3,4 are 'in' should setString(2,**), setString(3,**) ... What about 4? What do you want to set for the cursor? If you don't have a cursor to set, make it OUT only and only set parm 2 and 3
                                  • 14. Re: CallableStatement problem
                                    Tolls
                                    Your method is a PL/SQL procedure and hence has no return parameter, so remove the '? = ' part.
                                    That leavse you with 3 parameters.
                                    1 = IN String
                                    2 = IN String
                                    3 = OUT cursor (presuming you have changed it from IN OUT to simply OUT)
                                    So that's two setString() calls and one registerOutParameter() call.

                                    It's not difficult.
                                    1 2 Previous Next