locked
Connect to Oracle server from web server RRS feed

  • Question

  • User716333301 posted

    Hi everybody!

       I have to connect to Oracle server from web server (virtual machine), located in different places (there are not the same machine). I need to be able to access Oracle database from asp.net page.

       I have installed the ODAC 11.2 Release 4 (11.2.0.3.0) with Oracle Developer Tools for Visual Studio, which includes:

    • Oracle Developer Tools for Visual Studio 11.2.0.3.0
    • Oracle Data Provider for .NET 4 11.2.0.3.0
    • Oracle Data Provider for .NET 2.0 11.2.0.3.0
    • Oracle Providers for ASP.NET 4 11.2.0.3.0
    • Oracle Providers for ASP.NET 2.0 11.2.0.3.0
    • Oracle Database Extensions for .NET 4 11.2.0.3.0 -- for upgrade only
    • Oracle Database Extensions for .NET 2.0 11.2.0.3.0 -- for upgrade only
    • Oracle Provider for OLE DB 11.2.0.3.0
    • Oracle Objects for OLE 11.2.0.3.0
    • Oracle Services for Microsoft Transaction Server 11.2.0.3.0
    • Oracle ODBC Driver 11.2.0.3.0
    • Oracle SQL*Plus 11.2.0.3.0
    • Oracle Instant Client 11.2.0.3.0

       The setup has created a folder under c:\app\<User>.

       I have set the connectiong string in several formats:

    1. - Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};

    2. - SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));uid={3};pwd={4};

    3. - Provider=MSDAORA.1;Password={0};User ID={1};Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={2})(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={3})));Persist Security Info=False;

     but I can't connect to Oracle DataBase.

    Error: "Provider MSDAORA.1 is not register in local computer"

       I have opened the register and I can see driver MSDAORA.1. Any ideas? Have I to install anything else? What I'm doing wrong?

     

    Any help would be welcome! I've spend a few days with this issue...

    Thanks a lot!

     

     

     

    Tuesday, June 12, 2012 4:19 AM

All replies

  • User-661350001 posted

    please first verify that Oracle Server is accesible and able to connect to Database then please get the connection string from here

    http://www.connectionstrings.com/oracle

    Tuesday, June 12, 2012 4:31 AM
  • User716333301 posted

    Hi,

       I have access to Oracle server from web server (ping has response). As I post, I have tried all connection string from www.connectionstrings.com, but I'm not able to connect either get, for example, all records in Oracle table.

       So, I doubt if I need anything else to install o configure or I'm doing something wrong.

    Thanks.

     

     

    Tuesday, June 12, 2012 5:17 AM
  • User269602965 posted

    I have better luck putting the appropriate oracle.dataaccess.dll in the \BIN folder of my web project

    and making a LOCAL reference to it.

    My web config strings look like:

    web.config
    
      <connectionStrings>
        <add name="OraConnStr" connectionString="Data Source={YOUR_ORACLE_SID};Persist Security Info=True;User Id={SCHEMA};Password={Password}" providerName="Oracle.DataAccess.Client">
        </add>
      </connectionStrings>
      
      <namespaces>
        <clear />
        <add namespace="Oracle.DataAccess.Client" />
        <add namespace="Oracle.DataAccess.Types" />
      </namespaces>
    
    '***********************************************'
    ' sample code behind page to update and insert data    '
    
    Imports System.Xml.Linq.XElement
    
    Public Shared Sub updateUnitsActiveFlag(ByVal decCustomerSeq As Decimal)
      ' Update the UNITS.ACTIVE_FLAG on Customer set to Inactive'
      Dim connectionString As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
      Try
        Dim SQL =
        <SQL>
        UPDATE {YOURSCHEMANAME}.UNITS
        SET ACTIVE_FLAG        = 'Inactive',
            SEND_CONTRACT_FLAG = 'Do not send'
        WHERE CUSTOMER_SEQ = :CUSTOMER_SEQ
        </SQL>
        Using conn As New OracleConnection(connectionString)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("CUSTOMER_SEQ", OracleDbType.Decimal, decCustomerSeq, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
        AppCalls.WriteToEventLog(ex, "Updating UNITS.ACTIVE_FLAG from customer inactivation failed", "AppCalls.updateUnitsActiveFlag.vb")
      End Try
    End Sub
      
    Imports System.Xml.Linq.XElement
    
      Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal)
        ' Insert Quantity into new row Units table'
        Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
        Try
          Dim SQL =
          <SQL>
          INSERT INTO {YOURSCHEMANAME}.UNITS
            (UNITS_SEQ, QUANTITY, DATE_CLOSED, DATE_TODAY) 
          VALUES 
            (UNIT_SEQ.NextVal, :BindVarQuantity, :DateClosed, SYSDATE)
          </SQL>
          Using conn As New OracleConnection(OraConnStr)
            Using cmd As New OracleCommand(SQL.Value, conn)
              cmd.Parameters.Clear()
              cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
              cmd.Parameters.Add("DateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input)
              conn.Open()
              cmd.ExecuteNonQuery()
            End Using
          End Using
        Catch ex As Exception
          AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb")
        End Try
    End Sub
      

     

    Tuesday, June 12, 2012 10:55 AM
  • User716333301 posted

    The connection string in web.config is giving me an error with providerName.

    So, with your configuration and Oracle.Dataaccess.dll is giving me this error:

    ORA-12170: TNS:Connect timeout occurred

    I have configured the tnsNames.ora with correct values... Any ideas?

    Thanks!

    Wednesday, June 13, 2012 2:51 AM
  • User269602965 posted

    In my TSNNAMES.ORA I add the ORACLE_SID variable  and I use IP address instead of hostname.

    can you show your tnsnames.ora entry

    and your web.config connect string

    (hidding passwords of course)

     

    Wednesday, June 13, 2012 1:24 PM