Oracle collection insert statement-CodePudding

Getting error in Oracle, to use bulk collect not working at the time of insert. Any suggestion how to resolve below issue. Happy to use any other option

Getting error - ORA-00902: invalid datatype

/*
create table locations
(
id number(5),
Name varchar(10)
);

Insert into locations values(1,'India');
Insert into locations values(2,'UK');
Insert into locations values(3,'USA');

create table locations_cpy
(
id number(5)
);
commit;

*/

drop package  DataTypeIssue ;
/ 

CREATE OR REPLACE PACKAGE DataTypeIssue AS

  TYPE T_CURSOR IS REF CURSOR;
  TYPE array_of_numbers IS table OF NUMBER(5);
 PROCEDURE DataTypeIssue_example(V_CURSOR OUT T_CURSOR);
 
END DataTypeIssue;
/

CREATE OR REPLACE PACKAGE BODY DataTypeIssue AS

  
 PROCEDURE DataTypeIssue_example(V_CURSOR OUT T_CURSOR) IS
v_LocationIds array_of_numbers;
v_cnt number;
   v_error_code NUMBER;
 BEGIN

select id BULK COLLECT into v_LocationIds from locations bc;


-- works here in select 
open V_CURSOR for 
  select distinct id from locations
  where id  IN (SELECT column_value FROM TABLE(v_LocationIds));

-- insert gettting error
/*
ORA-00902: invalid datatype
*/

  insert into locations_cpy 
  select distinct 1 from locations
  where id  IN (SELECT column_value FROM TABLE(v_LocationIds)); 
     rollback;
 END DataTypeIssue_example; 
   

END DataTypeIssue;
/

any help will be appreciated

Getting error in Oracle, to use bulk collect not working at the time of insert. Any suggestion how to resolve below issue. Happy to use any other option available,

CodePudding user response：

 insert into locations_cpy 
 select distinct id, Name from locations
 where id  IN (SELECT column_value FROM TABLE(v_LocationIds)); 
 rollback;
 END DataTypeIssue_example;

CodePudding user response：

insert into locations_cpy 
select distinct id
where id  IN (SELECT column_value FROM TABLE(v_LocationIds)); 
rollback;
END DataTypeIssue_example;