Menu
Is it possible to use VARRAY in IN CLAUSE of pl/sql?
778
A varray is always associated with an object table. The simplest way to query a varray is by selecting the varray name within a simple SELECT statement. This, in turn, will display all the elements within the varray.
VARRAY stands for the variable-sized array. A VARRAY is single-dimensional collections of elements with the same data type. Unlike an associative array and nested table, a VARRAY always has a fixed number of elements(bounded) and never has gaps between the elements (not sparse).
The PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. A varray is used to store an ordered collection of data, however it is often better to think of an array as a collection of variables of the same type.
Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables.
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;
/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
