6 Replies Latest reply: Jul 10, 2012 12:51 AM by 948373 RSS

    Excel query to Oracle Database

    Terry_Chen
      Dear all:

      I want to use Excel link to Oracle Database to query data, and I don't want to install the Oracle Client and through ODBC. Can some one tell me how to do?




      Regards
      Terry
        • 1. Re: Excel query to Oracle Database
          Satishbabu Gunukula
          Here you go for simple and easy steps Excel query to Oracle Database using ODBC

          http://www.databasejournal.com/features/oracle/article.php/3358411/Connecting-with-Oracle-Accessing-Oracle-via-Access-and-Excel.htm

          Regards
          http://www.oracleracexpert.com
          Overview of Transparent Application Failover in Oracle RAC
          http://www.oracleracexpert.com/2010/04/overview-of-transparent-application.html
          In function 'lcdprm':: Warning after patch in RAC
          http://www.oracleracexpert.com/2010/04/in-function-lcdprm-warning-gets.html
          Controlfile and Server parameter file AUTOBackup
          http://www.oracleracexpert.com/2010/05/controlfile-and-server-parameter-file.html
          • 2. Re: Excel query to Oracle Database
            Terry_Chen
            Dear Satishbabu:
            Thank you for your reply. And I don't want to use the ODBC to link between Excel and Oracle. Is there another method to do ? Thank you!




            Regards
            Terry
            • 3. Re: Excel query to Oracle Database
              Satishbabu Gunukula
              Yes, you can use OLE DB...

              http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/

              Hope this helps,

              Regards,
              http://www.oracleracexpert.com
              Install ASM lib in 10g
              http://www.oracleracexpert.com/2009/08/install-and-configure-asmlib-in-10g.html
              createdisk, deletedisk and querydisk in ASM
              http://www.oracleracexpert.com/2009/09/createdisk-deletedisk-and-querydisk-in.html
              • 4. Re: Excel query to Oracle Database
                778221
                Terry,

                I think I'm trying to do the same thing you are and that is not use ODBC/DSN. I think the solution is to use ADO. Here are some links that I found useful.
                [http://www.mrexcel.com/forum/showthread.php?t=441453&highlight=oracle+connection+string]
                [http://www.mrexcel.com/forum/showthread.php?t=427548&highlight=oracle+connection+string&page=2]
                [http://www.mrexcel.com/forum/showthread.php?t=459863&highlight=oracle+connection+string] [http://www.mrexcel.com/forum/showthread.php?t=458814&highlight=oracle+connection+string]
                [http://www.erlandsendata.no/english/index.php?d=envbadacconnstring]

                Here is some code I'm using:

                Sub Main()
                Dim Conn As New ADODB.Connection
                Dim RS As New ADODB.Recordset
                Dim Cmd As New ADODB.Command
                Dim sqlText As String
                Dim Row As Long
                Dim Findex As Long
                Dim Data As Worksheet
                Dim X As Long
                Dim UID As String
                Dim PWD As String
                Dim Server As String
                Application.Calculation = xlCalculationManual
                UID = "USERID" 'Enter the User ID
                PWD = "PASSWRD" 'Enter the password
                Server = "SERVER" 'This comes from your TNSNames.ora file
                Set Data = Sheets("Sheet1") 'Change this to the name of the sheet you want to return data to
                Data.Select
                Range("A:F").ClearContents 'Change A:F to the range you expect data to return to
                Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" & Server & ";" & "USER ID=" & UID & ";PASSWORD=" & PWD 'Note, I am using MSDAORA as I use an ORACLE DB, you will need to change it for what DB you are using
                Cmd.ActiveConnection = Conn
                Cmd.CommandType = adCmdText
                ' Put your query next
                sqlText = " select B.CSE_ID AS ORIGINAL_ID, M.CSE_ID AS REF_CASE_ID, M.PGM_ID AS REF_PGM_CODE, " & _
                " RPT.TEXT AS REF_PGM_DESC, PAT.PTNT_FNAME AS FIRST_NAME, PAT.PTNT_LNAME AS LAST_NAME, " & _
                " FROM OPTUSER.DD_BOEHEALTH2 b LEFT JOIN SUMM_CCS.MEMBER_REF M ON B.CSE_ID=M.CSE_ID "

                Cmd.CommandText = sqlText
                Set RS = Cmd.Execute
                For X = 0 To 17 'Change to the number of columns you are selecting MINUS 1. this loops through the column names
                ' in the query and puts them in the spreadsheet
                Data.Cells(1, X + 1) = RS.Fields(X).Name
                Next
                Do While Not RS.EOF 'this loops through the data and puts it in the spreadsheet
                Row = Row + 1
                For Findex = 0 To RS.Fields.Count - 1
                Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
                Next Findex
                RS.MoveNext
                Loop
                Application.Calculation = xlCalculationAutomatic
                Application.Calculate
                End Sub

                Hope that helps,

                DanD
                • 5. Re: Excel query to Oracle Database
                  Terry_Chen
                  Dear DanD:
                  Thank you for your sharing. It's very useful.




                  Regard
                  Terry
                  • 6. Re: Excel query to Oracle Database
                    948373
                    Hi,

                    When i try to run the above given code in excel, getting the below error on the line "Dim Conn As New ADO.Connection". Note - I try to connect without oracle client installed in my machine.

                    ---------------------------
                    Microsoft Visual Basic
                    ---------------------------
                    Compile error:

                    User-defined type not defined
                    ---------------------------
                    OK Help
                    ---------------------------


                    Please help me to rectify it.