CODEWITHSUNDEEP
oracle-databaseplsql
user6148078
user6148078

Reputation: 721

Oracle how to use array in where clause?

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?

Upvotes: 3

Views: 10550

Answers (2)

user24433081
user24433081

Reputation:

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 );

Upvotes: 0

XING
XING

Reputation: 9886

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 ;

Upvotes: 3

Related Questions