This discussion is archived
4 Replies Latest reply: Oct 16, 2012 8:24 AM by 722846 RSS

How to create a DSN for Oracle Provider for OLE DB in a web server

722846 Newbie
Currently Being Moderated
Dear Guys,

I am a Excel VBA developer.

My requirement is from the Excel I have to call a Stored Procedure with REF CURSOR.

Normally I am using Microsoft ODBC for Oracle Driver for connecting Oracle DB, which is in the Server .
We have users using the Excel reports across the globe.
Sending Excel report is enough, the clients can connect DB from Excel via the DSN created in a web server.

But, I came to know that we can't access the REF CURSOR using Microsoft ODBC for Oracle Driver and it is possible to access by using ORA OLE DB Provider.

I have installed Oracle Client in my machine and tried using ORA OLE DB Provider like below
+con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=user_name;" & _+
+"Password=pwd;Data Source=Oracle;"+

The Excel worked fine in my machine but when I run the same Excel in my user machine in a different country I couldn't connect to DB.
Because the user machine doesn't have Oracle Client installed. We have n number of users across the world and we can't install Oracle client individually.

So, I have the plan of creating a DSN in a web sever as I used for Microsoft ODBC for Oracle Driver.

But, my doubt is how can I create a DSN for accessing ORA OLE DB provider? Is there any driver for ORA OLE DB provider? or is there any alternate solution for my issue?

Can anybody help me on this ASAP?

Thanks & Regards,
Satz
  • 1. Re: How to create a DSN for Oracle Provider for OLE DB in a web server
    Tridus Journeyer
    Currently Being Moderated
    Normally I wouldn't answer a question with another question, but in this case I don't understand what you're doing.
    user8831681 wrote:
    Sending Excel report is enough, the clients can connect DB from Excel via the DSN created in a web server.
    How are you doing that? I've never heard of sharing a DSN on a web server before.
  • 2. Re: How to create a DSN for Oracle Provider for OLE DB in a web server
    722846 Newbie
    Currently Being Moderated
    I have created a DSN in a web server (a Public IP machine) that is mapped to a Oracle DB.

    In my Excel using VBA coding, with the help of RDO object I will call the DSN in the Web server using the connection string like "DSN=ORS;UID=SDATA;PWD=SDATA;"
    This is working fine and in this case the client machine doesn't need Oracle client to be installed or any TNS entry.
    The user can run the Excel report by clicking a button and the click event connects the DSN in the web server (through its URL) and routes to the mapped DB and fetches the quried data.

    Please note that the above DSN is created based on Microsoft ODBC for Oracle driver.
    But the issue is using the Microsoft ODBC for Oracle driver I couldnt call the SP with Ref Cursor.

    When I searched in Internet I came to know using the provider oraoledb.oracle we can call SP that uses REF CURSOR.

    Now my question is what is the driver name that I can use to create a DSN to make use of the provider oraoledb.oracle for calling the SP with REF CURSOR from Excel VBA coding ?

    Appreciate your prompt reply.

    Thanks & Regards,
    Sathish
  • 3. Re: How to create a DSN for Oracle Provider for OLE DB in a web server
    Tridus Journeyer
    Currently Being Moderated
    Ah. That's pretty creative. :)

    Unfortunately I don't know of a way to create an ODBC connection for an OLE DB datasource. The two are fundamentally different, and at one point MS was touting OLE DB as a replacement for ODBC. (It didn't work out that way.)

    You could try creating an ODBC data source using Oracle's provider rather then Microsoft's. It's included in the same client package that has the OLE DB provider.
  • 4. Re: How to create a DSN for Oracle Provider for OLE DB in a web server
    722846 Newbie
    Currently Being Moderated
    I will try the below link and will post the solution if I found

    http://www.sparxsystems.com/enterprise_architect_user_guide/8.0/projects_and_teams/connect_to_oracle_via_odbc.html

    Please help me if you have any similar tips.

    Appreciate your response.

    Thanks & Regards,
    Satz

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points