Using an Oracle Table Type in IN-clause - compile fails
Simply trying to get a cursor back for the ids that I specify.
CREATE OR REPLACE PACKAGE some_package AS
TYPE t_cursor IS REF CURSOR;
TYPE t_id_table IS TABLE OF NVARCHAR(38) INDEX BY PLS_INTEGER;
PROCEDURE someentity_select(
p_ids I开发者_StackOverflow社区N t_id_table,
p_results OUT t_cursor);
END;
CREATE OR REPLACE PACKAGE BODY some_package AS
PROCEDURE someentity_select(
p_ids IN t_guid_table,
p_results OUT t_cursor)
IS
BEGIN
OPEN p_results FOR
SELECT *
FROM someschema.someentity
WHERE id IN (SELECT column_value FROM TABLE(p_ids)); - fails here
END;
END;
Note: someschema.someentity.id is a NVARCHAR2(38)
PL/SQL: ORA-00382: expression is of wrong type
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Where am I going wrong?
In Oracle versions prior to 12.2 you can only SELECT from a collection type that is defined in the database via a CREATE TYPE statement, not an associative array:
CREATE TYPE t_id_table IS TABLE OF NVARCHAR(38);
CREATE OR REPLACE PACKAGE some_package AS
PROCEDURE someentity_select(
p_ids IN t_guid_table,
p_results OUT SYS_REFCURSOR);
END;
CREATE OR REPLACE PACKAGE BODY some_package AS
PROCEDURE someentity_select(
p_ids IN t_guid_table,
p_results OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_results FOR
SELECT *
FROM someschema.someentity
WHERE id IN (SELECT column_value FROM TABLE(p_ids));
END;
END;
This is an index-by table, which is a PL/SQL type.
You can only use SQL types in the SQL engine of Oracle. Or PL/SQL types, that Oracle can hack around to look like SQL types.
You can have a simple array-like collection and use it as a result. (no index by)
type TGuidList is table of NVarchar(38);
But the best compatibility and stability, you get by declaring it as a global SQL type and use that inside your package:
create type TGuidList is table of NVarchar(38);
Edit: You will not need an NVarChar for a GUID, will you? A good ol' VarChar should do the trick just fine.
精彩评论