I want store the value in array, and then use it in where clause.
Like this:
SELECT ... ...
FROM myTable
WHERE (myTable.id, myTable.type)
IN (SELECT *
FROM table(array_collection)
)
array_collection value like this:
(('1','N'),
('2','N'),
('3','Y'))
And there have any way not create schema level table type to do that?
I want store the value in array.
This is how you store value:
CREATE OR REPLACE TYPE array_item IS object( id NUMBER, TYPE varchar2(3));
CREATE OR REPLACE TYPE array_collection IS VARRAY(10) OF array_item;
declare
--Initialization
var_array array_collection:=array_collection();
begin
---Storing 3 elements
var_array.extend(3);
var_array(1):= array_item(1,'N');
var_array(2):= array_item(2,'N');
var_array(3):= array_item(3,'Y');
for i in 1..var_array.count
loop
dbms_output.put_line( var_array(i).id ||' '|| var_array(i).TYPE );
end loop;
end;
You can use as :
SELECT ... ...
FROM myTable
WHERE (myTable.id, myTable.type) MEMBER OF var_array ;
If you're restricted to using only SQL and cannot use PL/SQL or create custom objects, you can still achieve this by using a combination of string manipulation and the IN operator. However, this method is less dynamic and may not be suitable for handling large arrays of values due to limitations on query length.
Here's how you can do it in SQL:
SELECT * FROM your_table WHERE your_column IN ( SELECT regexp_substr(:your_api_values, '[^,]+', 1, LEVEL) FROM dual CONNECT BY regexp_substr(:your_api_values, '[^,]+', 1, LEVEL) IS NOT NULL );