Is it possible to use VARRAY in IN CLAUSE of pl/sql?
可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
回答1:
Yes, you can, provided that the VARRAY type is a global type (and not local to some PL/SQL code):
CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200);
DECLARE
l_str_tab str_tab_type;
l_count NUMBER;
BEGIN
l_str_tab := str_tab_type();
l_str_tab.extend(2);
l_str_tab(1) := 'TABLE';
l_str_tab(2) := 'INDEX';
SELECT COUNT(*) INTO l_count
FROM all_objects
WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(l_str_tab));
END;
/
回答2:
I have the same Problem, but I don't want (and am not allowed) to define a global TYPE
When defining it local,
TYPE fkarraytype IS VARRAY(10) OF VARCHAR2(10);
fkarray fkarraytype;
The compiler Fails with:
Error: PLS-00642: Lokale Erfassungstypen in SQL-Anweisungen nicht zulässig Text: AND fk.strval IN (SELECT COLUMN_VALUE FROM TABLE(fkarray))