首先可以创建辅助表

CREATE TABLE master_channel_
(                                                                                                                                                                
 identify           VARCHAR2(15),
 channel_names      VARCHAR2(25), 
 channel_partners   VARCHAR2(500),
 load_date          VARCHAR2(25)  
);

并通过以下方式将数据加载到其中

$ . mchannel.sh

它的内容是

sqlldr userid="un/pwd"@thedb control=mchannel.ctl log=mchannel.log bad=mchannel.bad errors=99999999 direct=y

.ctl文件是

OPTIONS(skip=1)
LOAD DATA                                                                                                                                                                           
 INFILE 'mchannel.txt' "str '\n'"                                                                                                           
 TRUNCATE INTO TABLE master_channel_                                                                                                                      
 FIELDS TERMINATED BY '|'                                                                                                                                                         
 (                                                                                                                                                                
  identify           CHAR(15)  "TRIM(:identify)",
  channel_names      CHAR(25)  "TRIM(:channel_names)", 
  channel_partners   CHAR(500) "TRIM(:channel_partners)",
  load_date          CHAR(25)  "TRIM(:load_date)"
 )

然后使用以下查询

INSERT INTO master_channel(identify,channel_names,channel_partners,load_date)
 SELECT m.identify,
        m.channel_names,
        TRIM( BOTH '"' FROM REGEXP_SUBSTR(m.channel_partners,'[^,]+',1,level)),
        TO_DATE(m.load_date,'dd-MON-yyyy')
   FROM master_channel_ m
CONNECT BY level <= REGEXP_COUNT(m.channel_partners,',')+1
    AND prior sys_guid() IS NOT NULL
    AND prior m.identify = m.identify;

在应用所需的转换时插入到主表中，其中假定该表创建为

CREATE TABLE master_channel
(                          
 row_id             NUMBER generated always as identity,                                                                                                                                      
 identify           VARCHAR2(15),
 channel_names      VARCHAR2(25), 
 channel_partners   VARCHAR2(500),
 load_date          DATE  
)

正如Paul W所建议的，一个简单的AWK完成了准备文件的工作，您可以借此机会删除双引号（和/或更改分隔符，如您所愿）：

awk -F '|' '{split($3,a,/,/)}; { for (s in a) { print $1 "|" $2 "|" substr( a[s], 2, length(a[s])-2 ) "|" $4 }  }' 

1-ED|WEBSITE|abhibus|02-FEB-2022
1-ED|WEBSITE|amazon travel|02-FEB-2022
1-ED|WEBSITE|irctc|02-FEB-2022
1-ED|WEBSITE|redbus|02-FEB-2022
1-LP|WALKIN|printed reservation|04-FEB-2022
1-LP|WALKIN|current reservation|04-FEB-2022
1-LP|WALKIN|physical reservation|04-FEB-2022