SQL & PL/SQL

DECLARE
 fp utl_file.file_type;
 v_info varchar2(200);
 v_num1 VARCHAR2(49);
 v_num2 VARCHAR2(49);
-- v_num3 varchar2(49);

TYPE v_table IS TABLE OF VARCHAR2(40);
v_num3 v_table;
begin
 BEGIN
    fp :=utl_file.fopen('DIR2','abc.txt','r');
    loop
      utl_file.get_line(fp,v_info);
      dbms_output.put_line(v_info);
      INSERT INTO e  SELECT rtrim(regexp_substr(v_info||':','\d+:',1,1),':') num1 ,
                            rtrim(regexp_substr(v_info||':','\d+:',1,2),':') num2
                            FROM dual;
                            
    END loop;
   -- commit;
    end;
     execute immediate 'SELECT num1  FROM e WHERE ROWNUM=1 ' into v_num1;
     dbms_output.put_line(v_num1);
   -- commit;
   --   EXECUTE IMMEDIATE 'select distinct num1 from e' bulk collect INTO v_num3;
    --  FOR i IN 1..v_num3.count
    --  loop
      --  dbms_output.put_line(v_num3(i));
      --  END loop;
exception 
 WHEN no_data_found THEN
  NULL;
    end ;