Oracle stored procedure with parameters for IN clause

Question

How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause?

This is what I am trying to achieve. I do not know how to declare in PLSQL for passing a variable list of primary keys of the rows I want to update.

FUNCTION EXECUTE_UPDATE
  ( <parameter_list>
   value IN int)
  RETURN  int IS
BEGIN 
    [...other statements...]
    update table1 set col1 = col1 - value where id in (<parameter_list>) 

    RETURN SQL%ROWCOUNT ;
END;

Also, I would like to call this procedure from C#, so it must be compatible with .NET capabilities.

Thanks, Robert

Trebuchet

Answer 1

Using CSV is probably the simplest way, assuming you can be 100% certain that your elements won't themselves contain strings.

An alternative, and probably more robust, way of doing this is to create a custom type as a table of strings. Supposing your strings were never longer than 100 characters, then you could have:

CREATE TYPE string_table AS TABLE OF varchar2(100);

You can then pass a variable of this type into your stored procedure and reference it directly. In your case, something like this:

FUNCTION EXECUTE_UPDATE(
    identifierList string_table,
    value int)
RETURN int
IS
BEGIN

    [...other stuff...]

    update table1 set col1 = col1 - value 
    where id in (select column_value from table(identifierList));

    RETURN SQL%ROWCOUNT;

END

The table() function turns your custom type into a table with a single column "COLUMN_VALUE", which you can then treat like any other table (so do joins or, in this case, subselects).

The beauty of this is that Oracle will create a constructor for you, so when calling your stored procedure you can simply write:

execute_update(string_table('foo','bar','baz'), 32);

I'm assuming that you can handle building up this command programatically from C#.

As an aside, at my company we have a number of these custom types defined as standard for lists of strings, doubles, ints and so on. We also make use of Oracle JPublisher to be able to map directly from these types into corresponding Java objects. I had a quick look around but I couldn't see any direct equivalents for C#. Just thought I'd mention it in case Java devs come across this question.

Puddle

Quick note - CSV won't work by itself, as Oracle will treat it as one string. – Mirthless

Shouldn't that be ... from string_table(identifierList)); ...? – Beam

Answer 2

I think there is no direct way to create procedures with variable number of parameters. However there are some, at least partial solutions to the problem, described here.

1. If there are some typical call types procedure overloading may help.
2. If there is an upper limit in the number of parameters (and their type is also known in advance), default values of parameters may help.
3. The best option is maybe the usage of cursor variables what are pointers to database cursors.

Unfortunately I have no experience with .NET environments.

Nolin

"there is no direct way to create procedures with variable number of parameters"... Yes there is, use CREATE TYPE xxx AS TABLE OF yyy and then use that type as the parameter type. – Ofori

No, it is not a direct solution. Anyway your method is the accepted answer dated back to 2008. – Nolin

@Nolin the link in your answer is dead – Legislate

Answer 3

I found the following article by Mark A. Williams which I thought would be a useful addition to this thread. The article gives a good example of passing arrays from C# to PL/SQL procs using associative arrays (TYPE myType IS TABLE OF mytable.row%TYPE INDEX BY PLS_INTEGER):

Great article by Mark A. Williams

Granese

Answer 4

Why not just use a long parameter list and load the values into a table constructor? Here is the SQL/PSM for this trick

UPDATE Foobar
   SET x = 42
 WHERE Foobar.keycol
      IN (SELECT X.parm
            FROM (VALUES (in_p01), (in_p02), .., (in_p99)) X(parm)
           WHERE X.parm IS NOT NULL);

Rivero

Answer 5

just split variable as a comma separed string:

declare
schema_n VARCHAR2 (30);
schema_size          number;
schemas       VARCHAR2 (30);
begin
schema_n := 'USER_PROD01,USER_PROD01' ;

select sum(bytes)/1024/1024 INTO schema_size FROM dba_segments where owner in (
with rws as (
  select ''||schema_n||'' str from dual
)
  select regexp_substr (
           str,
           '[^,]+',
           1,
           level
         ) value
  from   rws
  connect by level <= 
    length ( str ) - length ( replace ( str, ',' ) ) + 1

) ;
DBMS_OUTPUT.PUT_LINE ('PAY ATTENTION : PROD '||schemas||' user size is in total of : '||schema_size||' MBs .');
end;
/

PAY ATTENTION : PROD user size is in total of : 18.8125 MBs .

PL/SQL procedure successfully completed.

Accentuation

Answer 6

I've not done it for Oracle, but with SQL Server you can use a function to convert a CSV string into a table, which can then be used in an IN clause. It should be straight-forward to re-write this for Oracle (I think!)

CREATE Function dbo.CsvToInt ( @Array varchar(1000)) 
returns @IntTable table 
    (IntValue nvarchar(100))
AS
begin

    declare @separator char(1)
    set @separator = ','

    declare @separator_position int 
    declare @array_value varchar(1000) 

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0 
    begin

      select @separator_position =  patindex('%,%' , @array)
      select @array_value = left(@array, @separator_position - 1)

        Insert @IntTable
        Values (Cast(@array_value as nvarchar))

      select @array = stuff(@array, 1, @separator_position, '')
    end

    return
end

Then you can pass in a CSV string (e.g. '0001,0002,0003') and do something like

UPDATE table1 SET 
       col1 = col1 - value 
WHERE id in (SELECT * FROM csvToInt(@myParam)) 

Vitus

Something similar can be done with PL/SQL but it is faster to pass a collection to the procedure as a csv string. The csv string needs to be tokenized first in PL/SQL. – Calie

Answer 7

There is an article on the AskTom web site that shows how to create a function to parse the CSV string and use this in your statement in a similar way to the SQL Server example given.

See Asktom

Ashantiashbaugh