Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Question Unanswered: Querying Oracle db from Excel

    Hi All
    I have a business problem that I've been solving via PL/SQL scripts generated by Excel VBA. (It's validating requests for new items from engineers, but I suspect that that is immaterial to the problem. The database being queried is the one that sits behind Oracle's E-Business Suite, just in case that **does** make a difference.) While I'm quite happy to open the scripts in SQL Tools, I'm trying to simplify the process for some colleagues. To that end, I want to do the following:
    • Generate a valid PL/SQL statement
    • Pass the statement and other data to a function that connects to Oracle
    • Display the dataset in a worksheet (if there is one)

    The first two are easy - my function takes three arguments (SQL statement, name of the destination worksheet and a variable that determines the server and database to interrogate), and forms a connection to the Oracle server. The code for the function was derived from two posts on Stack Overflow - here and here. This is where it gets fuzzy.
    I can generate valid PL/SQL in VBA, and I have tried running it in SQL Tools (it returns the results that I expect). When my function runs, it returns an empty recordset. It picks up the column names, but no records, and the recordset's recordcount value is -1.
    My code tends to the verbose (at least in the first instance), so I've attached text files. ValidateAllocations.txt contains the subroutine that generates the SQL statement; ConnectToOracle.txt contains the function code, along with the global constants and enumerations that are used. Some information redacted so that I don't get fired.
    If anyone's experienced this, I'd be grateful for words of advice! I've probably forgotten to mention a load of useful background information - please feel free to ask for more.
    Attached Files Attached Files
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    weejas,

    As for the recordset's recordcount, put this after your line rstOra.Open:

    rstOra.MoveLast
    debug.print rstOra.RecordCount
    rstOra.MoveFirst

    If that doesn't give you the expected value, look at the value for UBound(varData).
    Perhaps the data is in the Recordset, but isn't getting to varData.

    If the previous 2 items look OK, then your TransPose statement has a flaw.

    If you are only getting the headings, one of the three above is bad.

    I have no idea what the VBA code in ValidateAllocations does.
    I'll wait to see if the first module works.

    hth,
    Wayne

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hi WayCal

    Thanks for the tips. I've added your suggested snippet (after making the recordset dynamic), and it still gives me a record count of -1.

    The code in ValidateAllocations simply generates a SELECT statement and passes it to ConnectToOracle along with the name of the destination sheet and the Oracle environment to interrogate. I can post the SQL statement if it helps.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    On a hunch, I tried stepping through the recordset the old-school way. All of a sudden, I have data! Looks like the problem was within VBA rather than Oracle.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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