Oracle stored procedure with parameters for IN clause

by

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.

More Related Contents:

  1. Place to store PL/SQL block [closed]
  2. Passing an array of data as an input parameter to an Oracle procedure
  3. Get resultset from oracle stored procedure
  4. Functions vs procedures in Oracle
  5. What is the replacement of cursor foreach in oracle pl/sql?
  6. Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  7. Printing the value of a variable in SQL Developer
  8. PL/SQL, how to escape single quote in a string?
  9. Oracle PL/SQL – Raise User-Defined Exception With Custom SQLERRM
  10. What is the difference between “AS” and “IS” in an Oracle stored procedure?
  11. Convert comma separated string to array in PL/SQL
  12. UTL_FILE.FOPEN() procedure not accepting path for directory?
  13. Oracle PL/SQL – How to create a simple array variable?
  14. ways to avoid global temp tables in oracle
  15. Split function in oracle to comma separated values with automatic sequence
  16. ORA-06508: PL/SQL: could not find program unit being called
  17. Number of rows affected by an UPDATE in PL/SQL
  18. How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?
  19. Create Pivot view in SQL from a SQL table
  20. Solution to “cannot perform a DML operation inside a query”?
  21. Why doesn’t PL/SQL respect privileges granted by Roles?
  22. Can We use threading in PL/SQL?
  23. Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?
  24. What is the algorithm used by the ORA_HASH function?
  25. oracle “table or view does not exist” from inside stored procedure
  26. passing in table name as plsql parameter
  27. Reading clob line by line with pl\sql
  28. How to use parameters in a ‘where value in…’ clause?
  29. BEGIN – END block atomic transactions in PL/SQL
  30. Escaping single quote in PLSQL

Leave a Comment