Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Excludes From access

    "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.

    Thanks...

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    If all of your claims are in ORACLE what information do you need from access.

    What do you ultimatly wish to accomplish?

    What do these qry_Group1&2, qry_Group2&3, qry_Group3&1 do?

    The loading of this temp table where are those claims from?

    You can load external data several ways you can load it from Access through ODBC connection (very slow not what I would do.)

    You can use SQLLDR what interface tool to oracle do you have access to.

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    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)



    Thanks...

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    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.

    Soory I cannot give you a definite answer.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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
    FROM claims
    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unhappy

    The CLAIM table is in oracle and the analysis app is a script (?) within oracle. Being new to oracle, I don't want to mess with the script.

    So, I need to get the data into Oracle.
    I can get the data into access form Oracle easy - use ODBC, copy table, VOILA!

    But How do I do the reverse?
    Seems there's go to be an easier way in either TOAD or native oracle.

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    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 ...

    sql> insert into mytablename select * from othertablename@access1.world;


    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 =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521) )
    (CONNECT_DATA=(SID=ACCESS1))
    (HS=OK)
    )
    Add the following entry into the listener.ora file:
    (SID_DESC=
    (SID_NAME=ACCESS1)
    (ORACLE_HOME=D:\Oracle\Ora9011)
    (PROGRAM=hsodbc)
    )
    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 *
    FROM mytable@ACCESS1.WORLD;
    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.

    HTH
    Gregg

  8. #8
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Fantastic!
    Just what I was looking for.
    I guess it was a bit more complex to set it up than I imagined.
    Thanks Gregg

    Jerry

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    glad it worked out

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •