0

I have to select the rows of data of the table demo where the designation column value matches with any one of the elements of the array. I'm facing problem in 'where' part of the code. How do I compare the values of the array and the designation column?

CREATE OR REPLACE TYPE array_collection IS VARRAY(100) OF VARCHAR2(50);
--
declare
--Initialization 
var_array array_collection:=array_collection();
f VARCHAR2(100);
begin
  ---Storing 3 elements
 var_array.extend(3);
 var_array(1):= 'Software Engineer';
 var_array(2):= 'Account Manager';
 var_array(3):= 'Operations Team Leader';


select full_name into f from demo where designation member of var_array;


end;

I want all the rows which has designation 'Software Engineer','Account Manager','Operations Team Leader' from the table demo to be selected.

Jaya
  • 35
  • 2
  • 9

1 Answers1

0
CREATE OR REPLACE TYPE array_collection IS VARRAY(100) OF VARCHAR2(50);
--
declare
--Initialization 
var_array array_collection:=array_collection();
f VARCHAR2(100);
begin
  ---Storing 3 elements
 var_array.extend(3);
 var_array(1):= 'Software Engineer';
 var_array(2):= 'Account Manager';
 var_array(3):= 'Operations Team Leader';


select full_name into f from demo where designation in (select * from table(var_array));


end;
Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Error report - ORA-01422: exact fetch returns more than requested number of rows Thanks but got this error before, seen it before too. Can't tell if its because of my into clause. ORA-06512: at line 16 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned. *Action: Rewrite the query or change number of rows requested – Jaya Jul 10 '19 at 07:56
  • Yes, Because this error is thrown when the query returns more than one rows and you have used `into clause`. – Popeye Jul 10 '19 at 08:10