2

Question: Why can my local code execute a ORAOleDB.Oracle connection but I get no provider found when I run Anonymous execution though the same computers IIS Website.

So I am publishing a website on my local machine to connect to different databases. The code works fine connecting to a database and returning an error code that I parse. I just want to make sure the TNS Listener is up and Oracle Listener is up not actually log into it.

Locally : Code Passes

Push to IIS : Code Fails

However, when I publish the code to my IIS and run the same exact code I get the OraOledb.Oracle provider not found on local machine.

When I publish my Website I receive the following message for every Oracle Database I try:

The 'OraOLEDB.Oracle' provider is not registered on the local machine.

I have registered the dll:
cd C:\ProgramData\oracle\oracle11g\BIN
regsvr32 OraOLEDB11.dll

The following permissions on the folder where oracle is installed

C:\ProgramData\oracle has the following users:
IIS AppPool\DefaultAppPool
IIS_IUSRS
IIS AppPool\ASP.NET v4.0
All these users have Full Control

My Code is as follows. Very simple it just takes the database name and makes a dummy string.

   private void ExecuteDatabase(string database)
    {
        string openstring = "Provider=OraOLEDB.Oracle;Data Source=" + database + ";User Id=x_dummy;Password=x_dummy;Connection Timeout=5;";
        string msg = String.Empty;
        string Elapsed = String.Empty;
        string Result = String.Empty;

        using (OleDbConnection connection = new OleDbConnection(openstring))
        {
            //Timestamp for elapsed time counting
            DateTime stTime = DateTime.Now;
            try
            {
                // Try to connect. If succeed, then close, and create
                // an OK message. x_dummy user and password is improbabile.
                // If connection not successfull, there issued an Oracle
                // exception. We need to parse the exception.
                //connection.ConnectionTimeout = 5;
                System.Diagnostics.Debug.WriteLine("Connection Open");
                connection.Open();
                System.Diagnostics.Debug.WriteLine("Connection Opened");
                // If succeeded...
                msg = "OK";
                //.. then closes
                connection.Close();

            }
            // If connection is not succesfull, we get the errormessage.
            catch (Exception ex)
            {
                //We need the first part of message, this is an Oracle message like this: ORA-01017
                //msg = ex.Message.Split(':')[0];
                msg = ex.Message;
            }
            //Timestamp for elapsed time counting
            DateTime etTime = DateTime.Now;

            //Calculate the elapsed time
            TimeSpan ts = etTime - stTime;

            //Get the millisecs from elapsed time
            Elapsed = ts.Milliseconds.ToString();

            //Get the Result String
            Result = "{" + databaseResult(msg.Split(':')[0]) + "}";
            Result = Result + msg;

        }
    }

    private String databaseResult(string msg)
    {
        string ret = String.Empty;

        switch (msg)
        {
            //kind os listener answers 
            case "ORA-12154":
                ret = "No Oracle listener found";
                break;
            case "ORA-12514":
                ret = "No TNS listener found";
                break;
            case "ORA-12541":
                ret = "No listener ";
                break;
            //ORA-01017 is only good response for us. It means
            //that listener is good, but username and passwor is not.
            //This procedure gives back OK message and elapsed time in this case
            case "ORA-01017":
                ret = String.Format("OK");
                break;
            case "OK":
                ret = String.Format("OK");
                break;
            //In default case this procedure doesn't found listener.
            default:
                ret = "No Oracle listener found";
                break;
        }
        //returns the result of connection                        
        return ret;
    }
Arabidnun
  • 21
  • 2

0 Answers0