Große Datenmengen in Oracle 9i häppchenweise fetchen

[deefens]

Hallo Forumsteilnehmer,

ich brauche für meine Client-Anwendung eine Funktion, die das
Exportieren von Tabellendaten im CSV-Format ermöglicht. Da ein Select
* auf eine sehr große Tabelle dazu führt, dass der Client erstmal
längere Zeit gar nichts macht, wollte ich die Daten in mehreren
Durchläufen abwechselnd auslesen und auf die Platte schreiben.

Ich habe mich Serverseitig bereits schlau gemacht und mir folgenden
Code zurechtgebastelt:

Code:

spool c:\test
declare
  cursor c1 is
    select * from F_VBO_DC_OVERVIEW;
  type t_records is table of F_VBO_DC_OVERVIEW%ROWTYPE;
  records t_records; 
begin
  open c1;
  loop
    fetch c1 BULK COLLECT INTO records LIMIT 5;
    for i in 1..records.count loop
      dbms_output.put_line(records(i).MARKET_SHARE_PYM);
    end loop;
    exit when c1%notfound;
  end loop;
  close c1;
end;
/
spool off

Statt einem Spool in eine Datei will ich aber die Daten zur
Clientanwendung übertragen und dort sequentiell weiterverarbeiten vor
dem Speichern. HAt jemand eine Idee, wie ich das anstellen könnte? Ich
bräuchte sozusagen eine konstante Verbindung zur Datenbank während des
Fetchvorgangs mit Übertragung der Zwischenergebnisse... Als
DAtenbankkomponenten verwende ich die ODAC-Komponente von CoreLab, es
täte aber auch eine Lösung für dbExpress.

Danke für jeden Hinweis!!

[omata]

Moin,

um wieviel Datensätze geht es denn hier.

ich habe mal folgendes getestet...

500000 Datensätze ergaben eine CSV-Datei von 26MB und das dauerte 1 Min 10 Sec. (das hängt natürlich von der Rechnergeschwindigkeit ab)

Aber, wenn das so viele Datensätze sind dauert das naturlich auch eine gewisse Zeit.
Mit mehreren Abfragen würde ich das nicht so gut finden, weil dann in der Zwischenzeit Änderungen stattfinden könnten (Mehrbenutzerfähigkeit).

Mach doch einen Prozentbalken, dann sieht der Nutzer das noch was geschieht.

Beispiel mit DBExpress...

Delphi-Quellcode:

procedure TForm.SQLtoCSV(SQL, Dateiname: string; ProgressBar:TProgressBar; SQLConnection:TCRSQLConnection);
var SDS:TSimpleDataSet;
    i:integer;
    Datei:Textfile;
begin
  assignfile(Datei, Dateiname);
  try
    rewrite(Datei);
    SDS:=TSimpleDataSet.Create(Self);
    try
      SDS.Connection:=SQLConnection;
      SDS.DataSet.CommandText:=SQL;
      SDS.Open;
      ProgressBar.Min:=1;
      ProgressBar.Max:=SDS.RecordCount;
      ProgressBar.Step:=1;
      for i:=1 to SDS.FieldCount do begin
        write(Datei, SDS.Fields[i-1].FieldName);
        if i < SDS.FieldCount then
          write(Datei, ';');
      end;
      writeln(Datei);
      while not SDS.Eof do begin
        ProgressBar.StepIt;
        for i:=1 to SDS.FieldCount do begin
          write(Datei, SDS.Fields[i-1].AsString);
          if i < SDS.FieldCount then
            write(Datei, ';');
        end;
        writeln(Datei);
        SDS.Next;
      end;
      SDS.Close;
    finally
      SDS.Free;
    end;
  finally
    closefile(Datei);
  end;
end;

MfG
Thorsten

[Robert_G]

Zitat von omata:

500000 Datensätze ergaben eine CSV-Datei von 26MB und das dauerte 1 Min 10 Sec. (das hängt natürlich von der Rechnergeschwindigkeit ab)

Eben, in den 20 - 30 Sekunden bis die Daten da sind, würde der User nicht wissen was gerade passiert, bzw. wieweit das Programm schon ist.

Zitat von omata:

Aber, wenn das so viele Datensätze sind dauert das naturlich auch eine gewisse Zeit.
Mit mehreren Abfragen würde ich das nicht so gut finden, weil dann in der Zwischenzeit Änderungen stattfinden könnten (Mehrbenutzerfähigkeit).

Ein Ref Cursor ist _EINE_ Abfrage, du kannst über ihn, als Refferenz, die gleiche Ergebnismenge an unterschiedlichen Stellen verwenden.
Deshalb war der Ansatz von deefens schon ganz OK.
IMHO hast du jetzt 2 Möglichkeiten:

• Die CoreLabs-Kompos erlauben dir eine gewisse Cache-Größe zu bestimmen. (zum Bleistift soll er nur alle 1,000 Records die nächsten 1,000 holen)
• oder, du musst den Kampf da unten aufnehmen.

Das Problem, was ich jetzt sehe, ist das du das ganze als Bind variablen auslesen und übergeben musst.
Deshalb fällt eine "table of record" weg. (Ist ja ein PL/SQL-Typ, kein SQL-Typ )
Möglich wäre jetzt ein SQL Typ und ein weiterer SQL Typ als "table of [deinTyp]".

SQL-Code:

create or replace type rec$F_VBO_DC_OVERVIEW as Object
(
  /* Hier sollten alle nötigen Spalten (in der richtigen Reihenfolge !!!) stehen */
  A Integer
 ,B Varchar2(200)
 ,C Varchar2(20)
)

Der Tabellentyp:
create ore replace type tbl$F_VBO_DC_OVERVIEW as table of rec$F_VBO_DC_OVERVIEW Ein Package, in dem alle nötigen Functionen stehen könnte so aussehen:

• Spec:
  SQL-Code:

  create or replace package TestCurPck is
    cursor CursorJustForOpening is
      /* default constructor deines Typs (auch hier die Spaltenreihenfolge beachten!) */
      SELECT rec$F_VBO_DC_OVERVIEW(A
                                  ,B
                                  ,C)
      FROM   F_VBO_DC_OVERVIEW;
  
    type StrongTypedRefCur is
      Ref Cursor return CursorJustForOpening%RowType;
  
    function OpenCursor return StrongTypedRefCur;
  
    function FetchRecs
    (
      iCur      in StrongTypedRefCur
     ,iRecLimit in Integer
     ,ioTable   in out nocopy tbl$F_VBO_DC_OVERVIEW
    ) return integer;
  
  end TestCurPck;

• Body:
  SQL-Code:

  create or replace package body TestCurPck is
  
    function OpenCursor return StrongTypedRefCur is
      oCur StrongTypedRefCur;
    begin
      open CursorJustForOpening;
      oCur := CursorJustForOpening;
      return oCur;
    end;
    
    function FetchRecs
    (
      iCur      in StrongTypedRefCur
     ,iRecLimit in Integer
     ,ioTable   in out nocopy tbl$F_VBO_DC_OVERVIEW
    ) return integer is
    begin
      fetch iCur
        bulk collect into ioTable
        limit iRecLimit;
      if iCur%NotFound then
        return 1;
      else
        return 0;
      end if;
    end;
  
  end TestCurPck;

Da ich jetzt hier zu Hause nur meine eigene Ora 8.16 Personal Lizenz habe, kann ich das gute alte WeakTyped/StongTyped Cursor Problem nicht nachtesten.
(Ora9.2 verhält sich da nicht so zickig, aber _anders_, außerdem habe ich gerade einen akuten Mangel an Testlaune )
Das Problem ist, dass RefCursor generell weak typed sind. Für Bulk collect braucht du aber einen strong typed Cursor.
Du müsstest nämlich zuerst einen Ref Cursor öffnen und als Bind variable auslesen.

SQL-Code:

begin
  :oCur := TestCurPck.OpenCursor;
/* mit etwas Glück reicht es die Refferenz auf einen StrongTyped Cursor zu verwenden */
end;

Jetzt könnstest du FetchRecs ausführen (solange bis iState = 1).

SQL-Code:

begin
  /* Wenn Ora hinter dem RefCursor den geöffneten StrongTyped Cursor aus dem ersten Statement erkennt ist alles in Butter :) */
  :iState := TestCurPck.FetchRecs(:iCur
                                 ,:iRecLimit
                                 ,:ioTable);
end;

Wie du Objekte (die tbl$F_VBO_DC_OVERVIEW) oder Cursor als Bind variablen verwenden kannst, sollte dir die Doku der CoreLabs-Kompos verraten.

so, das ganze teste ich morgen mal in meiner 9.2-SandBox...

Nachtrag:
Herzlich Willkommen in der Delphi-PRAXIS!
Nur mal nebenbei die TOracleQuery aus DOA ist von sich aus Multithreaded (da sie außerdem einen RefCur kapselt gibt's die Problematik damit nicht )

[deefens]

hallo robert,

das sieht sehr vielversprechend aus, was du da so alles geschrieben hast. dir doa-komponenten scheiden für meine zwecke leider aus, da ich bereits die odac-komponenten registriert habe und oda um einiges teurer zu sein scheint ich werde deinen läsungsansatz heute abend mal checken und mich dann wieder melden.

gruß,
stefan

[ipworks]

Hallo.
Ich denke Dir geht's um die User-Info, dass der Rechner noch beschäftigt ist.
Warum fragst du nicht erst mit SUM die Anzahl der Datensätze ab und informierst den User über die Anzahl.
Danach kannst Du auch über einen Fortschrittsbalken den eigentlichen Fortschritt anzeigen, so ist die eigentliche Abfrage der zweite Schritt und der User kann warten (z.B. Kaffee holen gehen)
Peter

P.S: Alternativ kannst Du ihm mit einem AVI zeigen, dass die Maschine noch ackert.

[Robert_G]

HiHi, ich merke gerade, dass ich mir um 23:00 und ohne Ora keine solchen Sachen aus den Fingern saugen sollte.


So ist's gleich viel einfahcer:

• Spec:
  SQL-Code:

  create or replace package fetch$F_VBO_DC_OVERVIEW is
  
    function FetchRecs
    (
      iRecLimit in integer
     ,ioTable   in out nocopy tbl$F_VBO_DC_OVERVIEW
    ) return integer;
  
  end;

• Body:
  SQL-Code:

  create or replace package body fetch$F_VBO_DC_OVERVIEW is
  
    cursor cur$F_VBO_DC_OVERVIEW is
      SELECT rec$F_VBO_DC_OVERVIEW(A
                                  ,B
                                  ,C)
      FROM   F_VBO_DC_OVERVIEW;
  
    function FetchRecs
    (
      iRecLimit in integer
     ,ioTable   in out nocopy tbl$F_VBO_DC_OVERVIEW
    ) return integer is
    begin
      if not cur$F_VBO_DC_OVERVIEW%IsOpen then
        open cur$F_VBO_DC_OVERVIEW;
        dbms_application_info.set_action('cursor opened');
      end if;
    
      fetch cur$F_VBO_DC_OVERVIEW bulk collect
        INTO ioTable limit iRecLimit;
      if cur$F_VBO_DC_OVERVIEW%NotFound then
      
        declare
          RecCount integer := cur$F_VBO_DC_OVERVIEW%rowcount;
        begin
          close cur$F_VBO_DC_OVERVIEW;
          dbms_application_info.set_action('cursor closed (' || RecCount || ' records)');
        
        end;
  
        return 0;
      else
        dbms_application_info.set_action(cur$F_VBO_DC_OVERVIEW%rowcount || ' records fetched');
        return 1;
      end if;
    end;
  
  begin
    dbms_application_info.set_module('test fetching chunks of data'
                                    ,'initialize package');
  end;

Der cursor kann im Body des Package versteckt werden. Da eine Package variable über den Zeitraum einer Session "am Leben" bleibt, braucht man keinen RefCursor mehr hin & her zuschieben.

Du musst jetzt nur noch herausfinden, wie du ein Objekt vom Typ tbl$F_VBO_DC_OVERVIEW mit deiner Anwendung verbinden kannst.
In DOA würde das so aussehen (Wobei ich in DOA einfach SomeQuery.Threaded := true geschrieben hätte, ohne das package und den ganzen Käse ):

Delphi-Quellcode:

var
  RecLimit :integer;
  tblRecords :TOracleObject;
  SomeQuery  :TOracleQuery;
begin
  //...
  RecLimit := 1000;
  tblRecords := TOracleObject.Create(SomeSession, 'tbl$F_VBO_DC_OVERVIEW','');
  SomeQuery := TOracleQuery.Create(nil);
  try
   
    with SomeQuery do
    begin
       Session := SomeSession;
       DeclareVariable('oRecCount', otInteger);
       DeclareAndSetVariable('iRecLimit', otInteger, RecLimit);
       DeclareVariable('ioTable', otObject);
       SetComplexVariable('ioTable', tblRecords);
       SQL.Text :=
         'begin';
         '  :oRecCount := fetch$F_VBO_DC_OVERVIEW.FetchRecs(:iRecLimit, :ioTable);' + #10 +;
         'end;';
    end;
  
    repeat
       SomeQuery.Execute();
       // Mach' irgendwas mit tblRecords 
    until IrgendWas = IrgendWasAnderes;
  finally
    SomeQuery.Free();
  end;

[deefens]

Robert, you are my hero Danke für die Mühe, das sollte funktionieren. Noch eine zweite Frage, da du dich offenbar auch mit den CoreLab-Komponenten auszukennen scheinst: könnte ich nicht auch irgendwie über die Properties FetchAll und FetchRows die Vorgehensweise des Fetchens beeinflussen und damit den Einsatz eines separaten Packages umgehen? Das wäre natürlich das Sahnehäubchen. Leider sind die beiden Properties nicht gescheit dokumentiert. Wenn ich die Online-Hilfe diesbezüglich richtig verstanden habe, kann man mit FetchRows nur festlegen, wieviele Zeilen bei einer Query generell zurückgegeben werden sollen. Ist dies korrekt, oder kann ich ODAC irgendwie überlisten und z.B. mehrmals die Query rausjagen, solange, bis alle Daten da sind?

Gruß und Danke nochmal

Stefan

[Robert_G]

Sorry ich habe mich vor 'ner Weile für DOA entschieden. Bei den CoreLabs habe auch ich dich nur auf die Doku verwiesen.
Nachtrag:
Das klingt fast danach: (Frag's Pferd, wo ich das mal gelesen habe. )

Zitat von Robert_G:

• Die CoreLabs-Kompos erlauben dir eine gewisse Cache-Größe zu bestimmen. (zum Bleistift soll er nur alle 1,000 Records die nächsten 1,000 holen)

Zitat von Robert_G:

• oder, du musst den Kampf da unten aufnehmen

Du könntest den "Kampf" mit dem Package und der SQL-Collection auch nutzen, um dich ein wenig mit Oracle's Object binding auseinanderzusetzen.

[Alfons_G]

Ich weiß jetzt auch nicht auswendig, welche Tricks es bei FetchRows gibt. Aber Du könntest mal bei CRLab, dem ODAC-Hersteller direkt nachfragen ([e-mail]). Ich hab' bei meiner letzten Frage innerhalb von ein paar Stunden eine kompetente Antwort bekommen .