how do i read Visual foxpro Database as External table in oracle 10g database?

Question

I have couple of vfp 9.0 .dbc and .dbf files i am trying to setup these tables as external tables, but from information i googled it seems like that oracle only support .CSV files

is that even possible

I will be selecting,updating,deleting,inserting a few rows in the dbf files.

for example

 create or replace directory ext_tab as 'C:\EXT_TAB';
 CREATE TABLE ext_test( 
    CODE NUMBER(4), DESC CHAR(20)
    ORGANIZATION EXTERNAL( 
    TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab
    ACCESS PARAMETERS
    ( 
    RECORDS DELIMITED BY NEWLINE 
    NOBADFILE
    NOLOGFILE
    SKIP 1    
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  LRTRIM  
    MISSING FIELD VALUES ARE NULL  
    REJECT ROWS WITH ALL NULL FIELDS 
    (CODE INTEGER EXTERNAL (4),
    DESC CHAR(20)) 
    ) 
    LOCATION ('ext_tab_test.dbf')
    )
    PARALLEL 
    REJECT LIMIT 0;

any correction, guidance, article is much appreciated.

score 0 · Answer 1 · answered Mar 07 '14 at 09:42

I don't know about the physical layout of a visual fox pro database file but I have no doubts that it has the ability to use odbc connections. If that is the case you can use dg4odbc to create a connection from oracle to that database and use the tables in the way you want.

Having the fox pro files as external tables is not going to help you.

dg4odbc is part of the regular oracle installation. It comes down to finding a odbc driver, install that, create a odbc.ini with the details for your fox pro database. Next create a listener entry in your oracle listener with dg4odbc as program. Next create a tnsnames.ora alias that points to the listener entry that has (HS=ok) in the description. In $ORACLE_HOME/hs/admin you create a init{ODBC_NAME}.ora that is used by dg4odbc to tie the pieces together.

If all that is in place you can create a database link from oracle to your fox pro database using the tnsalias created above.

score 0 · Answer 2 · answered Mar 10 '14 at 18:36

I used to use Visual Foxpro 9 to query an Oracle 10g database in one of our applications.There are a couple ways to do this:

Visual Foxpro SQL pass through

or

Visual Foxpro Remote Views

Since you are already using a Visual Foxpro Database container (.DBC), you might want to look at the Remote View method. I've used both methods. They both work well.

how do i read Visual foxpro Database as External table in oracle 10g database?

2 Answers2