Prozedur wurde erstellt.

BEGIN exttabletest; END;

*
FEHLER in Zeile 1:
ORA-00905: Schlüsselwort fehlt
ORA-06512: in "SYSTEM.EXTTABLETEST", Zeile 40
ORA-06512: in Zeile 1

create or replace
PROCEDURE exttabletest 
IS

BEGIN

DECLARE

 --First I obtain information from a table which tells me about the files I want to import

 datumheute   VARCHAR2(10):= sysdate;  
 tag          VARCHAR2(2):= substr(sysdate, 1, 2);
 monat        VARCHAR2(2):=substr(datumheute, 4, 2);
 jahr         VARCHAR2(2):=substr(datumheute, 7, 2);
 dateischwanz VARCHAR2(50):=
rpad(rpad(rpad(rpad(rpad(rpad('_20', 5, jahr), 6, '_'), 8, monat), 9, '_'), 11, tag), 15, '.csv');
 dateiname    VARCHAR2(50);
 dir          CSV_DATEIEN.CSV_DIR%TYPE; 
 prefix       CSV_DATEIEN.CSV_PREFIX%TYPE; 
 delimiter    CSV_DATEIEN.CSV_DELIMITER%TYPE; 
 opt          CSV_DATEIEN.CSV_OPT%TYPE;  
 zeile1name   CSV_DATEIEN.CSV_ZEILE1NAME%TYPE; 
 testfile     VARCHAR2(30) := 'Datenlogger_2008_03_27.csv'; -- for testing purposes
 
 CURSOR TABLE_CURSOR IS 
 SELECT CSV_DIR, CSV_PREFIX, CSV_DELIMITER, CSV_OPT, CSV_ZEILE1NAME
 FROM CSV_DATEIEN WHERE CSV_AKTIV='1'; 

BEGIN
 
 --1: The Loop starts
 
  open TABLE_CURSOR;
  loop
 
    fetch TABLE_CURSOR into 
    dir, prefix, delimiter, opt, zeile1name; 
    dateiname:=rpad(prefix, 26, dateischwanz);
  
--2: import to external table (This is the problem part!!)

    EXECUTE IMMEDIATE 'CREATE TABLE test1_ext ( 
                       datum varchar2(10),zeit varchar2(8),energie varchar2(16),
id varchar2(3),power varchar2(10),
rlt varchar2(10),vlt varchar2(10),volume varchar2(16),
flow varchar2(12))
                       ORGANIZATION EXTERNAL ( 
                       TYPE ORACLE_LOADER DEFAULT DIRECTORY sqlplus 
                       ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE 
                       FIELDS TERMINATED BY '','' 
                       MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS) 
                       LOCATION (testfile)
                       ) REJECT LIMIT UNLIMITED PARALLEL';

--3: Later I want to insert the data into a real table.....

  exit when TABLE_CURSOR%NOTFOUND; 
  end loop; 
 close TABLE_CURSOR;
END;

END exttabletest;
/
EXEC exttabletest

ORA-00905: missing keyword

LOCATION ( ''' || testfile || ''' )

  1  begin
  2  execute immediate 'CREATE TABLE test1_ext (
  3                         datum varchar2(10))
  4                         ORGANIZATION EXTERNAL (
  5                         TYPE ORACLE_LOADER DEFAULT DIRECTORY sqlplus
  6                         ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE
  7                         FIELDS TERMINATED BY '','')
  8                         LOCATION (testfile)
  9                         ) REJECT LIMIT UNLIMITED PARALLEL';
 10* end;
SQL> /
begin
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 2

  1  declare
  2    testfile varchar2(30);
  3    begin
  4    testfile := 'Helloworld.txt';
  5    execute immediate 'CREATE TABLE test1_ext (
  6                           datum varchar2(10))
  7                           ORGANIZATION EXTERNAL (
  8                           TYPE ORACLE_LOADER DEFAULT DIRECTORY crm_data
  9                           ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE
 10                           FIELDS TERMINATED BY '','')
 11                           LOCATION ('''||testfile||''')
 12                           ) REJECT LIMIT UNLIMITED PARALLEL';
 13*  end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select table_name, location from user_external_locations
  2  where table_name = 'TEST1_EXT';

TABLE_NAME                     LOCATION
------------------------------ ------------------------------
TEST1_EXT                      Helloworld.txt

SQL> alter table test1_ext location('myfile.txt');

Table altered.

SQL> select table_name, location from user_external_locations
  2  where table_name = 'TEST1_EXT';

TABLE_NAME                     LOCATION
------------------------------ ------------------------------
TEST1_EXT                      myfile.txt

create or replace
PROCEDURE csv_import
IS
BEGIN
DECLARE
 datumheute   VARCHAR2(10):= sysdate;  
 tag          VARCHAR2(2):= substr(sysdate, 1, 2);
 monat        VARCHAR2(2):=substr(datumheute, 4, 2);
 jahr         VARCHAR2(2):=substr(datumheute, 7, 2);
 dateischwanz VARCHAR2(50):=rpad(rpad(rpad(rpad(rpad(rpad('_20', 5, jahr), 6, '_'), 8, monat), 9, '_'), 11, tag), 15, '.csv');
 dateiname    VARCHAR2(50);
 dir          CSV_DATEIEN.CSV_DIR%TYPE; 
 prefix       CSV_DATEIEN.CSV_PREFIX%TYPE; 
 delimiter    CSV_DATEIEN.CSV_DELIMITER%TYPE; 
 opt          CSV_DATEIEN.CSV_OPT%TYPE;  
 zeile1name   CSV_DATEIEN.CSV_ZEILE1NAME%TYPE; 
 dummy        VARCHAR2(30) := 'dummy.csv';
 CURSOR TABLE_CURSOR IS 
 SELECT CSV_DIR, CSV_PREFIX, CSV_DELIMITER, CSV_OPT, CSV_ZEILE1NAME
 FROM CSV_DATEIEN WHERE CSV_AKTIV='1'; 
BEGIN
--1: create extern table
EXECUTE IMMEDIATE 'CREATE TABLE test4_ext ( 
datum varchar2(10),
zeit varchar2(8),
energie varchar2(16),
id varchar2(3),
power varchar2(10),
rlt varchar2(10),
vlt varchar2(10),
volume varchar2(16),
flow varchar2(12))
ORGANIZATION EXTERNAL ( 
TYPE ORACLE_LOADER DEFAULT DIRECTORY sqlplus 
ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE 
FIELDS TERMINATED BY ''' || delimiter || ''' 
MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS) 
LOCATION (''' || dummy || ''')
) 
REJECT LIMIT UNLIMITED PARALLEL';
--2: The Loop starts
  open TABLE_CURSOR;
  loop
    fetch TABLE_CURSOR into 
    dir, prefix, delimiter, opt, zeile1name; 
    dateiname:=rpad(prefix, 26, dateischwanz);
    dateiname:='Datenlogger_2008_03_27.csv';
--3: import to external table
      EXECUTE IMMEDIATE 'alter table test4_ext location(''' || dateiname || ''')';
--4: Later I want to insert the data into a real table.....
  exit when TABLE_CURSOR%NOTFOUND; 
  end loop; 
 close TABLE_CURSOR;
--5: Later I want to drop the external table here...
END;
END csv_import;
/
EXEC csv_import

create or replace PROCEDURE csv_import
IS
BEGIN
DECLARE
 datumheute   VARCHAR2(10):= sysdate;  
 tag          VARCHAR2(2):= substr(sysdate, 1, 2);
 monat        VARCHAR2(2):=substr(datumheute, 4, 2);
 jahr         VARCHAR2(2):=substr(datumheute, 7, 2);
 dateischwanz VARCHAR2(50):=rpad(rpad(rpad(rpad(rpad(
 rpad('_20', 5, jahr), 6, '_'), 8, monat), 9, '_'), 11, tag),
 15, '.csv');
 dateiname    VARCHAR2(50);
 dir          CSV_DATEIEN.CSV_DIR%TYPE; 
 prefix       CSV_DATEIEN.CSV_PREFIX%TYPE; 
 delimiter    CSV_DATEIEN.CSV_DELIMITER%TYPE; 
 opt          CSV_DATEIEN.CSV_OPT%TYPE;  
 zeile1name   CSV_DATEIEN.CSV_ZEILE1NAME%TYPE; 
 dummy        VARCHAR2(30) := 'dummy.csv';
 CURSOR TABLE_CURSOR IS 
 SELECT CSV_DIR, CSV_PREFIX, CSV_DELIMITER, CSV_OPT, CSV_ZEILE1NAME
 FROM CSV_DATEIEN WHERE CSV_AKTIV='1'; 
BEGIN
--1: create extern table
EXECUTE IMMEDIATE 'CREATE TABLE test1_ext ( 
datum varchar2(10),
zeit varchar2(8),
energie varchar2(16),
id varchar2(3),
power varchar2(10),
rlt varchar2(10),
vlt varchar2(10),
volume varchar2(16),
flow varchar2(12))
ORGANIZATION EXTERNAL ( 
TYPE ORACLE_LOADER DEFAULT DIRECTORY wmzdaten 
ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE 
FIELDS TERMINATED BY '';'' 
MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS) 
LOCATION (''' || dummy || ''')
) 
REJECT LIMIT UNLIMITED PARALLEL';
--2: The Loop starts
  open TABLE_CURSOR;
  loop
    fetch TABLE_CURSOR into 
    dir, prefix, delimiter, opt, zeile1name; 
    dateiname:=rpad(prefix, 26, dateischwanz);
    dateiname:='Datenlogger_2008_03_27.csv';
--3: import to external table
      EXECUTE IMMEDIATE 'alter table test1_ext location(''' || dateiname || ''')';
--4: insert the data into the productive table
      EXECUTE IMMEDIATE 'INSERT INTO test1 SELECT * FROM test1_ext';
  exit when TABLE_CURSOR%NOTFOUND; 
  end loop; 
 close TABLE_CURSOR;
--5: drop the external table
EXECUTE IMMEDIATE 'DROP TABLE test1_ext';
END;
END csv_import;