SQLj iterators and SELECT ... WHERE ... IN clauses.

I'm not sure if I'm posting to the right group, but I thought you folks might know about this.

I'm using a Java GUI to pull data from an Oracle 9i database. We're using SQLj to generate our Java classes for table iterators. Simple iterators, like:

    SELECT * FROM some_table;

work fine. The problem is that, if I try to do something like

    SELECT * FROM some_table WHERE some_field IN ('a', 'b');

I get some strange behavior. The problem is that I am building up a set of values that the some_field must be in, and passing the set to database as a string. The SQLj code looks like this:

            CarrierSsms3Iterator carrierSsms3Iterator = null;

            // create a string list of IDs
            StringBuffer carrierIdBuffer = new StringBuffer();
            BigDecimal[] carrierIdArray = carrierIds.getArray();
            for( int i = 0; i < carrierIdArray.length; ++i )
            {
                BigDecimal carrierId = carrierIdArray[ i ];
                if( carrierId.intValue() < 0 )


                {

                    break;
                }
                if( i != 0 )


                {

                    carrierIdBuffer.append( ", " );
                }
                carrierIdBuffer.append( carrierId );
            }
            String carrierIdStr = carrierIdBuffer.toString();

            #sql [getConnectionContext()] carrierSsms3Iterator
                = { SELECT
                        ssc_name,
                        carrier_id,
                        display_colour
                    FROM
                        carrier_ssms3_view
                    WHERE
                         carrier_id IN ( :carrierIdStr )
                  };

Note that carrier_id is an integer type. When I try to execute the generated method, I get

    java.sql.SQLException: ORA-01722: invalid number

The reason appears to be that, if the list contains more than one ID (eg. "1, 2, 3"), it tries to parse the whole string as if it were one number. I have tried putting single quotes around each ID (eg. "'1', '2', '3'"), with the same results. I've tried giving it an Integer[] array, rather than a String, but it just complains about the type. So is there some other way to give it a list of numbers for the IN clause?

Dave. Received on Thu Apr 17 2003 - 19:07:27 CEST