Querying Microsoft Office Access 2007 data from an Oracle 11g Database Part 3 A
Stage 3: Selecting LONG data type columns over a database link.
In part 1 of this blog, I demonstrated the initial configuration of the Oracle Database Gateway for ODBC (DG4ODBC).
Part 2 completed the configuration and also showed how to run some basic queries. Also at the end of part 2, I
gave examples of various issues that you can encounter if the tables in the Microsoft Office Access 2007 database contain
one or more LONG data type columns.
Part 3 will now show a method of resolving these issues, using a combination of external tables and materialized views.
In theory, you could just amend the data type in the Microsoft Office Access 2007 database to be something other than a LONG, but rather than risk any data loss or application issues with the Micrsoft Office Access database, we’ll assume that this is not an option when working through these notes.
STEP K: Create a directory
——-
40) Create a new directory in windows explorer:
C:\app\Administrator\admin\ORCL11\nwind_dump
Note: ORCL11 is the name of my test database. Replace this with the name of your own database, whenever you see it in these notes.
41) Then login to the Oracle database as the SYSTEM user and create the following directory. Also grant read and write access to the NWIND_ACCESS user, which we set up previously as the user that connects to the Northwind database from within Oracle.
create or replace directory NWIND_DIR as ‘C:\app\Administrator\admin\ORCL11\nwind_dump’;
grant read, write on directory nwind_dir to nwind_access;
STEP L: Extract the contents of a northwind table out to a text file.
——
42) connect to the Oracle database as the nwind_access user and then run the commands below.
conn nwind_access/zuggy4
set head off feedback off trimspool on lines 32766 buffer 32767 pages 0 space 0 colsep ‘~’
alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
spool C:\app\Administrator\admin\ORCL11\nwind_dump\customers.txt
select * from “Customers”@Northwind;
spool off
You should now have created a file called C:\app\Administrator\admin\ORCL11\nwind_dump\customers.txt, which contains the data from the customers table.
Note: You may need to decrease 32766 if your screen buffer size will not support this value.
I’ve used a tilde (’~') instead of the normal space as the character that will separate each column. This is to allow for the fact that some of the data may contain commas.
STEP M: Create an external table based on the contents of the newly-created text file.
——-
43) Create the external table as the nwind_access user in the Oracle database:
create table nwind_customers
(ID NUMBER(10),
COMPANY VARCHAR2(100),
LAST_NAME VARCHAR2(100),
FIRST_NAME VARCHAR2(100),
EMAIL_ADDRESS VARCHAR2(100),
JOB_TITLE VARCHAR2(100),
BUSINESS_PHONE VARCHAR2(50),
HOME_PHONE VARCHAR2(50),
MOBILE_PHONE VARCHAR2(50),
FAX_NUMBER VARCHAR2(50),
ADDRESS CLOB,
CITY VARCHAR2(100),
STATE_PROVINCE VARCHAR2(100),
ZIP_POSTAL_CODE VARCHAR2(30),
COUNTRY_REGION VARCHAR2(100),
WEB_PAGE CLOB,
NOTES CLOB,
ATTACHMENTS CLOB
)
–Note: in the above LONG converted to CLOB
organization external
(type oracle_loader
default directory nwind_dir
access parameters
(
records delimited by newline
load when (COMPANY != “off”)
badfile NWIND_DIR:’customers.bad’
discardfile NWIND_DIR:’customers.dsc’
logfile NWIND_DIR:’customers.log’
skip 1
fields terminated by ‘~’ optionally enclosed by ‘”‘ LRTRIM
missing field values are null
reject rows with all null fields
(
ID,
COMPANY,
LAST_NAME,
FIRST_NAME,
EMAIL_ADDRESS,
JOB_TITLE,
BUSINESS_PHONE,
HOME_PHONE,
MOBILE_PHONE,
FAX_NUMBER,
ADDRESS,
CITY,
STATE_PROVINCE,
ZIP_POSTAL_CODE,
COUNTRY_REGION,
WEB_PAGE,
NOTES,
ATTACHMENTS
)
)
location (NWIND_DIR:’customers.txt’)
)
reject limit unlimited
/
Check the files C:\app\Administrator\admin\ORCL11\nwind_dump\customers.bad (rejected records), customers.dsc (records discarded due to any exclusions you specify) and customers.log (the main log file) for any issues.
Please see part 3 B tomorrow