Querying Microsoft Office Access 2007 data from an Oracle 11g Database Part 4 A
Stage 4: Automating Data Refreshes.
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.
Part 3 showed a method of resolving issues encountered when dealing with LONG data type columns, using a combination of external tables and materialized views.
In the fourth and final part, I’ll demonstrate one method of automating the data refreshes for this solution. There are many solutions available to automate procesess, but for this example, I’ll use the database scheduler.
Step L: Check the objects we have created previously
——-
58) Run the query below to confirm the list of objects owned by NWIND_ACCESS, that were created in parts 1,2 and 3 of this blog:
conn nwind_access/zuggy4
select table_name from user_tables union select view_name from user_views;
The information below shows how the tables and views map to the Microsoft Office Access 2007 database tables from the northwind database.
ORCL11 Object ORCL11 objects referenced Microsoft Office Access tables
========== ====================== ====================
MV_NWIND_CUSTOMERS (materialized view) NWIND_CUSTOMERS(external table) Customers
MV_NWIND_ORDERS (materialized view) NWIND_ORDERS (external table) Orders
V_ORDER_DETAILS(View) Order Details
V_ORDER_STATUS(View) Order Status
T_ORDER_DETAILS(Table) Order Details
T_ORDER_STATUS(Table) Orders Status
Step M: Create Additional Materialized Views and re-create existing materialized views
——-
59) We created materialized views based on the Customers table and the Orders table from the northwind database. We now also need to create them for information stored within the Order Details and Orders Status tables.
As demonstrated previously, we aren’t able to base these on selects directly from the table. (If we try to create the materialized view as a “select * from v_order_details” or “select * from “Order Details”@Northwind” then you just see the error: ORA-00942: table or view does not exist)
60) Create the text files that the external tables will be based on.
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\order_details.txt
select * from “Order Details”@Northwind;
spool off
spool
C:\app\Administrator\admin\ORCL11\nwind_dump\order_status.txt
select * from “Orders Status”@Northwind;
spool off
Then re-set your SQLPLUS* environment:
set head on feedback on trimspool off lines 132 buffer 132 pages 1000 space 1 colsep ” “
Note: Throughout all of this work, ensure that the permissions of the directory which contains the output text files (i.e. the nwind_dir database directory location) is restricted according to your company’s security policies.
61) Create the new external tables nwind_order_details and nwind_order_status and re-create the existing external tables:
conn nwind_access/zuggy4
create table nwind_order_details
(
ID NUMBER(10),
ORDER_ID NUMBER(10),
PRODUCT_ID NUMBER(10),
QUANTITY NUMBER(18,4),
UNIT_PRICE NUMBER(19,4),
DISCOUNT FLOAT(53),
STATUS_ID NUMBER(10),
DATE_ALLOCATED DATE,
PURCHASE_ORDER_ID NUMBER(10),
INVENTORY_ID NUMBER(10)
)
organization external
(type oracle_loader
default directory nwind_dir
access parameters
(
records delimited by newline
badfile NWIND_DIR:’order_details.bad’
discardfile NWIND_DIR:’order_details.dsc’
logfile NWIND_DIR:’order_details.log’
fields terminated by ‘~’ optionally enclosed by ‘”‘ LRTRIM
missing field values are null
reject rows with all null fields
(
ID,
ORDER_ID,
PRODUCT_ID,
QUANTITY,
UNIT_PRICE,
DISCOUNT,
STATUS_ID,
DATE_ALLOCATED CHAR(20) date_format DATE mask “dd-mon-yyyy hh24:mi:ss”,
PURCHASE_ORDER_ID,
INVENTORY_ID
)
)
location (NWIND_DIR:’order_details.txt’)
)
reject limit unlimited
/
create table nwind_order_status
(
STATUS_ID NUMBER(3),
STATUS_NAME VARCHAR2(100)
)
organization external
(type oracle_loader
default directory nwind_dir
access parameters
(
records delimited by newline
badfile NWIND_DIR:’order_status.bad’
discardfile NWIND_DIR:’order_status.dsc’
logfile NWIND_DIR:’order_status.log’
fields terminated by ‘~’ optionally enclosed by ‘”‘ LRTRIM
missing field values are null
reject rows with all null fields
(
STATUS_ID,
STATUS_NAME
)
)
location (NWIND_DIR:’order_status.txt’)
)
reject limit unlimited
/
Note: In the above, you’ll notice that we’ve left out the ” skip 1” clause that was included in the create table statements in earlier parts of this blog. As commands will not be echoed to the spool file, as we are using the database scheduler, we need to remove this clause, otherwise we’ll lose the first row of data. Because of this issue, we’ll also need to drop and re-create the existing Customers and Order external tables also. (We don’t need to re-create the materialized views that were based on these tables). We also don’t need to include the ” load when ( != “off”) ” commands in the access parameters section, as the SQL commands will not appear in the output file.