6. November 2007

CSV-Dateien einlesen mit SQL: Easy CSV Parser!

Heute (so zwischendurch) mal ein ganz anderes Thema: Wenn es darum geht, Kommaseparierte Dateien in die Datenbank zu laden, gibt es zahlreiche Wege: SQL*Loader, External Tables, Application Express, 3rd-Party Tools und zahlreiche Varianten mehr ... Alle erfordern mehr oder weniger Aufwand - Um eine external table anzulegen, muss man die SQL*Loader-Syntax schon gut kennen, auch wenn die Datei noch so einfach strukturiert ist.
Ich habe mich schon länger gefragt, ob die im Post LDAP Server abfragen mit SQL verwendeten völlig dynamischen Table Functions hier Verwendung finden können. Hintergrund ist ja, dass, verwendet man für Table Functions den sog. Interface Approach, die Struktur der zurückgegebenen Tabelle völlig dynamisch sein kann.
Also habe ich es mal probiert ... der Code ist zwar ein wenig länger geworden, aber eine erste Version ist fertig. Es funktioniert wie folgt:
select * from table (easy_csv_parser.parse(
  'CSV_DIR',     -- Directory-Objekt, in welchem die Datei liegt
  'file.txt',    -- Dateiname
  ',',           -- Feld-Delimiter
  '"',           -- Felder optional umschlossen von
  1,             -- 1: Es soll versucht werden, die Datentypen selbst zu erkennen
  1              -- 1: Spaltennamen aus erster Zeile entnehmen
))
/
Insbesondere wenn man mit dem CSV-Format noch experimentiert, sollte man stets noch einen SQL-Kommentar hinzufügen, um das SQL-Kommando für die Datenbank neu zu machen. Das Directory-Objekt muss auf ein Verzeichnis im Betriebssystem zeigen und ggfs. zunächst mit CREATE DIRECTORY erzeugt werden. Das Ergebnis mit bspw. dieser Datei hier ...
NAME,VORNAME,ORT,GEHALT,DATUM
Czarski,Carsten,München,400099,01-DEC-01
Franz,Muster,Trier,3000,01-JAN-01
"Mustermann, uu","Karl", Bad Homburg, 500, NODAT
... sieht dann etwa so aus:
NAME            VORNAME         ORT                 GEHALT DATUM
--------------- --------------- --------------- ---------- --------------------
Czarski         Carsten         München             400099 01-DEC-01
Franz           Muster          Trier                 3000 01-JAN-01
Mustermann, uu  Karl             Bad Homburg           500

3 Zeilen ausgewählt.
Probiert es einmal aus - Feedback ist erwünscht. Nächstes mal geht es wieder mit XML in der Datenbank, Ausführungsplänen und Performance weiter. Und hier kommt nun endlich der Code:
CREATE type easy_csv_parser as object(
  row_types          anytype,

  utlfile_id         number,
  utlfile_datatype   number,
  ecp_delimiter      varchar2(10),
  ecp_enclosed       varchar2(10),
  ecp_sample         number,

  static function parse(
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1, 
    p_cols_firstline   in number default 1 
  ) return anydataset pipelined using easy_csv_parser,

  static function ODCITableDescribe(
    record_table       out anytype,
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1, 
    p_cols_firstline   in number default 1 
  ) return number,

  static function ODCITablePrepare (
    sctx               out easy_csv_parser, 
    tab_func_info      in sys.ODCITabFuncInfo,
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number,

  static function ODCITableStart   (
    sctx               in out easy_csv_parser, 
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number, 

  member function ODCITableFetch   (
    self               in out easy_csv_parser,
    nrows              in number, 
    record_out         out anydataset
  ) return number,
  
  member function ODCITableClose   (
    self               in easy_csv_parser 
  ) return number
)
/
sho err

CREATE or replace type BODY easy_csv_parser as
 static function ODCITableDescribe(
    record_table       out anytype,
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number as
    v_record_structure anytype; 
    v_field_name       varchar2(4000); 
    v_field_data       varchar2(4000); 

    v_filehandle       utl_file.file_type;
    v_line_fields      varchar2(32767);
    v_line_data        varchar2(32767);

    v_f_ep             pls_integer := -1;
    v_f_sp             pls_integer := 1;
    v_d_ep             pls_integer := -1;
    v_d_sp             pls_integer := 1;
    v_enc_p            pls_integer := 1;
    v_field_pos        pls_integer := 1;

    v_typecode         pls_integer := dbms_types.typecode_varchar2;
    v_typecode_prec    pls_integer;
    v_data_date        date;
    v_data_number      number;
  begin 
    anytype.begincreate(dbms_types.typecode_object, v_record_structure);
    v_filehandle := utl_file.fopen(p_dir, p_filename, 'R'32767);

    utl_file.get_line(v_filehandle, v_line_fields, 32767);
    if p_cols_firstline = 1 then 
      utl_file.get_line(v_filehandle, v_line_data, 32767);
    else 
      v_line_data := v_line_fields;
    end if;

    while v_f_ep != 0 loop
     v_f_ep := instr(v_line_fields, p_delimiter, v_f_sp);
     v_d_ep := instr(v_line_data, p_delimiter, v_d_sp);
     if v_f_ep > 0 then 
      v_enc_p := instr(
       replace(v_line_fields, p_enclosed||p_enclosed, ''), 
       p_enclosed, 
       v_f_sp
      );
      if v_enc_p > 0 and v_enc_p < v_f_ep then 
       v_enc_p := instr(
        replace(v_line_fields, p_enclosed||p_enclosed, ''), 
        p_enclosed, 
        v_enc_p + 1
       );
       if v_enc_p > 0 then
        v_f_ep := instr(v_line_fields, p_delimiter, v_enc_p + 1);
       else 
        v_f_ep := instr(v_line_fields, p_delimiter, v_f_sp);
       end if; 
      end if;
     end if;
     if v_d_ep > 0 then 
      v_enc_p := instr(
       replace(v_line_data, p_enclosed||p_enclosed, ''), 
       p_enclosed, 
       v_d_sp
      );
      if v_enc_p > 0 and v_enc_p < v_d_ep then 
       v_enc_p := instr(replace(
        v_line_data, p_enclosed||p_enclosed, ''), 
        p_enclosed, 
        v_enc_p + 1
       );
       if v_enc_p > 0 then
        v_d_ep := instr(v_line_data, p_delimiter, v_enc_p + 1);
       else 
        v_d_ep := instr(v_line_data, p_delimiter, v_d_sp);
       end if; 
      end if;
     end if;
     if v_f_ep = 0 then 
       v_field_name := substr(v_line_fields, v_f_sp);
       v_field_data := substr(v_line_data, v_d_sp);
     else  
       v_field_name := substr(v_line_fields, v_f_sp, (v_f_ep - v_f_sp));
       v_field_data := substr(v_line_data, v_d_sp, (v_d_ep - v_d_sp));
     end if;
     if p_enclosed is not null then 
      v_field_name := regexp_replace(v_field_name, '^'||p_enclosed, '');
      v_field_name := regexp_replace(v_field_name, p_enclosed||'$''');
      v_field_data := regexp_replace(v_field_data, '^'||p_enclosed, '');
      v_field_data := regexp_replace(v_field_data, p_enclosed||'$''');
      v_field_data := rtrim(ltrim(v_field_data));
     end if;
     v_typecode := dbms_types.typecode_varchar2;
     if p_sample = 1 and v_field_data is not null then 
       begin
         v_data_date := to_date(v_field_data);
         v_typecode := dbms_types.typecode_date;
       exception when others then
         begin
           v_data_number := to_number(v_field_data);
           v_typecode := dbms_types.typecode_number;
         exception when others then null;
         end;
       end;
     end if;
     v_record_structure.addattr(   
       ANAME     => case 
                      when p_cols_firstline = 1 then upper(replace(v_field_name,' ','_')) 
                      else 'COLUMN_'||lpad(v_field_pos, 3'0') 
                    end,
       TYPECODE  => v_typecode,
       PREC      => null,
       SCALE     => null,
       LEN       => case 
                      when v_typecode = dbms_types.typecode_varchar2 then 4000 
                      else null 
                    end,
       CSID      => null,    
       CSFRM     => null,
       ATTR_TYPE => null
     );
     v_f_sp := v_f_ep + 1;
     v_d_sp := v_d_ep + 1;
     v_field_pos := v_field_pos + 1;
     v_enc_p := -1;
    end loop;
    
    v_record_structure.endcreate();

    anytype.begincreate(dbms_types.typecode_table, record_table); 

    record_table.setinfo(
      nullnullnullnullnull, v_record_structure, dbms_types.typecode_object, 0
    ); 
    record_table.endcreate(); 
    utl_file.fclose(v_filehandle);

    return odciconst.success;
  
  -- exception when others then  
  --  return odciconst.error;
  end;   

  static function ODCITablePrepare (
    sctx               out easy_csv_parser, 
    tab_func_info      in sys.ODCITabFuncInfo, 
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number is
    prec         pls_integer; 
    scale        pls_integer; 
    len          pls_integer; 
    csid         pls_integer; 
    csfrm        pls_integer; 
    record_desc  anytype; 
    aname        varchar2(30); 
    dummy        pls_integer; 

  begin 
      -- insert into log values ('ODCITablePrepare entered') ; commit;
    dummy := tab_func_info.RetType.GetAttrElemInfo(
      null, prec, scale, len, csid, csfrm, record_desc, aname
    ); 
    sctx := easy_csv_parser(
      record_desc, nullnull, p_delimiter, p_enclosed, p_sample
    ); 
    return odciconst.success; 
  end; 

  static function ODCITableStart   (
    sctx               in out easy_csv_parser, 
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number is
    v_filehandle       utl_file.file_type;
    v_line       varchar2(32767);
  begin 
    v_filehandle := utl_file.fopen(p_dir, p_filename, 'R'32767);
    if p_cols_firstline = 1 then 
      utl_file.get_line(v_filehandle, v_line);
    end if;
    sctx.utlfile_id := v_filehandle.id;
    sctx.utlfile_datatype := v_filehandle.datatype;
    return odciconst.success; 
  end; 
 

  member function ODCITableFetch   (
    self               in out easy_csv_parser,
    nrows              in number, 
    record_out         out anydataset
  ) return number is
    v_filehandle       utl_file.file_type;

    v_field            varchar2(4000);
    v_line             varchar2(32767);
    v_f_ep             pls_integer := -1;
    v_f_sp             pls_integer := 1;
    v_enc_p            pls_integer := -1;
  
    v_field_pos        pls_integer := 1;

    d_prec             PLS_INTEGER; 
    d_scale            PLS_INTEGER;
    d_len              PLS_INTEGER;
    d_csid             PLS_INTEGER;
    d_csfrm            PLS_INTEGER;
    d_schema_name      VARCHAR2(30); 
    d_type_name        VARCHAR2(30); 
    d_version          varchar2(100);
    d_count            PLS_INTEGER;
    d_desc             pls_integer;
    d_attr_elt_type    anytype;
    d_aname            varchar2(30);
  begin 
    v_filehandle.id := self.utlfile_id;
    v_filehandle.datatype := self.utlfile_datatype;
 
    d_desc := self.row_types.getinfo(
      d_prec, d_scale, d_len, d_csid, d_csfrm, 
      d_schema_name, d_type_name, d_version, d_count
    );

    record_out := null;
    anydataset.begincreate(dbms_types.typecode_object, self.row_types, record_out); 
    record_out.addinstance;
    record_out.piecewise(); 
    begin
     utl_file.get_line(v_filehandle, v_line, 32767);
     while v_f_ep != 0 and v_field_pos <= d_count loop
      v_f_ep := instr(v_line, ecp_delimiter, v_f_sp);
      if v_f_ep > 0 then 
       v_enc_p := instr(
        replace(v_line, self.ecp_enclosed||self.ecp_enclosed, ''), 
        self.ecp_enclosed, 
        v_f_sp
       );
       if v_enc_p > 0 and v_enc_p < v_f_ep then 
        v_enc_p := instr(
         replace(v_line, self.ecp_enclosed||self.ecp_enclosed, ''), 
         self.ecp_enclosed, 
         v_enc_p + 1
        );
        if v_enc_p > 0 then
         v_f_ep := instr(v_line, ecp_delimiter, v_enc_p + 1);
        else 
         v_f_ep := instr(v_line, ecp_delimiter, v_f_sp);
        end if; 
       end if;
      end if;
      if v_f_ep = 0 then 
       v_field := substr(v_line, v_f_sp);
      else 
       v_field := substr(v_line, v_f_sp, (v_f_ep - v_f_sp));
      end if;
      if self.ecp_enclosed is not null then 
       v_field := regexp_replace(v_field, '^'||self.ecp_enclosed, '');
       v_field := regexp_replace(v_field, self.ecp_enclosed||'$''');
      end if;
      d_desc := self.row_types.getattreleminfo(
       v_field_pos, d_prec, d_scale, d_len, d_csid, d_csfrm, d_attr_elt_type, d_aname
      );
      if d_desc = dbms_types.typecode_varchar2 or self.ecp_sample = 0 then 
        record_out.setvarchar2(v_field);
      elsif d_desc = dbms_types.typecode_date then 
       begin
        record_out.setdate(to_date(ltrim(rtrim(v_field))));
       exception when others then 
        record_out.setdate(to_date(null));
       end;
      elsif d_desc = dbms_types.typecode_number then 
       begin
        record_out.setnumber(to_number(v_field));
       exception when others then 
        record_out.setnumber(to_number(null));
       end;
      end if;
      v_f_sp := v_f_ep + 1;
      v_field_pos := v_field_pos + 1;
     end loop;
     while v_field_pos <= d_count loop
      d_desc := self.row_types.getattreleminfo(
       v_field_pos, d_prec, d_scale, d_len, d_csid, d_csfrm, d_attr_elt_type, d_aname
      );
      if d_desc = dbms_types.typecode_varchar2 or self.ecp_sample = 0 then 
       record_out.setvarchar2(null);
      elsif d_desc = dbms_types.typecode_date then 
       record_out.setdate(to_date(null));
      elsif d_desc = dbms_types.typecode_number then 
       record_out.setnumber(to_number(null));
      end if;
      v_field_pos := v_field_pos + 1;
     end loop;
     record_out.endcreate;
    exception 
     when NO_DATA_FOUND then 
      begin
       record_out.endcreate;
      exception when others then null;
      end;
      record_out := null;
    end;
    return odciconst.success; 
  end; 

  
  member function ODCITableClose   (
    self               in easy_csv_parser 
  ) return number is
   v_filehandle     utl_file.file_type;
  begin
    v_filehandle.id := self.utlfile_id;
    v_filehandle.datatype := self.utlfile_datatype;
    utl_file.fclose(v_filehandle);
    return odciconst.success; 
  end;
end;
/
sho err

Kommentare:

Jens Kropp hat gesagt…

Schönes Ding! Vielen Dank! Ich hab's gleich implementiert.

Steffen hat gesagt…

Tolle Idee - vielen Dank für diesen nützlichen Beitrag. Ich musste es auch gleich mal ausprobieren, hatte allerdings ein kleineres Problem mit der Vergabe der Column Names, da in meiner Sample CSV Datei die Spaltennamen Leerzeichen hatten sowie mixed case (z.B. "View Status").

Habe daher kleinen Vorschlag für Zeile 173:
upper(replace(v_field_name,' ','_'))


Im context:

v_record_structure.addattr(
ANAME => case
when p_cols_firstline = 1 then upper(replace(v_field_name,' ','_'))
else 'COLUMN_'||lpad(v_field_pos, 3, '0')
end,

Carsten Czarski hat gesagt…

Das ist eine gute Idee ... und auch schon integriert ... Vielen Dank!

-Carsten

Dirk hat gesagt…

Hallo,

wenn ich im Beispiel als Trennzeichen ein Semikolon einsetze, kommt nicht das gewünschte Ergebnis raus, sondern:

NAME,VORNAME,ORT,GEHALT,DATUM
-----------------------------
Czarski
Franz
Mustermann, uu


Desweiteren habe ich ein Problem, wenn ich versuche folgende Datei einzulesen:

Strasse,Hausnummer
Henri-Dunant-Straße,53

Bei Kommata als Trennzeichen funktioniert es, ausser das das ß nicht richtig dargestellt wird.

Mit Semikolon als Trennzeichen liefert die erste Abfrage kein Ergebnis. Wenn die Abfrage ein 2.mal ausgeführt wird, bekomme ich eine Fehlermeldung:

OALL8 befindet sich in einem inkosistenten Status.

Ausgeführt wurde die Abfrage über den SQL-Developer, DB Version ist die XE unter Windows XP.

Carsten Czarski hat gesagt…

Ich habe es gerade nochmal probiert - Hier ist die Datei mit den Semikolons:

FELD1;FELD2;Feld3
Wert1;Wert2;Wert3
Werta;Wetb;Wert,c

select * from table(easy_csv_parser.parse('CSV_DIR','test.txt', ';','',1,1)
)

FELD1 FELD2 FELD3
---------- ---------- ----------
Wert1 Wert2 Wert3
Werta Wetb Wert,c

Das falsch dargestellte "ß" könnte an den NLS_LANG-Einstellungen liegen ... mal im SQL*Plus probiert ...?

Anonym hat gesagt…

Adding the followoing to ODCITableClose makes the SQL-statement "neu" for every call:

select object_id
into t_id
from user_objects
where object_name = 'EASY_CSV_PARSER'
and object_type = 'TYPE BODY';
-- invalidating of the type body forces that ODCITableDescribe is executed for every call to the function
-- and we do need that to make sure that any new columns are picked up
dbms_utility.invalidate( t_id );


Anton

Anonym hat gesagt…

Ein sehr gelungenes Werkzeug - Danke !
Wie könnte ich den Parser verwenden um die Rückgabe aus der csv-Datei in eine Table zu schreiben ? Die Spalten sind bereits definiert.

Siegmund

Carsten Czarski hat gesagt…

müsste doch einfach mit einem INSERT into [table] (SELECT ...) gehen, oder ...?

Viele Grüße

-Carsten

Anonym hat gesagt…

Super gemacht! Vielen Dank!! Leider habe ich das Problem das die Umlaute nicht richtig dargestellt werden.

Carsten Czarski hat gesagt…

Hallo (anonym),

die Frage ist nun, ob sie falsch gelesen oder nur falsch dargestellt werden.

Mit welchem Werkzeug arbeitest Du denn - SQL*Plus?

Welches Betriebssystem ...?

Ganz gängig ist die falsche Darstellung von Umlauten bei SQL*Plus auf Windows ... dies könnte man mit einem einfachen Trick beheben ...

set NLS_LANG=GERMAN_GERMANY.WE8PC850

Hilft das ...?

-Carsten

Anonym hat gesagt…

Funktioniert leider nicht
Folgender Fehler im JDeveloper

Error(61,1): PLS-00103: Encountered the symbol "/" when expecting one of the following: ; not alter final instantiable

Carsten Czarski hat gesagt…

Hallo,

die Skripte sind für SQL*Plus gemacht; der "/" ist ein SQL*Plus-Zeichen, dass das Skript zu Ende ist.

Im JDeveloper müsste man das einfach wegnehmen ... oder die Skripte mit SQL*Plus bzw. dem SQL Developer einspielen ...

Grüße

-Carsten

Anonym hat gesagt…

Ohweh hab es jetzt mit SQL *Plus probiert Teil 1 läuft Durch.
Teil 2 hört mittendrin ein paar mal auf hab den Code jeweils an entsprechender Stelle fortgesetzt. Am Ender kommen folgende Fehler:


Warnung: Typrumpf wurde mit Kompilierungsfehlern erstellt.

SQL> sho err
Fehler bei TYPE BODY EASY_CSV_PARSER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/24 PL/SQL: Item ignored
15/24 PLS-00201: identifier 'UTL_FILE' must be declared
32/5 PL/SQL: Statement ignored
32/5 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

34/5 PL/SQL: Statement ignored
34/23 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

36/7 PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
36/25 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

139/5 PL/SQL: Statement ignored
139/21 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

186/24 PL/SQL: Item ignored
186/24 PLS-00201: identifier 'UTL_FILE' must be declared
189/5 PL/SQL: Statement ignored
189/5 PLS-00320: the declaration of the type of this expression is

LINE/COL ERROR
-------- -----------------------------------------------------------------
incomplete or malformed

191/7 PL/SQL: Statement ignored
191/25 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

193/5 PL/SQL: Statement ignored
193/24 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

194/5 PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
194/30 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

Carsten Czarski hat gesagt…

Hallo,

naja ... das Packet braucht UTL_FILE - und das fehlt in der Datenbank entweder oder es fehlen die Rechte darauf ... ich glaube eher letzteres. Der DBA müsste also Privilegien auf UTL_FILE einräumen ...

Beste Grüße

-Carsten

Anonym hat gesagt…

welche Privilegien bräuchte ich denn als HTMLDB - Entwickler?

Carsten Czarski hat gesagt…

Hallo,

naja ... ein APEX-Workspace hat normalerweise alle, die man braucht. Das Paket Easy CSV-Parser möchte jedoch auf das Dateisystem des Datenbankservers zugreifen (UTL_FILE); und dafür braucht man zusätzliche Privilegien ...

Grüße

-Carsten

Anonym hat gesagt…

ich werde mal EXECUTE Rechte beantragen...mal sehen ob es hilft.

Vielen Dank für den Support ;-)

Anonym hat gesagt…

Hallo Carsten

wo können sich die einzulesenden Files befinden? Ich hab Probleme die Privilegien zu bekommen weil mein DBA meint ich würde om OS rumfummeln auf dem Unser Ora- Server läuft.

Carsten Czarski hat gesagt…

Hallo,

der DBA hat recht - dieses Package ist fürs Parsing von Dateien auf dem Datenbankserver vorgesehen. Also werden entsprechende Privilegien benötigt:
* UTL_FILE für den generellen Dateizugriff
* DIRECTORY-Objekt für die konkreten Dateien

Grüße

-Carsten

Anonym hat gesagt…

dann bin ich ja eh auf dem Holzweg. Welche Möglichkeit gibt es denn csv Files (Eigentlich xls-Files) auf einem Win Fileserver auszulesen bzw per PL/SQL abzufragen und zu editieren?

Carsten Czarski hat gesagt…

Hallo,


naja --- das muss kein Holzweg sein; wenn man das Share des Windows-Fileservers auf dem DB Server mountet (Samba?) und dann mit UTL_FILE und DIRECTORY-Objekten arbeitet, geht das schon ...

Ansonsten muss man wohl Programme schreiben ...

Grüße

-Carsten

Anonym hat gesagt…

Nach Rücksprache mit Meinem DBA werden wir die Sache mit dem Mounten angehen ;-)

Vielen Dank Carsten!

Einen Schönen Tag noch!
Grüße aus Hessen

Anonym hat gesagt…

Hallo, diese Variante ist einfach Super !!!

Allerdings versuchte ich vorhin eine Stunde lang dieses Konstrukt in einer PL/SQL Procedure zu verwenden um eine Importprocedure bereitzustellen. Ich bekomme den ORA-22905.

Ich habe diverse Versuche mit TABLE(CAST(easycsv.. as MEINDATENTYP_TAB)) hinter mir, als Cursor, als OPEN sql for LOOP, es will nicht funktionieren.
Die Rückgabestruktur hatte ich als Typ angelegt/beschrieben, aber das funktioniert irgendwie nicht (ich werde sogar hart von ORACLE abgemeldet-nicht mit Oracle verbunden, wenn ich die Procedure derzeit starte).

Ich muss doch sehr wahrscheinlich sagen, wie die Rückgabestruktur genau aussieht, sonst komme ich in PL/SQL nicht weit, oder ?
Haben Sie ein Beispiel dafür ?

Ach ja, Oracle 11gR2 64bit auf Windows2008R2

Anonym hat gesagt…

Nettes Werkzeug.

Leider funktioniert bei mir die Parameter-Einstellung 0 im letzten Prameter (Spaltennamen aus erster Zeile entnehmen) nicht und endet in einem ORA-00902: Ungültiger Datentyp.
Und das, obwohl ich zunächst nur Varchar2-Typen einlese.

Hat jemand eine Idee oder einen Tipp?

Danke und Gruss
Jochen Schulz

Carsten Czarski hat gesagt…

Hallo Jochen,

hmmm ... so aus der Ferne ist das nur schwer zu beurteilen - hast Du ein Beispiel, mit dem der Fehler auftritt ...?

Beste Grüße

Carsten

Anonym hat gesagt…

Hallo Carsten,

Danke für die prompte Antwort.

Testdaten brauche ich gar nicht zur Verfügung zu stellen, da es auch mit Deinen Testdaten nicht funktioniert. Übrigens interessanterweise, wenn ich für die letzten beiden Paramter ("Es soll versucht werden, die Datentypen selbst zu erkennen" resp. "Spaltennamen aus erster Zeile entnehmen") den Wert "0" anstatt "1" einsetze.
Die Defaults von "1" arbeiten fehlerfrei, allerdings fände ich das Feature, die erste Zeile als Überschriften zu definieren schon cool.

Die Oracle-Version ist übrigens 11.2.0.3.0 - aber ich glaube kaum, dass es damit was zu tun haben dürfte.

lg
Jochen

Anonym hat gesagt…

Korrektur, sorry, die Defaults von "1" erzeugen den Fehler, "0" funktioniert.
M.a.W. es funktioniert die Übernahme der Spaltennamen nicht, wenn die erste Zeile diese enthält, dann wird der ORA-00902: Ungültiger Datentyp-Fehler geworfen.

Carsten Czarski hat gesagt…

Hallo Jochen,

hmm ... ich habe das jetzt nochmals mit allen vier Permutationen von "1" und "0" getestet; es funktioniert auf meinem System blasenfrei.
Ich habe eine Datenbank auf Linux - welche Plattform verwendest Du? Wenn es Windows ist, könnte mir vorstellen, dass es am "Line-Terminator" liegt.

Beste Grüße

-Carsten

Anonym hat gesagt…

Ich verwende TOAD und SQL-Developer auf Windows.

Habe aber etwas Skuriles herausgefunden:

Wenn ich den Parameter auf "1" setze und die Varchar2-variable v_field_name um mindestens 3 Characters durch Konkatinierung verlängere, funktioniert es,
bei weniger als 3 Parametern funktioniert es nicht.
Leider sind die Variablen v_f_sp etc. im Code weiter oben etwas schlecht interpretierbar und die Kommentierung des Codes etwas spärlich.

Also: Das funktioniert

ANAME => case
when p_cols_firstline = 1 then upper(replace(v_field_name,' ','_')) || '123'
else 'COLUMN_'||lpad(v_field_pos, 3, '0')
end,

DAS nicht

ANAME => case
when p_cols_firstline = 1 then upper(replace(v_field_name,' ','_')) || '12'
else 'COLUMN_'||lpad(v_field_pos, 3, '0')
end,

DAS ORIGINAL SCHON GAR NICHT

ANAME => case
when p_cols_firstline = 1 then upper(replace(v_field_name,' ','_'))
else 'COLUMN_'||lpad(v_field_pos, 3, '0')
end,

WARUM?


Carsten Czarski hat gesagt…

Hallo Jochen,

Tja - warum? Das ist in der Tat eine gute Frage. Da ich das Problem bei mir nicht nachstellen kann, fällt es mir schwer, etwas dazu zu sagen ...

Ich muss mal ein wenig darüber nachdenken - im Moment habe ich erstmal keine Idee ...

Beste Grüße

Carsten

Anonym hat gesagt…

Hallo Carsten,

Danke. Verstehe, dass das ganz schwierig ist, wenn man es nicht nachstellen kann.
Habe aber mit einem erfahrenen Entwickler als "Zeugen" drübergeschaut, der genauso verdutzt und ratlos ist wie ich.

Wahrscheinlich ist auch die Stellenanzahl von 30 für die Überschriften kritisch - das jedenfalls haben weitere Tests ergeben.

Nur just for Info - folgender Workaround funktioniert:

--
-- ACHTUNG: die Konkatinierung mit mindestens 3 (!) Zeichen ist ein Workaround, der warum-auch-immer funktioniert.
-- Dieser ist nur im "Überschriftenmodus notwendig
-- Orignialcode: upper(replace(v_field_name,' ','_'))
-- Workaround : substr(upper(replace(v_field_name,' ','_')) || ' ',1,29)
--

v_record_structure.addattr(
ANAME => case
when p_cols_firstline = 1 then substr(upper(replace(v_field_name,' ','_')) || ' ',1,29)
else 'COLUMN_'||lpad(v_field_pos, 3, '0')
end,
TYPECODE => v_typecode,
...

lg
Jochen

Anonym hat gesagt…

Hallo Carsten,
das ist ein sehr interessanter Ansatz.
Ich habe den umgekehrten Fall:
Verwende eine Function-Based-Table und möchte einen übergebenen Cursor dynaisch auswerten:
SELECT *
FROM TABLE (PKG_EXPORT_FILE.F_EXPORT_FILE (
CURSOR (SELECT SPALTE1, SPALTE2, SPALTE_USW FROM MEINE_TABELLE)
));
Um dann das Ergebnis des Cursors (also die gelieferten Daten) über ein BULK-Select in ein Record zu schreiben um sie dann über UTL_FILE in eine Datei zu schreiben.
Das Problem dabei ist: Ich kenne die Struktur des Cursors nicht.
Ich muss also dynaisch (evtl. über DBMS_SQL) herausfinden, welcher Typ die jeweilige Spalte ist, um dann einen Record zu definieren. Diesen könnte ich dann für das BULK verwenden.
Soweit so gut, mit normalen PL/SQL-Mitteln komme ich nicht weiter. Der objektorientierte Ansatz klingt aber vielversprechend (auch wenn ich damit bisher noch nichts gemacht habe).
Ist das ein Weg oder eher eine Sackgasse?

Viele Grüße
Eric

Carsten Czarski hat gesagt…

Hallo Eric,

wenn es Dir darum geht, einen Text zu erzeugen, kannst Du auch einfach hergehen und alle Spalten des Cursors in VARCHAR2-Variablen übernehmen. Ist die Spalte vom Typ NUMBER, DATE oder TIMESTAMP, so wird implizit nach VARCHAR2 konvertiert. Das VARCHAR2 kannst Du dann per UTL_FILE rausschreiben oder mit DBMS_LOB.WRITEAPPEND an einen CLOB anhängen. Er verwendet dann (natürlich) Standardformatmasken. (hier ist ein Beispiel dazu: http://sql-plsql-de.blogspot.co.uk/2015/03/string-operationen-auf-clobs-richtig.html)
Wenn Du die Datentypen feststellen möchtest, musst Du mit DBMS_SQL arbeiten; nur dort hast Du die Describe-Funktionen.

Beste Grüße

-Carsten

Beliebte Postings