As the foregoing comment (or elsewhere on AskTom) states, we can get the entire directory and access files via an external table.
Suppose the external table has CSV contents like so (I’ve mocked it up using ordinary SQL using the SALGRADE table):
select rownum-1 as r#, 'GRADE'||','||'LOSAL'||','||'HISAL' csv
from dual
union all
select rownum, Grade||','||losal||','||hisal
from salgrade
SQL> /
R# CSV
--- ---------------------------------------------------------------
0 GRADE,LOSAL,HISAL
1 1,700,1200
2 2,1201,1400
3 3,1401,2000
4 4,2001,3000
5 5,3001,9999
6 rows selected.
Let’s assume the file name is X1. If you can assume string datatypes, and a standard length (say VARCHAR2(20) in my example below), then a basic PL/SQL block can create the table from the external table:
DECLARE ctas varchar2(2000);
inserter CLOB := '';
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table x1';
EXCEPTION WHEN OTHERS THEN NULL; END;
FOR rec IN (
select r#
, case when r# = 0
then 'create table x1 ('
||replace(csv,',',' VARCHAR2(20),')||' VARCHAR2(20))'
when r# = 1
then 'insert into x1 select '||csv||' from dual '
else 'select '||csv||' from dual '
end
||case when NOT ( r# = 0 or rr# = 1 ) then ' UNION ALL' end as DDL
from (
select row_number()over(order by r# DESC) rr#
, t.*
from (
select rownum-1 as r#, 'GRADE'||','||'LOSAL'||','||'HISAL' csv
from dual
union all
select rownum, Grade||','||losal||','||hisal
from salgrade
) t order by r#
)
)LOOP
IF rec.r# = 0
THEN execute immediate rec.ddl;
ELSE inserter := inserter||' '||rec.ddl;
END IF;
END LOOP;
execute immediate inserter;
END;
/
PL/SQL procedure successfully completed.
SQL> select * from x1;
GRADE LOSAL HISAL
-------------------- -------------------- -------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999