append dbf files to oracle table

{
usage:
Dbase2Oracle params.txt

params.txt
dbase_alias=db1
query_dbase.sql=dbase.sql
oracle_alias=sample_tns_alias
oracle_username=username
oracle_password=password
query_oracle.sql=oracle.sql
rowspercommit=10000
}
program DBase2Oracle;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DBTables,
  Classes;

const
  MaxParam = 4;
  ParamDefaults: array[1..MaxParam, 1..2] of string =
    (('dbase_alias', 'db_dbase'), ('oracle_alias', 'db_oracle'),
     ('query_dbase.sql', 'query_dbase.sql'), ('query_oracle.sql', 'query_oracle.sql')
     );
  DefaultRowsPerCommit = 10000;

var ParamList: TStrings;
function Param(ParamName: string): string;
var
  paramFileName: string;
  I: Integer;
begin
  Result := '';
  if not assigned(ParamList) then
  begin
    ParamList := TStringList.Create;
    ParamFileName := 'params.txt';
    if ParamStr(1) <> '' then
      ParamFileName := ParamStr(1);
    if FileExists(ParamFileName) then
      ParamList.LoadFromFile(ParamFileName);
  end;
  if Assigned(ParamList) then
  begin
    Result := ParamList.Values[ParamName];
    if Result = '' then
      for I := 1 to MaxParam do
        if SameText(ParamDefaults[I, 1], ParamName) then
          Result := ParamDefaults[I, 2];
  end;
end;

var
  dbd, dbo: TDatabase;
  qryd, qryo: TQuery;
  I, rec: Integer;
  csv,errors: Text;

begin
  try
    dbd := TDatabase.Create(nil);
    try
      dbd.AliasName := param('dbase_alias');
      dbd.DatabaseName := 'dbase_source';
      dbd.LoginPrompt := False;
      dbd.Connected := True;

      if dbd.Connected then
      begin

        dbo := TDatabase.Create(nil);
        try
          dbo.LoginPrompt := False;
          dbo.AliasName := param('oracle_alias');
          dbo.Params.Values['USERNAME'] := param('oracle_username');
          dbo.Params.Values['PASSWORD'] := param('oracle_password');
          dbo.DatabaseName := 'oracle_source';
          dbo.Connected := True;

          if dbo.Connected then
          begin
            qryd := TQuery.Create(nil);
            try
              qryd.DatabaseName := dbd.DatabaseName;;
              qryd.SQL.LoadFromFile(param('query_dbase.sql'));
              qryd.Open;
              if not qryd.IsEmpty then
              begin
                { Writing to CSV }
                if not FileExists(param('query_oracle.sql')) then
                begin
                  Assign(csv, 'csv.txt');
                  Rewrite(csv);
                  try
                    for i := 0 to qryd.Fields.Count-1 do
                    begin
                      Write(csv, QuotedStr(qryd.fields.Fields[I].FieldName));
                      if i < qryd.Fields.Count-1 then
                        Write(csv, ',');
                    end;
                    WriteLn(Csv, '');
                    while not qryd.Eof do
                    begin
                      for i := 0 to qryd.Fields.Count-1 do
                      begin
                        Write(csv, QuotedStr(qryd.fields[I].AsString));
                        if i < qryd.Fields.Count-1 then
                          Write(csv, ',');
                      end;
                      WriteLn(csv, '');
                      qryd.Next;
                    end;
                  finally
                    CloseFile(csv);
                  end;
                end
                  else
                begin
                  {Writing to oracle }
                  rec := 0;
                  qryo := TQuery.Create(nil);
                  try
                    qryo.DatabaseName := dbo.DatabaseName;
                    qryo.SQL.LoadFromFile(param('query_oracle.sql'));
                    qryo.Prepare;
                    while not qryd.Eof do
                    begin
                      for i := 0 to qryo.Params.Count-1 do
                        qryo.Params.Items[I].Value := qryd.FieldByName(qryo.Params.Items[I].Name).Value;
                      qryo.ExecSQL;
                      inc(rec);
                      if rec mod RowsPerCommit = 0 then
                        dbo.Commit;
                      qryd.Next;
                    end;
                    dbo.Commit;
                  finally
                    qryo.Free;
                  end;
                end;
              end;
              qryd.Close;
            finally
              qryd.Free;
            end;
          end;

        finally
          dbo.Free;
        end;
      end;
    finally
      dbd.Free;
    end;
  except
    on E: Exception do
    begin
      Assign(errors, 'errors.txt');
      ReWrite(errors);
      Writeln(errors, E.ClassName, ': ', E.Message);
      CloseFile(errors);
    end;

  end;
end.

Select all Open in new window

select last_name, first_name, acct_nbr, address_1, city, state, birth_date from clients.dbf

Select all Open in new window

insert into clients (last_name, first_name, acct_nbr, address_1, city, state, birth_date)
values (:last_name, :first_name, :acct_nbr, :address_1, :city, :state, :birth_date)

Select all Open in new window