-2

I'm trying to send an array of strings to an oracle stored procedure. The procedure is to compare the value in a column to see if it is present in a list.

I'm using oracle v23 and .NetCore 6

My stored proc declaration:

CREATE OR REPLACE PACKAGE DEV.EMPLOYEES_HELPER
AS

    TYPE string_array_type IS TABLE OF VARCHAR2(100);
    
    FUNCTION get_employees(p_string_list string_array_type) RETURN SYS_REFCURSOR;
    
    FUNCTION check_value_in_list (
            p_value_to_check VARCHAR2,
            p_string_list string_array_type
        ) RETURN BOOLEAN;

END EMPLOYEES_HELPER;

My stored proc body:

CREATE OR REPLACE PACKAGE BODY DEV.EMPLOYEES_HELPER
AS

FUNCTION get_employees(p_string_list string_array_type) RETURN SYS_REFCURSOR IS
    c_results SYS_REFCURSOR;
    BEGIN
         OPEN c_results FOR
            SELECT *
            FROM EMPLOYEES e 
            WHERE EMPLOYEES_HELPER.check_value_in_list(e.DEPARTMENT , p_string_list) = TRUE;
            
         RETURN c_results;
    END;

    -- Implement the function
    FUNCTION check_value_in_list (
        p_value_to_check VARCHAR2,
        p_string_list string_array_type
    ) RETURN BOOLEAN
    IS
    BEGIN
        FOR i IN 1..p_string_list.COUNT LOOP
            IF p_value_to_check = p_string_list(i) THEN
                RETURN TRUE;
                EXIT; -- Exit the loop once the value is found
            END IF;
        END LOOP;

        RETURN FALSE;
    END check_value_in_list;
   
END EMPLOYEES_HELPER;

My C# code snippet that calls the proc:

using (OracleConnection connection = new OracleConnection(connectionString))
{
    connection.Open();

    // Create command for calling the stored procedure
    using (OracleCommand command = connection.CreateCommand())
    {
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.CommandText = "DEV.EMPLOYEES_HELPER.get_employees";

        // Create parameter for the string array
        OracleParameter stringArrayParam = new OracleParameter();
        stringArrayParam.ParameterName = "p_string_list";
        stringArrayParam.Direction = System.Data.ParameterDirection.Input;
        stringArrayParam.OracleDbType = OracleDbType.Varchar2; //  OracleDbType.Array ???
        stringArrayParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        stringArrayParam.Value = new string[] { "HR", "IT" };

        // Add the parameter to the commands
        command.Parameters.Add(stringArrayParam);

        // Create output parameter for the cursor
        OracleParameter cursorParam = new OracleParameter();
        cursorParam.ParameterName = "result";
        cursorParam.Direction = System.Data.ParameterDirection.ReturnValue;
        cursorParam.Size = int.MaxValue;
        cursorParam.OracleDbType = OracleDbType.RefCursor;

        // Add the cursor parameter to the command
        command.Parameters.Add(cursorParam);

        // Execute the command
        using (OracleDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // Process each row from the cursor
                Console.WriteLine($"Id: {reader["ID"]}, Name: {reader["NAME"]}, Department: {reader["DEPARTMENT"]}");
            }
        }
    }
}

The above results in this error and I can't figure out where I'm going wrong:

Oracle.ManagedDataAccess.Client.OracleException: 'ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'GET_EMPLOYEES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored'
Anthony Packer
  • 1
  • This is a duplicate of https://stackoverflow.com/a/56645211/1509264 . C# does not support passing SQL or PL/SQL collection data types (`TYPE x IS TABLE OF y`) it only supports passing PL/SQL associative array data types (`TYPE x IS TABLE OF y INDEX BY z`) so you need to pass an associative array and then convert it to an SQL collection if you want to use it in SQL. – MT0 Aug 25 '23 at 10:53
  • @MT0 Event if I change to `TYPE string_array_type IS TABLE OF VARCHAR2(100) index by BINARY_INTEGER;` I still get the same error – Anthony Packer Aug 25 '23 at 11:01
  • You cannot use the `check_value_in_list` function in an SQL statement (firstly, `BOOLEAN` is a PL/SQL only data type and, secondly, if you change `string_array_type` to an associative array then that is also a PL/SQL only data type); you would need to return `1`/`0` or `'Y'/`'N'` from the function and use a helper function to convert `string_array_type` from an associative array to a collection (see the [first of the linked duplicates](https://stackoverflow.com/a/74608092/1509264) for an example helper function). The second linked duplicate gives C# code (and does not set `.OracleDbType`). – MT0 Aug 25 '23 at 11:18
  • [fiddle](https://dbfiddle.uk/MzNIAMp2) is a fixed version of your Oracle code. – MT0 Aug 25 '23 at 11:39

0 Answers0