How about a design pattern like this?
CREATE OR REPLACE DIRECTORY c_external_csv_tab_dir AS 'c:\some_folder_on_database_server\';
CREATE TABLE c_external_migration_tab
(
a1 VARCHAR2(256),
a2 VARCHAR2(256),
a3 VARCHAR2(256),
a4 VARCHAR2(256),
a5 VARCHAR2(256),
a6 VARCHAR2(256),
a7 VARCHAR2(256),
a8 VARCHAR2(256),
a9 VARCHAR2(256),
a10 VARCHAR2(256)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY c_external_csv_tab_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
CHARACTERSET AL32UTF8
STRING SIZES ARE IN CHARACTERS
BADFILE 'c_external_migration_tab.bad'
DISCARDFILE 'c_external_migration_tab.dis'
LOGFILE 'c_external_migration_tab.log'
SKIP 1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
LTRIM
MISSING FIELD VALUES ARE NULL
(
a1 CHAR(256),
a2 CHAR(256),
a3 CHAR(256),
a4 CHAR(256),
a5 CHAR(256),
a6 CHAR(256),
a7 CHAR(256),
a8 CHAR(256),
a9 CHAR(256),
a10 CHAR(256)
)
)
location (c_external_csv_tab_dir:'c_external_migration_tab.csv')
)
REJECT LIMIT UNLIMITED;
GRANT SELECT ON c_external_migration_tab TO ifsinfo WITH GRANT OPTION;
GRANT SELECT ON c_external_migration_tab TO ifssys;
GRANT SELECT ON c_external_migration_tab TO fnd_admin;
DECLARE
info_ VARCHAR2(32767);
objid_ VARCHAR2(32767);
objversion_ VARCHAR2(32767);
attr_ VARCHAR2(32767);
BEGIN
FOR header_rec_ IN (
SELECT e.a1 AS wo,
e.a2 AS description,
e.a3 AS maint_org
FROM c_external_csv_tab_dir
GROUP BY e.a1, e.a2, e.a3
ORDER BY 1, 2, 3
) LOOP
client_sys.clear_attr(attr_);
client_sys.add_to_attr('WO', header_rec_.wo, attr_);
client_sys.add_to_attr('DESCRIPTION', header_rec_.description, attr_);
client_sys.add_to_attr('MAINT_ORG', header_rec_.maint_org, attr_);
header_lu_api.new__(info_, objid_, objversion_, attr_, 'DO');
FOR line_rec_ IN (
SELECT e.a4 AS line,
e.a5 AS description,
e.a6 AS duration,
e.a7 AS plannedmen
FROM c_external_csv_tab_dir
WHERE e.a1 = header_rec_.wo
AND e.a2 = header_rec_.description
AND e.a3 = header_rec_.maint_org
ORDER BY 1, 2, 3, 4
) LOOP
client_sys.clear_attr(attr_);
client_sys.add_to_attr('LINE', line_rec_.line, attr_);
client_sys.add_to_attr('DESCRIPTION', line_rec_.description, attr_);
client_sys.add_to_attr('DURATION', line_rec_.duration, attr_);
client_sys.add_to_attr('PLANNEDMEN', line_rec_.plannedmen, attr_);
line_lu_api.new__(info_, objid_, objversion_, attr_, 'DO');
END LOOP;
END LOOP;
END;
/
Thanks Kevin - I hadn't considered approaching the issue like this but it makes sense to do so. Your code has given me some good ideas on where to take the solution outside of the migration job.
Thanks Kevin - I hadn't considered approaching the issue like this but it makes sense to do so. Your code has given me some good ideas on where to take the solution outside of the migration job.
Using generic column names like that allows you to reuse that external table for other purposes, and I never write migration jobs. (The version of this I personally use has 120 columns. If you need more than that, you’re doing something wrong.)