2 Replies Latest reply: Jun 6, 2013 5:47 AM by 1010342 RSS

    Re: How to pass null or empty array via setPlsqlIndexTable

    1010342

      I know this thread was back in 2008 but I have run into a similar issue and wondered if anyone had a solution. Upgrading code to jdk1.6 and started using ojdbc6.jar.

      Our current code looks something like below and works fine with ojdbc14.jar.

      ps = (OracleCallableStatement) mConn.prepareCall("begin index_by_table_test.do_stuff(:1); end;");
      int[] blah = null;
      ps.setPlsqlIndexTable(1, blah, 0, 0, OracleTypes.NUMBER, 100);
      ps.execute();

      The runtime implementation of OraclePreparedStatement.setPlsqlIndexTableInternal has changed in ojdbc6.jar and now contains a specific null check so get a sqlException when executing the statement. I can do the following

      ps = (OracleCallableStatement) mConn.prepareCall("begin index_by_table_test.do_stuff(:1); end;");
      int[] blah = new in[]{0};
      ps.setPlsqlIndexTable(1, blah, 1, 1, OracleTypes.NUMBER, 100);
      ps.execute();

      but need to alter the StoredProcedure to deal with the new value. Is there any other way round this other than coding around the change in the api?

      Mod: branched out of a zombie res.

      Edited by: PhHein on 22.05.2013 11:50

        • 1. Re: How to pass null or empty array via setPlsqlIndexTable
          rp0428
          >
          I know this thread was back in 2008 but I have run into a similar issue and wondered if anyone had a solution. Upgrading code to jdk1.6 and started using ojdbc6.jar.

          Our current code looks something like below and works fine with ojdbc14.jar.

          ps = (OracleCallableStatement) mConn.prepareCall("begin index_by_table_test.do_stuff(:1); end;");
          int[] blah = null;
          ps.setPlsqlIndexTable(1, blah, 0, 0, OracleTypes.NUMBER, 100);
          ps.execute();

          The runtime implementation of OraclePreparedStatement.setPlsqlIndexTableInternal has changed in ojdbc6.jar and now contains a specific null check so get a sqlException when executing the statement. I can do the following

          ps = (OracleCallableStatement) mConn.prepareCall("begin index_by_table_test.do_stuff(:1); end;");
          int[] blah = new in[]{0};
          ps.setPlsqlIndexTable(1, blah, 1, 1, OracleTypes.NUMBER, 100);
          ps.execute();

          but need to alter the StoredProcedure to deal with the new value. Is there any other way round this other than coding around the change in the api?
          >
          To deal with 'what' new value? Why are you defining a one-element array and passing 'maxLen' and 'curLen' of '1' if you don't want a value?
          Define a non-null, but empty, array
          int [] blah = new int [0];
          • 2. Re: How to pass null or empty array via setPlsqlIndexTable
            1010342
            Thanks for the reply to my query.

            I had tried int [] blah = new int [0]; but that threw a SQLException due to a zero length array.

            In the end I resolved this using named binding when defining the input parameters and just ignored the null values.

            Thanks for your help though.