SQL & PL/SQL

Hi All,

I have written the following code and it's working fine but talking around approx. 40 minutes for 112000 records.

declare
  lv_website varchar2(100);
  
  cursor c1 is
      select *
        from tb_main_table
        where status = 'C' and active = 'Y'
              and ext_id in (select webid from temp_website_table);
Begin

	For Rec in c1 Loop
      --the following query will always return one record
	  begin
		 select weburl
		   into lv_website
		   from temp_website_table 
		  where webid = Rec.ext_id;
	  exception
		 when others then
			  lv_website := null;
	  end;
	  
	  Rec.seq_id    := tb_main_seq.nextval;
	  Rec.link_text := lv_website;
	  
	  begin
	    Insert into tb_main_table values Rec;
		dbms_output.put.line('Data successfully inserted--:'||Rec.ext_id||'-'||Rec.seq_id);
	  exception
        when others then
             dbms_output.put.line('Error while insert--:'||Rec.ext_id||'-'||Rec.seq_id||'-:'||sqlerrm);
      end;	
	  
	End loop;

exception
  when others then
       dbms_output.put.line('Error--:'||sqlerrm);
end;

I want to convert this normal pl sql to collection bulk collect into and using for all.

I have also tried the below code but getting the below errors

PLS-00642: local collection types not allowed in SQL statements

PL/SQL: ORA-22905: cannot access rows from a non-nested table item

DECLARE
    cursor c1 is
        select *
         from tb_main_table
        where status = 'C' and active = 'Y'
              and ext_id in (select webid from temp_website_table);

    lv_website varchar2(100);
    
    TYPE type_main_collection IS TABLE OF tb_main_table%ROWTYPE;
    lv_cr_data type_main_collection;
    
    TYPE type_website_collection IS TABLE OF VARCHAR2(100);
    lv_websites type_website_collection;

BEGIN
    OPEN c1;
    LOOP
        -- Fetch data in bulk into the collection
        FETCH c1 BULK COLLECT INTO lv_cr_data;
        EXIT WHEN lv_cr_data.COUNT = 0;

        -- Collect websites for the fetched data
        --This select always fetch only one record for one web id 
        --(Getting error from the below select)
        SELECT weburl
        BULK COLLECT INTO lv_websites
        FROM temp_website_table 
        WHERE webid IN (SELECT ext_id FROM TABLE(lv_cr_data));

        -- Assign website and sequence values to lv_cr_data collection
        FOR i IN 1 .. lv_cr_data.COUNT LOOP
            lv_cr_data(i).seq_id    := tb_main_seq.nextval;
            lv_cr_data(i).link_text := lv_websites(i);
        END LOOP;

        -- Bulk insert into the target table using FORALL
        FORALL i IN 1 .. lv_cr_data.COUNT
            INSERT INTO tb_main_table VALUES lv_cr_data(i);

        -- Optionally commit after each batch of records
        COMMIT;

    END LOOP;
    CLOSE c1;
END;
/

Please suggest the best or any other way to do this.