+ Reply to Thread
Results 1 to 2 of 2

Retrive Records From Oracle DB

  1. #1
    Registered User
    Join Date
    02-21-2017
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    6

    Retrive Records From Oracle DB

    Hi All,

    I have values in excel column A as below. My requirement is I want to search for below value connecting to Oracle DB and retrieve its value
    KW_VALUE
    510000226342
    510000226332
    510000226330
    510000226327
    510000226315
    510000226312
    510000226285
    510000226256
    510000226181

    I'm able to connect to Oracle DB, but how to retrieve the date
    Ex: TableABC has below columns
    KW_VALUE, DATE, COUNTRY, PHONE

    Macro has to check A column value in TableABC if exists then retrieve its info an print in Excel as below, if record does not exists then print No Record
    KW_VALUE DATE COUNTRY ID Value
    510000226342 14-10-17 UK 12345
    510000226332 13-10-17 USA 5456
    510000226330 17-10-17 RU 5465465
    510000226327 01-11-17 SI 897654
    510000226315 07-10-17 SI 6561
    510000226312 15-10-17 UK 65484
    510000226285 16-10-17 RU 84521
    510000226256 No Record No Record No Record
    510000226181 16-10-17 PO 21318

    TIA

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Retrive Records From Oracle DB

    Since you have Excel 2016, there are number of options available to you.

    1. ADODB using VBA
    2. PowerQuery (I believe you'd need ProPlus license for Oracle Import).
    3. MS Query.

    Exact method will depend on what sort of permission you have on the database etc.

    Using MS Query, you can load data from Excel (list of KW_Value) then also bring data from Oracle, left join Oracle data onto KW_Value. Row with no record will show null.
    You can then replace null with your desired string.

    But my preferred method is to use PQ by passing on KW_Value as variable(s) in SQL statement (using IN Clause), then doing left join using subset only.

    Simple method using ADODB is to pass each value in column A as variable in query and check if record set returns empty or not. Or you can bring in Oracle data, and then loop through recordset to check.
    Which method is more efficient will depend on size of your db table.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Fetch data from Oracle to Excel with Oracle ODBC driver using VBA
    By clam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2022, 11:39 AM
  2. [SOLVED] Query Oracle using range in Excel to return individual records for each cell in range
    By bigwillydier in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-18-2013, 06:37 PM
  3. Excel to Oracle connection using VBA for various oracle versions
    By pam79 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2012, 02:39 AM
  4. Retrive only first name.
    By Wskip49 in forum Excel General
    Replies: 3
    Last Post: 08-27-2008, 11:23 AM
  5. Retrive Data
    By Tax User in forum Excel General
    Replies: 4
    Last Post: 10-28-2007, 11:32 PM
  6. hOW to retrive the Data after did UPPERCASE
    By shobsrangan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-01-2006, 07:10 AM
  7. [SOLVED] Connect to Oracle using Microsoft ODBC for Oracle
    By Kent in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2006, 11:55 PM
  8. [SOLVED] HOW TO RETRIVE LAST NAME USING EXCEL, IT SHOULD BE GENERIC
    By zomex in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-29-2005, 11:40 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1