在中选择使用Oracle参数问题在中选择使用Oracle参数问题(problem using Ora

将数字的字符串转换成SQL查询时，我有一个问题

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

...要么：

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

......与此代码：

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

所以，如果我运行此查询我得到：

ORA-01722：无效数字

但是，如果我只插入一个数字，即“3857”将返回查询的数据确定。

Answer 1:

要通过一组值，你需要使用Oracle的表或数组类型。

起初，你创建（如NUMBER）表类型：

CREATE TYPE number_table AS TABLE OF NUMBER;

当您创建用于查询的参数，声明它作为关联PL / SQL数组：

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

然后分配一些值：

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

和您的查询需要转换：

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

Answer 2:

这不是参数是如何工作的。你不能指定一个“集”作为参数，你必须装配在字符串中的SQL查询。并观看了SQL注入。

此外，你可能想看看这些：

是否有可能使用查询参数来填充IN关键字
参数化IN子句的SQL

更新

科多的回答对甲骨文非常有趣的方法。我现在不能测试它，但它肯定看起来很有希望。

有一个非常类似的问题在这里：的OracleParameter，并在条款，如@DCookie指出。这不是一个确切的重复，因为当阵列中的项目发生改变的类型，SQL投也改变。

Answer 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'));

希望这对你的作品。我已经看到了一些其他的代码，不会这一点。对于什么它的价值，这是很容易在Microsoft SQL Server做

HARV Sather