locked
how to connect to oracle db RRS feed

  • Question

  • User-1384036652 posted

    Now, I have this problem: if I view the page within VS, it loads the records from oracle table.  But if I try to view the page via iis (localhost), it gives the error.

    An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)


    Any clue?

    Tuesday, May 21, 2013 12:01 PM

Answers

  • User-183374066 posted

    Download Oracle Data Provider for .NET

    After Installation Add Reference to assembly Oracle.DataAccess.dll to your project

    using System;
    using Oracle.DataAccess.Client;
    class OraTest
    {
        OracleConnection con;
        void Connect()
        {
            con = new OracleConnection();
            con.ConnectionString = "User Id=<username>;Password=<password>;Data Source=<datasource>";
            con.Open();
            Console.WriteLine("Connected to Oracle" + con.ServerVersion);
        }
        void Close()
        {
            con.Close();
            con.Dispose();
        }
        static void Main()
        {
            Example OraTest = new OraTest();
            OraTest.Connect();
            OraTest.Close();
        }
    }

    Regards.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 21, 2013 12:29 PM
  • User269602965 posted

    ORA12514 is generic error with many causes.

    Can your ,NET server connect to Oracle using the SQLPLUS that was installed with the Oracle Client?

    Is the database server firewall open on TNS listening port (default is TCP 1521) to traffic from your .NET server?

    Example connection and sql execution code

    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 insert data    '
    
    
    You have an Oracle table called TEST with three text columns
    
    
    vb.net
    
    Imports System.Xml.Linq.XElement
    
    Public Shared Sub InsertIntoTest(ByVal strID As String, ByVal strPerson As String, ByVal strLocation As String)
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString
      Try
        Dim SQL =
        <SQL>
          INSERT INTO {YourSchemaName}.TEST
           (ID, Person, Location)
          VALUES
           (:strID, :strPerson, :strLocation)
        </SQL>
        Using conn As New OracleConnection(OraConnStr)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("strID", OracleDbType.Varchar2, strID, ParameterDirection.Input)
            cmd.Parameters.Add("strPerson", OracleDbType.Varchar2, strPerson, ParameterDirection.Input)
            cmd.Parameters.Add("strLocation", OracleDbType.Varchar2, strLocation, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
      End Try
    End Sub
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 21, 2013 12:30 PM

All replies

  • User-183374066 posted

    Download Oracle Data Provider for .NET

    After Installation Add Reference to assembly Oracle.DataAccess.dll to your project

    using System;
    using Oracle.DataAccess.Client;
    class OraTest
    {
        OracleConnection con;
        void Connect()
        {
            con = new OracleConnection();
            con.ConnectionString = "User Id=<username>;Password=<password>;Data Source=<datasource>";
            con.Open();
            Console.WriteLine("Connected to Oracle" + con.ServerVersion);
        }
        void Close()
        {
            con.Close();
            con.Dispose();
        }
        static void Main()
        {
            Example OraTest = new OraTest();
            OraTest.Connect();
            OraTest.Close();
        }
    }

    Regards.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 21, 2013 12:29 PM
  • User269602965 posted

    ORA12514 is generic error with many causes.

    Can your ,NET server connect to Oracle using the SQLPLUS that was installed with the Oracle Client?

    Is the database server firewall open on TNS listening port (default is TCP 1521) to traffic from your .NET server?

    Example connection and sql execution code

    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 insert data    '
    
    
    You have an Oracle table called TEST with three text columns
    
    
    vb.net
    
    Imports System.Xml.Linq.XElement
    
    Public Shared Sub InsertIntoTest(ByVal strID As String, ByVal strPerson As String, ByVal strLocation As String)
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString
      Try
        Dim SQL =
        <SQL>
          INSERT INTO {YourSchemaName}.TEST
           (ID, Person, Location)
          VALUES
           (:strID, :strPerson, :strLocation)
        </SQL>
        Using conn As New OracleConnection(OraConnStr)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("strID", OracleDbType.Varchar2, strID, ParameterDirection.Input)
            cmd.Parameters.Add("strPerson", OracleDbType.Varchar2, strPerson, ParameterDirection.Input)
            cmd.Parameters.Add("strLocation", OracleDbType.Varchar2, strLocation, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
      End Try
    End Sub
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 21, 2013 12:30 PM