只需尝试为我指定的ID获取光标.
- 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 IN 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;
注意：someschema.someentity.id是一个NVARCHAR2(38)
PL / sql：ORA-00382：表达式类型错误
PL / sql：ORA-22905：无法访问非嵌套表项的行
我哪里错了？
在12.2之前的Oracle版本中,您只能通过CREATE TYPE语句从数据库中定义的集合类型中进行SELECT,而不是关联数组：
- 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;
