"When you have a hammer, everything looks like a nail"
I'm trying to get out of my MS Access mentality and being new to Oracle I need some help.
I have a table (CLAIMS) that contains all of my claims in Oracle. It takes a long time to load all of the claims.
I have a table in Access that has the claims with groups 1, 2, 3 broken down.
I have created queries in Access to isolate the three groups: qry_Group1&2, qry_Group2&3, qry_Group3&1.
I have to eliminate group 2 & 3 from the CLAIMS table, then run an analysis program, then load them back & eliminate 1 & 3, run an analysis, etc.
I would like to bring over the queries or tables and do it all in Oracle, but I don't know how to:
1) bring the table from Access to Oracle (the other way is easy)
2) I would love to create a temp table in Oracle and load the other claims there, but I don't know how to load the other claims into a temp table once it's created.
If anyone has any ideas, I sure would appreciate hearing them.
The information i need to come across from Access is a table containing IDs and identifiers for which group the claim is in. It was in a spreadsheet and I imported it into an Access table.
I have TOAD.
Logically, I would think this would be the best way to accomplish this (unfortunately, I don't know how to do it):
1) get the table from Access into Oracle
2) create a TEMP table with only the structure of the CLAIMS table in Oracle
then (these I can handle in Oracle)
3) create query to dump records into TEMP table for groups 1 & 2
4) run analysis for group 3
5) truncate CLAIM
6) bring in records from TEMP for group 2
7) repeat 4-6 (for group 1)
Do you have permissions to create tables in the oracle environment?
If you can then proceed else SOL.
Toad I think has an import feature I have never used.
I user SQLLDR from a unix prompt.
I think that the same can be done for TOAD. In DBA version of software.
The table needs to be created and you import into this table.
You can link the oracle table into ACCESS and run a against the 2 different data sources this may be the easiest if the data resources are smaller.
(This is something that some people who are comfortable with access will do.) (I don't condone this action.)
I can tell you if it was me I would
Create my ORACLE table
Create a SQLLDR file.
Create a dat file (DATA in text format)
If you excel file is small and you dont wish to tackle SQLLDR at this time another work around is concat an insert statment in a excel cell and cut and paste into a sql window.
run the SQLLDR (either using TOAD or UNIX)
Then the data is in oracle and you can process.
I hope this helps. I don't know your level of permissions or your level of comfort with available tools.
I don't understand why you have to keep rebuilding your claims table. Have you ever used a where clause in your access queries? For example if you have a link to your oracle table and build a query in access that does the filtering. The where clause will be sent to oracle and ONLY the rows that you want will be read by access. You have NO need to have a local copy of your claims.
SELECT DISTINCTROW claims.col1, claims.col2, claims.col3
WHERE group = '1';
If CLAIMS is an external link to the oracle tables, the Oracle database will perform the filtering and return only the rows for group 1. This query could be used in access as if it was a table and could be referenced in a report or any place else.
If you need to move the data into a perminent oracle table, then have excel make a CSV file and import it using sql*loader.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
SQLLDR is probably the best bet for loading this data, but since you are
not as familiar with the processing, you might look at heterogenous services
You actually make your access database look like another Oracle database (at least to the existing Oracle Database).
The following at the steps to set this up. Keep in mind that this is done
on the server. Once setup is complete, access can be queried from SqlPlus
just like any Oracle table ... So, you getting data into Oracle would be ...
• Make sure a valid username and password are present for the non-Oracle database (MyUser/MyPassword).
• Create an ODBC DataSource for the non-Oracle database (Access1).
• Create a file called: ORACLE_HOME\hs\admin\initACCESS1.ora which contains:
• HS_FDS_CONNECT_INFO = Access1 #ODBC DSN
HS_FDS_TRACE_LEVEL = OFF
• Add the following entry to the tnsnames.ora file:
• ACCESS1.WORLD =
• (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521) )
• Add the following entry into the listener.ora file:
• Reload the listener: lsnrctl reload
• Create a database link using:
CREATE DATABASE LINK access1.world CONNECT TO "MyUser" IDENTIFIED BY "MyPassword" USING 'ACCESS1.WORLD';
• Query the table using:
• SELECT *
With MS Access the first query is slow due to the Access application being loaded. As expected, subsequent queries do not display a similar lag.