append dbf files to oracle table

i have to every month append data for my existing table from dbase server ( dbf format )
here i'm using to import it first to ms excell , then from access to oracle
any procedure to use the dbf file name as a parameter , search for it in a specific folder , then upload it to my table
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
If everything is in windows you could try using odbc drivers for dbase and then opening the dbf file as external tables with the heterogeneous gateway.


http://www.experts-exchange.com/articles/9850/Configuring-and-using-Oracle-Database-Gateway-for-ODBC.html
DavidSenior Oracle Database AdministratorCommented:
Also, you imply you're concerned with a single table. I'd skip the Access step and use SQLLoader to read the Excel CSV file.
NiceMan331Author Commented:
sdstubar
it is too dificult for me
dvz
use SQLLoader to read the Excel CSV file.
what is the steps
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
>>>> it is too dificult for me

which step did you get stuck on?
NiceMan331Author Commented:
Sorry for my bad expression , but I just read the article , I found that some database admin tasks such as database link and drivers which I don't have privilege to create it in addition to adding values to the registery where our erp is strict for a specific values in
Geert GOracle dbaCommented:
technically you need to change the point where the data is inserted
don't insert it in the dbase, insert it in the oracle directly

if you describe the dbase tables and the oracle destination tables
it should be easy enough to come up with an etl program
do run the conversion

you'll need to identify what records need to be uploaded
> probably easiest in the oracle
then load that data from dbase
insert it in oracle
mark finished

dbase is almost ancient technology ... it might be more future proof to get rid of it
NiceMan331Author Commented:
dbase is almost ancient technology
yes i know , but it is the policy of the company , i have nothing to do with

don't insert it in the dbase, insert it in the oracle directly
i'm not inserting into dbase , it is job of other department they do as per the instruction of the management
you'll need to identify what records need to be uploaded
actually the programm every month it created a file named by month name
for example sep-15
and here i want to upload all its records to my oracle table

then load that data from dbase
insert it in oracle
mark finished
this is my question , how ?
Geert GOracle dbaCommented:
i was thinking of building you an app ...
the data is in the dbase and you want it copied (or moved ?) to the oracle

i'll conjure up something and come back with it
Geert GOracle dbaCommented:
i'd appreciate a dbf file with a few records in it

the descr of the oracle table
i suppose you'll map the column names to the same column names ?

what i was thinking off ...
on oracle:
select max(id) last_id from ora_table
> this gives me the last record inserted in the oracle

select * from dbase_table
where id > :last_id

insert into ora_table (id, col1, col2, col3)
values (:id, :col1, :col2, :col3);

technically you only need these 3 queries per table do the copy from dbase 2 oracle
NiceMan331Author Commented:
actually i don't require the first 2 queries , because , as i explained before , the dbase file created every month with new name "month name"
so , i not care about the first & last id , i want to insert the whole records from this file
named "sep-15"
next month i will copied all records from "oct-15" to my table , and so on
and still now i don't know how to do the last query
NiceMan331Author Commented:
i already created the table in oracle , and append the table of ms access into it
now i can append from toad from dbase into oracle , but still one issue
my table in oracle has a field "period = date format " to have the value of date for this transactions
how i can insert this value together while appending the next file
if there is no solution to make the append by script ,
Geert GOracle dbaCommented:
took some time ... darn bde wasn't installed on my test pc

use the attached Dbase2Oracle.Exe to upload the data from dbase to oracle
if the oracle.sql file doesn't exist, it will write a csv.txt file with the dbase data

the code in Delphi:
{
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.

Open in new window


sample dbase query:
select last_name, first_name, acct_nbr, address_1, city, state, birth_date from clients.dbf

Open in new window


oracle query:
the parameter names must match the dbase column names
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)

Open in new window


ugh ... i can't attach the exe
you can get it here : http://www.veerle-en-geert.be/scripts/DBase2Oracle.exe

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
hi
i'm so sorry for the delay
but the reason i waited to the new dbase file been created
then , the file you mensioned above is not working ( not able to install)
when i click it to install it shutted down
Geert GOracle dbaCommented:
install ?
it's an exe ... you just run it on windows
it doesn't require an installation

you need to provide a params.txt with options as specified below:

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
NiceMan331Author Commented:
Yes , when I'm clicking it it just open dos Windows then close immediately , I'm using Windows 7
NiceMan331Author Commented:
But any how I don't need third party , I need a code to do it within oracle , because it should search for my parameter ( file name ) then append it in addition to extra variables
slightwv (䄆 Netminder) Commented:
>>Yes , when I'm clicking it it just open dos Windows then close immediately , I'm using Windows 7

Open a CMD prompt and run it from there.  It should be able to provide more information about why it isn't running.

>>But any how I don't need third party , I need a code to do it within oracle

Oracle doesn't know how to read a DBase file natively.  You will need to write the code yourself, buy it, or find ways to do it on the web.

Geert Gruwez was nice enough to write some Delphi code for you.  I would probably continue with him.
Geert GOracle dbaCommented:
the program requires the BDE to be installed
and for you to have setup an alias to dbase and an alias to oracle
provide the parameters for those items in the params.txt

then run the program in cmd
you don't need anything else, it copies the data from dbase to oracle

since you are using software which is 2 decades old ... the BDE ... i assumed you know how to use DOS
NiceMan331Author Commented:
Sorry again , what is : BDE ? And how to create the aliases you refer to ? Then what should I do with Delphi code ? Do I need to create text file name it : param ?
slightwv (䄆 Netminder) Commented:
>>Sorry again , what is : BDE ?

I must admit, I had no idea.  So I Googled:  Delphi BDE

Found this:
https://en.wikipedia.org/wiki/Borland_Database_Engine

>>And how to create the aliases you refer to ?

Those are the parameters:  dbase_alias and oracle_alias

>>Do I need to create text file name it : param ?

I believe the direction was:
you need to provide a params.txt with options as specified below:

Name the file params.txt and provide it as a command line argument.

Look at the raw source code provided:  usage: Dbase2Oracle params.txt

I would also expect it to display that message if you ran the .exe from a CMD prompt.

But those are all just guesses since I don't know Delphi...
NiceMan331Author Commented:
sorry , i didnt know how to use that program also
i found in Toad can use what i need
thanx to all
Geert GOracle dbaCommented:
I gave all the possible help I could imagine
Toad ... for dbase ????
NiceMan331Author Commented:
yes geert i'm really thanking you
but i didn't understand how to do with your program
Geert GOracle dbaCommented:
you working with ancient technology ... dbase
it's not from the time the dinosaurs walked on this planet, but a little later
it's from the time a 56k modem was used to logon to internet providers

it's really time to invest in upgrading that ancient technology

what i see is that you are giving very limited information
what version is the dbase database ?
NiceMan331Author Commented:
Geert , I'm not the owner of the business , I'm just an employee , and I'm trying to immigrate the data at least to oracle because I'm not the design maker to decide to upgrade the system
Geert GOracle dbaCommented:
i guessed as much

what happens when you indicate to the business it's getting impossible to get it right ?
reason > software technology which doesn't meet current business requirements anymore

this should be an indication to the business that this item requires a lot more attention than they expected.

you can keep wading through the mud
... or replace the mud with a solid floor
NiceMan331Author Commented:
anyhow , this is the case now and it is an issue for me
because i'm concerning to analyse the data , i used to immagrate it to oracle for analyses purposes
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.