DBF to Oracle (sqlloader?)
Jay Weiland
This is my situation: I am looking to append two columns to a DBF
file with information from an Oracle database.
The psuedo-solutions I can see are:
(1) Using a third-party product like MS Access and make calls through
ODBC and export a new DBF file
(2) Using SQL*LOADER and/or UTL_FILE to directly manipulate the DBF
file.
(3) Make an ODBC data source to the DBF file (but I am not sure about
updating the DBF file)
Does anyone have any experience or ideas on this one?
I have seen references to being able to directly load .DBF files
into Oracle via SQL*Loader, and I am assuming the statement would look
something like:
LOAD DATA
INFILE "TEST_IN.DBF"
APPEND
INTO TABLE dbf_test
(emp_id POSITION(1:10),
emp_name POSITION(12:32))
Jay!!!
Jay Weiland
>
> This is my situation: I am looking to append two columns to a DBF
> file with information from an Oracle database.
More specifically, I am looking at starting with a table that appears as
(emp_id CHAR(10),
emp_name CHAR(50))
...and ending with a table like:
(emp_id CHAR(10),
emp_name CHAR(50),
ssn CHAR(10),
hire_date DATE)
I am even willing to update an existing DBF file with all of the end
fields (i.e. emp_id, emp_name, ssn, and hire_date).
Jay!!!
Mike Rose
suggest the following based on lots of prior experiences. From my
understanding of what you are attempting to do, try this:
1) Attach the Oracle and dBase/Clipper tables in Access with ODBC.
2a) Create the target *.dbf
Use an Append Query to insert records into the *.dbf from the
columns selected from the 2 Oracle tables
Or
2b) Use a Make Table Query to create the *.dbf from the columns selected
from the 2 Oracle tables
You didn't mention how you will match the records from the 2 Oracle tables
to form 1 new *.dbf record. I hope you have some key field(s) common to both
tables that can be used.
Mike Rose
Jay Weiland wrote in message <36D72E1A...@pixar.com>...
Jay Weiland
I realize that my question was not presented as clearly
as it could have been. Let me rephrase and try again:
1) I have a DBF file, EMP_RECS.DBF, with the following columns:
(emp_id CHAR(5),
emp_name CHAR(50),
ssn CHAR(9),
hire_date DATE)
...where the hire_date has not been populated.
Sample Record of the EMP_RECS.DBF file:
EMP_ID: 07419
EMP_NAME: Weiland, Jay
SSN: 626-00-1234
HIRE_DATE: <null>
2) I have an Oracle table with the following columns:
(emp_id CHAR(5),
hire_date DATE);
Sample Record in Oracle Table, HIRE_DATES:
EMP_ID: 07419
HIRE_DATE: 21-FEB-99
3) The emp_id is a common field between the two databases
(i.e. a primary key)
4) Desired Result of EMP_RECS.DBF file:
EMP_ID: 07419
EMP_NAME: Weiland, Jay
SSN: 626-00-1234
HIRE_DATE: 21-FEB-99
Question: How would someone update the HIRE_DATE column
of the DBF file using SQL*Plus?
Would the SQL*Plus command look something like the following?:
update @ODBC:EMP_RECS
set HIRE_DATE = (select HIRE_DATE
from HIRE_DATES
where @ODBC:EMP_RECS.EMP_ID = EMP_ID);
Or is there another method that should be explored?
...any help would be appreciated.
Jay!!!
Mike Rose
try the Microsoft Data Access Controls (MDAC), the MDAC 2.0 Collection
contains ADO, and ODBC drivers for Xbase and Oracle. I would strongly urge
you to use the Microsoft Oracle ODBC driver instead of any of the recent
Oracle ODBC drivers. The Microsoft Oracle ODBC driver should work with 7.x
and 8.x Oracle databases.
>1) I have a DBF file, EMP_RECS.DBF, with the following columns:
>
> (emp_id CHAR(5),
> emp_name CHAR(50),
> ssn CHAR(9),
> hire_date DATE)
>
> ...where the hire_date has not been populated.
1) Attach this dBase/Clipper table in Access with an ODBC Driver.
Use these Access menu selections
File/Get External Data/Link Tables/files of type: ODBC Databases ()
>2) I have an Oracle table with the following columns:
>
> (emp_id CHAR(5),
> hire_date DATE);
2) Attach this Oracle table in Access with an ODBC Driver.
Use these Access menu selections
File/Get External Data/Link Tables/files of type: ODBC Databases ()
I've left you to use the Access on-line, context-sensitve Help to create an
Update Query in Access that has both of the Attached Tables in it. You can
even use the mouse to drag the emp_id from one table to the other and create
the join condition. Creation of Data Set Names (DSNs) in ODBC and
determining what your Oracle SQL*Net Alias is so you can use it when
creating the Oracle DSN are execises ...
Mike Rose
>3) The emp_id is a common field between the two databases
> (i.e. a primary key)
>
>Question: How would someone update the HIRE_DATE column
> of the DBF file using SQL*Plus?
You can't so go ahead and use Access with ODBC.
>
>update @ODBC:EMP_RECS
> set HIRE_DATE = (select HIRE_DATE
> from HIRE_DATES
> where @ODBC:EMP_RECS.EMP_ID = EMP_ID);
>
You are fairly close on your proposed SQL statement -- however ODBC was
designed to lessen the negative aspects of SQL dialects hence the ANSI-92
Oracle SQL would look different from the Access SQL.