problem using Oracle parameters in SELECT IN

I have a problem when inserting a string of numbers into sql query

  SELECT * 
    FROM tablename a 
   WHERE a.flokkurid IN (3857,3858,3863,3285) 
ORDER BY sjodategund, rodun

...or:

  SELECT * 
    FROM tablename a 
   WHERE a.flokkurid IN (:strManyNumbers) 
ORDER BY sjodategund, rodun

...with this code:

using (OracleCommand sel = new OracleCommand(SQL, connectionstring)) {
  sel.Parameters.Add(":strManyNumbers", 
                      OracleDbType.Varchar2, 
                      "Client",
                      ParameterDirection.Input);
}

So if i run this query i get:

ORA-01722: invalid number

but if i insert just one number, i.e. "3857" it will return query OK with data.

回答1:

To pass a set of values, you need to use Oracle's table or array types.

At first, you create a table type (e.g. for NUMBER):

CREATE TYPE number_table AS TABLE OF NUMBER;

When you create the parameter for the query, declare it as an associative PL/SQL array:

OracleParameter param1 = new OracleParameter(); 
param1.OracleDbType = OracleDbType.Int32; 
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

Then assign some values:

param1 = new int[] { 3857, 3858, 3863, 3285 };

And your query needs a cast:

SELECT * FROM tablename a 
where a.flokkurid in (TABLE(CAST(:manyNumbers AS number_table)))
order by sjodategund, rodun

回答2:

That's not how parameters work. You cannot specify a "set" as a parameter, you have to assemble the SQL query in the string. And watch out for SQL Injection.

In addition, you might want to take a look at these:

Is it possible to use query parameters to fill the IN keyword
Parameterize an SQL IN clause

Update

Codo's answer has a very interesting approach for Oracle. I cannot test it right now, but it sure looks promising.

There's a very similar question here: OracleParameter and IN Clause , as pointed out by @DCookie. It's not an exact duplicate because when the type of the item in array changes, the SQL cast also changes.

回答3:

CREATE OR REPLACE PACKAGE IH_FORMS_TRIAL.STRING_TO_TABLE IS
  type grs_list_row is record ( varchar_list varchar2(512), int_list number,  date_list date );
  type grs_list_tab is table of grs_list_row;

FUNCTION ft_string_to_table(av2_list varchar2, av2_delimiter varchar2 := ',', av2_list_type varchar2 := 'V',  av2_date_mask varchar2 := 'DD-MON-YY')  return grs_list_tab   PIPELINED;

END STRING_TO_TABLE;
/


CREATE OR REPLACE package body IH_FORMS_TRIAL.STRING_TO_TABLE IS
    FUNCTION ft_string_to_table(av2_list varchar2, av2_delimiter varchar2 := ',', av2_list_type varchar2 := 'V',  av2_date_mask varchar2 := 'DD-MON-YY')  return grs_list_tab   PIPELINED
             IS


    /**********************************************************************************************************
    http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Table_Functions_Cursor_Expressions/Pipelined_Table_Functions.htm
    http://www.akadia.com/services/ora_pipe_functions.html
    PIPLELINED TABLE FUNCTION


    PURPOSE -   
    ------------------
            This function takes a string as input and returns a table.
            The table that is returned will normally be used in an SQL "IN" clause
    =====================================================================================
    ARGUMENTS
    ------------------
            av2_list - this is a comma delimited list of values that will be converted into single rows of a table
            av2_delimiter - this is a character value and should only be one character long.
                                         It is the delimiter that is between valid values in the av2_list
                                         The default value is a comma ','
            av2_list_type - This function can return various types of lists or tables
                           For this parameter
                                   A value of 'V' will return a table of varchar2
                                   A value of 'I' will return a table of integers
                                   A value of 'D' will return a table of dates
            av2_date_mask - This is required if the value of av2_list_type is 'D' for date
                            The date mask will be used by the Oracle built-in TO_DATE function
                            A default value of 'DD-MON-YY' is used


    =====================================================================================
    RETURNS
                            Table of values for input to an IN portion of a WHERE clause
    =====================================================================================
    EXAMPLES

    SELECT * FROM <TABLE> WHERE <VARCHAR_COLUMN> IN (select varchar_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454')));
    SELECT * FROM <TABLE> WHERE <INTEGER_COLUMN> IN (select int_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('1,2,3,4,5,6,7,8,9', ',', 'I')));
    SELECT * FROM <TABLE> WHERE <DATE_COLUMN> IN (select date_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MM-DD')));

    =====================================================================================
    TEST CASES


             select varchar_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454', ',', 'V'));
             select int_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('1,2,3,4,5,6,7,8,9', ',', 'I'));
             select date_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('04-mar-10, 05-mar-10', ',', 'D'));
             select date_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MM-DD'));


             test using and invalid list type
             Use Y instead of V, I or D
             Should produce an error
             select varchar_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454', ',', 'Y'));


             test using a date format that does not match the date format passed
             Should produce an error
             select date_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MON-DD'));
             select date_list from  table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-MAR-04, 2010-MAR-05', ',', 'D', 'YYYY-MM-DD'));

    ----------

    =====================================================================================
    REVISION HISTORY
      Called by misc systems
      ----------------------------------------------------------------
      Modification History

      Date               User            Description
      ------------       --------------  ----------------------------------
      2006-03-03         HarvS           Initial Release
      2010-04-09         HarvS           Translated from SQL Server to ORACLE
                                         Combined functions that returned lists of varchar, integer, and date into one function with optional parameters

     REVISION HISTORY
      ----------------
      Build Version    -  11.02.01.001
      Build Date       -  08-June-2010
      Modified By      -  has986
      Description      -  Created                                             



    ******************************************************************************/

                --local variable of type grs_list_row
          lrs_row grs_list_row;


                 E_INVALID_LIST_TYPE EXCEPTION;

        li_delimiter_position int;
        li_previous_delimiter_position int;
        lv2_value varchar2(512);

    BEGIN

           if av2_list_type not in ('V', 'I', 'D') THEN
                 raise E_INVALID_LIST_TYPE;
            end if;

        li_delimiter_position := 1;
        li_previous_delimiter_position := 1;

        li_delimiter_position := INSTR(av2_list, av2_delimiter,  li_delimiter_position);

        while li_delimiter_position > 0 loop
              lv2_value := substr(av2_list, li_previous_delimiter_position, (li_delimiter_position - li_previous_delimiter_position));
               --Trim the value
              lv2_value := RTRIM(LTRIM(lv2_value));

              if length(lv2_value) > 0 THEN
                  if av2_list_type = 'V' then --varchar
                     lrs_row.varchar_list := lv2_value;
                  elsif av2_list_type = 'I' then --integer
                     lrs_row.int_list := to_number(lv2_value);
                  elsif av2_list_type = 'D' then --date
                     lrs_row.date_list := to_date(lv2_value, av2_date_mask);
                  end if;
                  pipe row ( lrs_row );
              END IF;


              --set the new delimiter positions
            li_previous_delimiter_position := li_delimiter_position + 1;
            li_delimiter_position := INSTR(av2_list, av2_delimiter,  li_delimiter_position + 1);
        END loop;

          --Get the last value
          lv2_value := SUBSTR(av2_list, li_previous_delimiter_position, length(av2_list));

        --Trim the value
        lv2_value := RTRIM(LTRIM(lv2_value));

        if length(lv2_value) > 0 THEN
        --Insert the value into the in memory table
              if av2_list_type = 'V' then --varchar
                 lrs_row.varchar_list := lv2_value;
              elsif av2_list_type = 'I' then --integer
                 lrs_row.int_list := to_number(lv2_value);
              elsif av2_list_type = 'D' then --date
                 lrs_row.date_list := to_date(lv2_value, av2_date_mask);
              end if;
              pipe row ( lrs_row );
         END IF;

         return;


        EXCEPTION

             WHEN E_INVALID_LIST_TYPE then


                                    /*
                                        The developer should be notified of this error during the development phase.
                                    */
                    raise_application_error (-20001, av2_list_type || ' is not a valid type. Valid types are (V, I, or D)' );


            WHEN OTHERS THEN
                        RAISE;
    END ft_string_to_table;
end string_to_table;
/




select * FROM table( string_to_table.ft_string_to_table('1, 2, 3', ',', 'I'));

select * FROM table( string_to_table.ft_string_to_table('fred, wilma, betty, barney', ',', 'V'));

select * FROM table( string_to_table.ft_string_to_table('2011-5-1, 1950-1-1, 1960-1-2, 2023-12-1', ',', 'D', 'yyyy-mm-dd'));

Hope this works for you. I have seen some other code that does this as well. For what its worth, this is much easier to do in Microsoft SQL Server

Harv Sather