To pass a list/array of ids from C# into Oracle PL/SQL procedure, you have to define an associative array type e.g. a table with integer indexing:
TYPE Ids_t_a is table of Number index by binary_integer;
I want to write a stored proc which wraps a query like the following:
SELECT Id, Name from Person where Id in Ids;
I know basic SQL but am not a PL/SQL expert at all and table-valued types are beyond me. I tried:
PROCEDURE GetData(Ids in Ids_t_a, results out sys_refcursor) IS
BEGIN
Open Results for
SELECT Id, Name from Person p where p.Id in Ids;
END;
But this gives an error "expression is of wrong type" and that seems to be because you cannot use an associative array this way.
What additional steps do I need to run my desired query against my input arguments?
CodePudding user response：
Oracle offers a built-in type (sys.odcinumberlist) that is capable of containing numbers (so that you wouldn't have to create your own); that's what my example does. I'm using Scott's sample schema; the emp table contains some employees who work in certain departments - list of departments will be passed to the procedure.
SQL> create or replace procedure getdata
2 (ids in sys.odcinumberlist, results out sys_refcursor)
3 is
4 begin
5 open results for
6 select empno, ename from emp
7 where deptno in (select * from table(ids)); --> this is what you're looking for
8 end;
9 /
Procedure created.
Let's try it:
SQL> set serveroutput on
SQL> declare
2 l_ids sys.odcinumberlist; -- it'll contain list of departments
3 l_rc sys_refcursor; -- refcursor will be returned into this variable
4 l_empno number; -- employee number
5 l_ename varchar2(20); -- their name
6 begin
7 getdata(sys.odcinumberlist(10, 20), l_rc); -- this is how you call the procedure
8
9 loop -- you'll be using the result differently; I'm just
10 fetch l_rc into l_empno, l_ename; -- displaying contents
11 exit when l_rc%notfound;
12 dbms_output.put_line(l_empno ||' - '|| l_ename);
13 end loop;
14 end;
15 /
7782 - CLARK
7839 - KING
7934 - MILLER
7369 - SMITH
7566 - JONES
7788 - SCOTT
7876 - ADAMS
7902 - FORD
PL/SQL procedure successfully completed.
SQL>
You can use your own type, of course; it's just that I didn't feel like creating a package and do everything there because type (the one you posted) can't be created at SQL level:
SQL> create or replace TYPE Ids_t_a is table of Number index by binary_integer;
2 /
Warning: Type created with compilation errors.
SQL> show err
Errors for TYPE IDS_T_A:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/17 PLS-00355: use of pl/sql table not allowed in this context
SQL>
CodePudding user response：
In Oracle, there are two scopes where statements can be evaluated:
- The SQL scope where the Oracle engine will parse SQL statements; and
- The PL/SQL scope where the Oracle engine will parse procedural language statements (PL/SQL).
An associative array is a PL/SQL data type and can only be used in the PL/SQL scope. It CANNOT be used in SQL statements so it is impossible to use an associative array directly as you are attempting (and, for some unknown reason, C# does not support passing non-associative arrays).
What additional steps do I need to run my desired query against my input arguments?
You need to convert the associative array collection type that you pass in from C# to a non-associative array collection type (either a nested-table collection type or a VARRAY collection type that you have defined in the SQL scope) in the PL/SQL scope and then use the non-associative array in the SQL scope.
You could use a built-in collection type like SYS.ODCINUMBERTYPE or you can define your own collection in the SQL scope:
CREATE TYPE number_list IS TABLE OF NUMBER;
Then you can create your package and procedure and a helper function to perform the conversion:
CREATE PACKAGE your_package IS
TYPE Ids_t_a is table of Number index by binary_integer;
FUNCTION map_ids(
Ids in Ids_t_a
) RETURN number_list PIPELINED;
PROCEDURE GetData(
Ids in Ids_t_a,
results out sys_refcursor
);
END;
/
Then the package body:
CREATE PACKAGE BODY your_package IS
FUNCTION map_ids(
Ids in Ids_t_a
) RETURN number_list PIPELINED
IS
v_idx BINARY_INTEGER;
BEGIN
IF ids IS NULL THEN
RETURN;
END IF;
v_idx := ids.FIRST;
WHILE v_idx IS NOT NULL LOOP
PIPE ROW(ids(v_idx));
v_idx := ids.NEXT(v_idx);
END LOOP;
END;
PROCEDURE GetData(
Ids in Ids_t_a,
results out sys_refcursor
)
IS
BEGIN
Open Results for
SELECT Id, Name
FROM Person
WHERE Id MEMBER OF map_ids(ids);
END;
END;
/
Note: if you use a VARRAY collection data type, such as the built-in SYS.ODCINUMBERLIST type, then you cannot use the MEMBER OF operator as that only supports nested-table collection types. Instead you would have to use a sub-query (or a join) and a table collection expression. For example: Open Results for SELECT p.Id, p.Name FROM Person p INNER JOIN TABLE(map_ids(ids)) i ON p.id = i.COLUMN_VALUE;
If you have the sample data:
CREATE TABLE person (id, name) AS
SELECT 1, 'Alice' FROM DUAL UNION ALL
SELECT 2, 'Beryl' FROM DUAL UNION ALL
SELECT 3, 'Carol' FROM DUAL UNION ALL
SELECT 4, 'Debra' FROM DUAL;
Then you can call your procedure (either from C# or from a PL/SQL block) and pass an associative array and retrieve the cursor:
DECLARE
v_cur SYS_REFCURSOR;
v_ids YOUR_PACKAGE.IDS_T_A;
v_id PERSON.ID%TYPE;
v_name PERSON.NAME%TYPE;
BEGIN
v_ids(1) := 3; -- Note: These are deliberately not sequential index values.
v_ids(3) := 1; -- Indexes generated by C# probably would be, but it
v_ids(42) := 7; -- is not guaranteed to always be true.
YOUR_PACKAGE.GetData(v_ids, v_cur);
LOOP
FETCH v_cur INTO v_id, v_name;
EXIT WHEN v_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_name);
END LOOP;
END;
/
Which outputs:
1: Alice 3: Carol