locked
asp.net to oracle application produces error ORA-12154 :TNS:could not resolve the connect identifier specified RRS feed

  • Question

  • User-153615472 posted

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> 

    <o:p> </o:p>

    I created webapp on one server to query an oracle database housed on another server

    It works from within visual studio,”view in browser” but when the page is server from IIS the following is displayed<o:p></o:p>

    <o:p> </o:p>

    System.Data.OracleClient.OracleException: ORA-12154: TNS:could not resolve the connect identifier specified<o:p></o:p>

    <o:p> </o:p>

    Any help is much appreciated; [:'(]

    full details of the setup are below, is this a permissions issue.

    I have read many many posts and believe that I have everything configured correctly ?

    <o:p> </o:p>

    <o:p> </o:p>

    SERVERone: <o:p></o:p>

    Windows 2003 server running IIS 6.0 with .NET framework 2.057 and oracle InstantClient.<o:p></o:p>

    Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210<o:p></o:p>

                            I have a tnsname.ora and sqlnet.ora saved to the same directory (C:\instantClient\instantclient10_1) <o:p></o:p>

    I have a corresponding enviroment variable to that folder TNS_ADMIN C:\instantClient\instantclient10_1<o:p></o:p>

    “Authenticated Users” has FULL CONTROL  permissions on this folder and subfolders<o:p></o:p>

    Permission for read execute are also set for IWAM_SERVERone , IUSR_SERVERone, SERVERtwo/ASPNET<o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    SERVEtwo: <o:p></o:p>

    Windows 2003 sever which is running an instance of Oracle 9i database.in <o:p></o:p>

    ORA home folder “Authenticated Users” has FULL CONTROL permissions and sub folders <o:p></o:p>

    Permission for read execute are also set for IWAM_SERVERone, IUSR_SDMS-SERVERone, SERVERtwo ASPNET<o:p></o:p>

    <o:p> </o:p>

    I can successfully connect from SERVERone to SERVERtwo using TOAD 9.0.1 and I can query the database.<o:p></o:p>

    <o:p> </o:p>

    <o:p>More Details.....</o:p>

    I want to create a web app on SERVERone to queryan oracle database on SERVERtow

    I created an ASP.NET application using visual web developer.<o:p></o:p>

    I addded a sqldatasource and configured the sqldatasource to use “oracle database” as the data source, and the data provider.NET Framework Data Provider for Oracle”<o:p></o:p>

    I set the Server Name to the entry from the Tnanames.ora file an dentered the username and password<o:p></o:p>

    When I click TEST CONNECTION it works , I then added a select * from xyz query<o:p></o:p>

    <o:p> </o:p>

    WHEN I RUN THE PAGE FROM WITHIN VWD it works perfectly ( “view in browser”)<o:p></o:p>

    <o:p> </o:p>

    However when I add the site to IIS and launch the page I get the ORA-12154 error

    <o:p> </o:p>

    <o:p>

    TNSNAME.ORA entry<o:p></o:p>

    <o:p> </o:p>

    HOMER= <o:p></o:p>

      (DESCRIPTION = <o:p></o:p>

        (ADDRESS_LIST = <o:p></o:p>

            (ADDRESS = <o:p></o:p>

              (PROTOCOL = TCP)<o:p></o:p>

              (HOST = 182.198.100.14)<o:p></o:p>

              (PORT = 1521)<o:p></o:p>

            )<o:p></o:p>

        )<o:p></o:p>

        (CONNECT_DATA =<o:p></o:p>

           (SID = ERR)<o:p></o:p>

           (GLOBAL_NAME = ERR.world)<o:p></o:p>

        )<o:p></o:p>

      )<o:p></o:p>

     <o:p></o:p>

    sqlnet.ora ( this is the only line in this file , I also tried NTFS to no avail)<o:p></o:p>

    <o:p> </o:p>

    SQLNET.AUTHENTICATION_SERVICES= (NONE)<o:p></o:p>

    </o:p>

    <o:p> </o:p>

    DATA SOURCE<o:p></o:p>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"<o:p></o:p>

                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand='SELECT * FROM XYZ'><o:p></o:p>

                <SelectParameters><o:p></o:p>

    <o:p> </o:p>

    <o:p> WEB CONFIG Entry</o:p>

    <o:p>

    <

    </o:p>

    ORA-12154: TNS:could not resolve the connect identifier specified<o:p></o:p>

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.OracleClient.OracleException: ORA-12154: TNS:could not resolve the connect identifier specified


    Source Error: <o:p></o:p>

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. <o:p></o:p>


    Stack Trace: <o:p></o:p>

    <o:p> </o:p>
    [OracleException (0x80131938): ORA-12154: TNS:could not resolve the connect identifier specified<o:p></o:p>
    ]<o:p></o:p>
       System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc) +84<o:p></o:p>
       System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName) +688<o:p></o:p>
       System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions) +135<o:p></o:p>
       System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +37<o:p></o:p>
       System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28<o:p></o:p>
       System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429<o:p></o:p>
       System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70<o:p></o:p>
       System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +512<o:p></o:p>
       System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85<o:p></o:p>
       System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +102<o:p></o:p>
       System.Data.OracleClient.OracleConnection.Open() +34<o:p></o:p>
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121<o:p></o:p>
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137<o:p></o:p>
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83<o:p></o:p>
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770<o:p></o:p>
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17<o:p></o:p>
       System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149<o:p></o:p>
       System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70<o:p></o:p>
       System.Web.UI.WebControls.GridView.DataBind() +4<o:p></o:p>
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82<o:p></o:p>
       System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69<o:p></o:p>
       System.Web.UI.Control.EnsureChildControls() +87<o:p></o:p>
       System.Web.UI.Control.PreRenderRecursiveInternal() +41<o:p></o:p>
       System.Web.UI.Control.PreRenderRecursiveInternal() +161<o:p></o:p>
       System.Web.UI.Control.PreRenderRecursiveInternal() +161<o:p></o:p>
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360<o:p></o:p>

    <o:p> </o:p>


    Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210<o:p></o:p>

    Wednesday, February 7, 2007 5:54 AM

All replies

  • User-1623675128 posted

    Hi

    did you give permissions to the ASP.NET users for ORacle home folder and subfolders too[Check this at subfolders level whether the permissions were set or not]

    Thursday, February 8, 2007 1:13 AM
  • User-153615472 posted

    Hi

    Thanks for the reply.  My Oracle Hoem folder ( c:/instanClient/) has permissions for "ASP.NET Machine Account " for all sub folders

     Thsi is really bugging me now , can anyone help?

    Aidan 

    Thursday, March 1, 2007 6:04 AM
  • User-861005831 posted

    me too getting the same error if got the solution  please inform  to me

    my address:

    A . Shiva Prasad

    GIS Developer

    MIDWESTINFOTECH Pvt LIMITED

    70 , Kanakapura main road , JP Nagar 6th Phase

    Opposite to family mart  Bangalore

    INDIA

    @mail : shiva.prasad@midwestinfotech.com , altrenate mail is : asp.347@gmail.com

    mobile : +919886451711

     

     

    Friday, March 2, 2007 2:13 AM
  • User-1623675128 posted

    Hi,

    If you are using asp.net2.0 then processmodel attribute in machine.config file values set to "system".Other than this no guessses...

    if this also not sort out your problem means you should reinstall the s/w.

    Friday, March 2, 2007 4:05 AM
  • User-153615472 posted

    HI

    Thanks Ramana123,

    It seems to be system alreday.

    <section name="processModel" type="System.Web.Configuration.ProcessModelSection, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" allowDefinition="MachineOnly" allowLocation="false" /> 

     

    The bosses are now trying to get rid of  asp.net for this application and use pl/sql.

    I really want to use asp.net , ANY HELP IS MUCH APPRECIATED GUYS 

    Friday, March 2, 2007 6:31 AM
  • User-1623675128 posted

    Hi,

    It seems your machine.config doesnt have the attribute username= 'system' try with after adding best of luck..

     

    Friday, March 2, 2007 6:44 AM
  • User-153615472 posted

    Thanks again for teh reply RAM.

     When I add username='system or userName='system' or userName="system"

    I get the teh parser error message below  , Any thoughts, have I mispelled it ?

    Thank you again
    Parser Error Message: Unrecognized attribute 'userName'. Note that attribute names are case-sensitive.

    Machine.config entry... 

    <section name="processModel" userName="system" type="System.Web.Configuration.ProcessModelSection, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" allowDefinition="MachineOnly" allowLocation="false"/>
               
     

    Friday, March 2, 2007 9:22 AM
  • User-1623675128 posted

    Hi,

    in asp.net2.0 after enteruing into that section in machine.config then press "psace bar" so that the intelligence window will open for to avoid the spelling mistakes.

    But I think there will be two process model sections are available in 2.0.please check it and add since I dont have 2.0 in my pc at this moment I unable to figure it which section ecatly it is.

    Sunday, March 4, 2007 10:49 PM
  • User-153615472 posted

    Hi

    Thanks again for your help Ram,
    I found the second sectioin in the machien.config under <system.web>
    I added the userName attribute using the intellisense as suggested. I set teh userName property to system.

    Unfortunatley it did not resolve the TNS error I'm recieving. The section in my machien.config is as follows

     <system.web>
            <processModel userName="system" autoConfig="false"/>

    I also tried

     <system.web>
            <processModel userName="system" autoConfig="true"/>

     

     I am now at a complete loss as to what the problem is?

    Monday, March 5, 2007 5:16 AM
  • User-406085658 posted

    Hi,

     

    Was anyone able to find the the solution to the above problem??

    I am stuck here too and cant figure out what's the issue.

     

    Please help

     

    Thanks,

    Wednesday, March 14, 2007 2:47 PM
  • User-153615472 posted

    Hi

    I'm still completely baffled by this.

    I worked around it by installing .net and iis on the "data Server" and use it as teh web server in order to serve the data from my asp.net page, so essentaill both web and data are on the same server and it works . THIS MAY HELP YOU

    It some env variable but i can't figure out what .

    Please get in touch if you can fix this annoying issue

    Thanks 

     

     

    Friday, March 16, 2007 11:44 AM
  • User-2005691517 posted

    On Windows 2003 server, ASP.Net applications run in the security context of the "Network Service" user. (not aspnet)

    Grant rights to "Network Service" user on the relevant folders (e.g. Oracle ) and it should solve your problem.

    Wednesday, April 4, 2007 6:31 AM
  • User-153615472 posted

    Hi

    Thank you for your reply

    I have added Network Service and set full control on oracle home directory and sub folders.

    I am still getting the same ORA-12154 error.

    I will await a reboot of the machine (possibly tomorow) to see if that is required before the security settings filter down.

    I will post details on what happens,
     

    THANKS AGAIN FOR YOUR SUGGESTION 

    Aidan 

    Wednesday, April 4, 2007 7:12 AM
  • User-153615472 posted

    Hi,

    unfortunatley I am still seeing this error ORA-12154 having granted full control permissions to "Network Service" to teh ORA_HOME folder on dataserver (and to the oracle clinet folder on web server) 

    I rebooted the box to make sure the permissions changed.

    I'm lost ! ?

     

    Aidan 

     

     

    Thursday, April 5, 2007 6:52 AM
  • User-2005691517 posted

    Can you check how many oracle homes you have on the webserver?

    They should be listed under the following registry key 

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

    Thursday, April 5, 2007 7:20 AM
  • User1320774252 posted

    I may be leading you the wrong way but worth a look.

    if your database is on another server from the IIS server then you may need to look into impersonation settings in your web.config - had a similare issue but dont have access to the particular config at the moment so cant help with the details

    Paul

    Thursday, April 5, 2007 9:34 AM
  • User-153615472 posted

    Hi Prashant,

    Thanks again for the prompt response. 

    In registry  under Oracle I have 2 Keys "KEY_XEClient and "OracleMTSRecoveryService"

    In KEY_XEClient the following are set
    ...
    ORACLE_HOME is set as "C:\XEClient" 

    ORACLE _HOME_KEY is "SOFTWARE\ORACLE\KEY_XEClient" ,
    ORACLE_BASE is "C:\XEClient"
    ORACLE_HOME_NAME is XEClient
    ... 

    I think you might have found a problem !! , I  thought the path would be C:\InstantClient

    In my enviroment variables I have the following System variables Path variable "C:\XEClient\bin"..."C:\instantClient"

    SQLPATH "C:\XEClient\sqlplus"

    I also have the user varaible TNS_ADMIN "C:\instantClient\InstantClient10_1"
     

    Should I update the enviroment variable to be  C:\XEClient\sqlplus

    Thank you again Praschant  for the help.

    Aidan
     

    Thursday, April 5, 2007 10:12 AM
  • User-153615472 posted

    Hi PTEKelly

     

    I have <identity impersonate="true" /> in the web.config and I *believe* I have the correct permission for teh ASPNET user on teh appropriate folders

    I'm still getting the ORA-12154 error . Any thought on other attributes I'd need for impersonation in teh web.config

    Thanks , any help is much appreciated 

     

    Aidan 

    Thursday, April 5, 2007 10:30 AM
  • User-2005691517 posted

    Try first with giving rights to "NetworkService" on the folder and its subfolders.

    And then restart the machine if possible. 

    Thursday, April 5, 2007 8:29 PM
  • User-1371014704 posted

    Platform: WindowsXP sp2 / Visual Studio 2003 / .net Framework v1.1.4322 / Oracle 8.1.7.4

    After uninstalling and reinstalling oracle 8.1.7.4, I kept getting stubborn "ORA-12154 :TNS:could not resolve the connect identifier specified" from ASP.net app.

    I was unable to assign read & execute on my c:\oracle folder to my IUSR_computer account.  Turns out my PC had been renamed before I took possession of it.  IIS properties tab indicated it was using IUSR_OLDcomputer instead of IUSR_CURRENTcomputer.

    Adding read & execute on c:\oracle for IUSR_OLDcomputer finally solved ORA-12154.

    These permissions are standard fix for ORA-12154 but the OLDcomputer name was a twist not mentioned among all the posts I've read.  Thought I'd share.

    Tuesday, November 6, 2007 1:12 PM
  • User1559214423 posted

    Hi

    Go to the Oracle Home / Admin / Network folder and look at the tnsnames.ora file.  Your database connection string should match the SID stated in the file.

     Hope this helps

     Fouwaaz

    Tuesday, November 6, 2007 2:24 PM
  • User1103877942 posted

    did you ever get this to work?  I too wrote an app that access an Oracle DB on another server.  I get the same error.  I've tried everything that is mentioned on this page.  Any suggestions would be appreciated.

     

    Thanks

    Tom

    Monday, May 12, 2008 12:53 PM
  • User1559214423 posted

    Could you post the tnsnames.ora file (Oracle Home / Admin / Network) and the connection string?  That way, we'll be able to get a better idea.

    Monday, May 12, 2008 3:56 PM
  • User758795899 posted

    I, too, have run into the same problem.

    My scenario is as follows:

    - Win 2008
    - Oracle 11g
    - IIS 7.0, configured to manage .svc-styled web services

    Oracle connectivity has been tested through a standard ODP client - successfully.
    tnsping shows the tnsnames.ora is properly constructed, and that it is reachable through ORACLE_HOME. SERVICE_NAME (not SID) is used in the CONNECT_DATA section.
    Permissions on ORACLE_HOME has been granted to Network Service.

    My Web Service method gets the 12154 error and I am at a loss as to why. I can debug into the service method and the connection.Open() method throws an exception when the connection string is identical to the one that was used in the simple database client.

    I have seen this same code work in IIS 6. As IIS 7 does not use the same configuration commands as IIS 6, I am wondering if my flavor of this dilemma could be attributable to IIS 7 config.

     Any insights into the problem would be welcomed.

    Thanks

    Friday, May 30, 2008 6:33 PM
  • User758795899 posted
    I have gotten by this problem by ensuring IUSR_ComputerName, NETWORK SERVICE, ASPNET, and IIS_IUSRS have read access to %ORACLE_HOME%\Network\Admin\TNSNAMES.ORA. Of these, I think the anonymous user and NETWORK SERVICE are the more critical. However, I made sure all of them had access. Rebooting after setting access is important.
    Sunday, June 1, 2008 6:03 AM
  • User758795899 posted
    I am somewhat shooting in the dark here because I have not narrowed down the cause of my problem to my satisfaction ... I've been hunting for possibilities and believe that my problem was an access problem, and yours may be as well. Ensure that your ORACLE_HOME environment variable is properly defined ... this Oracle error signals some problem accessing ORACLE_HOME and the TNSNAMES.ORA file - so it may point to the fact that your ORACLE_HOME is not pointing to the proper location. To ensure that your TNSNAMES.ORA file is properly structured and you can access it, see if you can tnsping your database. If you cannot, then your TNSNAMES.ORA is either not structured correctly, or you cannot access it. Trying to use sqlplus will also tell you the file is structured right and is accessible. If you can do the above, your problem is most likely an IIS user permission issue. You might try to check out the IIS logs to see which user is trying to access the TNS...ORA file to ensure this user has permissions. IIS authentication method could also be an issue ... you might look into this. Ensure NETWORK SERVICE and IUSR_ComputerName have at least read access to %ORACLE_HOME%\Network\Admin and to the TNSNAMES.ORA in this folder (I assume you have a standard Oracle location for ORACLE_HOME). Ensure your connection string in your app or web config is proper ... and make sure that you are using Oracle's data provider rather than Microsoft's - probably not necessary to resolve an access problem, but it cannot hurt.
    Sunday, June 1, 2008 6:27 AM
  • User-1912196532 posted

    http://www.techonthenet.com/oracle/errors/ora12154.php

     

    Does this help at all? 

    Thursday, June 5, 2008 12:14 PM
  • User1484686823 posted

    One more addition to this thread: I was facing the same error and tried all of the above. I was running a VB.net application as the administrator of the domain, so access was unlikely to be the problem.

    Eventually what appeared to be the root-cause of my problem was the fact that I was running the application on a 64 bit machine and had installed my application in the wrong directory.

    I had already installed the 10g oracle client for 64bit for which an earlier error had warned me. However, from that moment on ORA-12154 kept appearing, indicating a communication problem between the application and the Oracle-client.

    • TNSPing referred to the correct directory, the connection test worked properly.
    • An ODBC connection using the client worked properly, but as soon as I used this through my application using System.Data.ODBC, I had the same problem again.

    In the end I moved my application from C:\Program Files (x86) to C:\ProgramFiles and this solved it for me

    Tuesday, June 10, 2008 4:38 PM
  • User-1231423037 posted

    Hi, guys,

    I had the same problem, and I found a workaround- maybe someone will use as I use it.

     

    So, I think the problem is with access to tnsnames.ora or sqlnet.ora, because when you run your app in VS Developement Server it runs under your account's right (or user who ran the devenv.exe proccess) and everything is OK (or when you run sql plus, sql developer everything looks the same), when you run under IIS it runs with ASPNET account and its rights which are insufficient  for  these files

    To workaround  you can change  or  add  xml node to your web.config or  machine.config. The node should be under  <system.web> section, the node should look like:

     <identity impersonate="true" userName="yourWindowsAccountName" password="appropriatePassword" />

     

    Have fun, if betters ideas let as all know! Thx 

    Tuesday, July 15, 2008 10:54 AM
  • User-1231423037 posted

    I found a workaround
     

     

    I found the solution (I will quote this site: http://ora-12154.ora-code.com/ )

    " If you are using local naming (TNSNAMES.ORA file):" // we all use TNS

    1. Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

        -open file SQLNET.ORA a paste this:

            names.directory_path=(LDAP, TNSNAMES, ONAMES, HOSTNAME)

             //for most of us only "TNSNAMES" is sufficient so it maybe                    names.directory_path=(TNSNAMES)
     

    2. Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

       -if you didn't move it, it's on his proper directory

       -if you want to just use the tnsnames.ora with aspnet user, you have to set rights for directory tree, and for files! Make sure three times for three and files separately separately

    3.Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

    4.Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
     

     And that's it.

    Wednesday, July 16, 2008 2:36 AM
  • User1913540530 posted

    I had this same error. The solution for me was to simply add the ASPNET user to the accounts allowed to access the tnsnames.ora file:

    1) Locate the tnsnames.ora file

    2) Right click and select properties

    3) Click Security and add the ASPNET user, make sure it has at least Read and Read & Execute permissions

     

    Friday, November 21, 2008 10:48 AM
  • User983280779 posted

    Hi,

    Could you please tell me how to add ASPNET user to access tnsnames.ora?

    Regards,

    Parul

    Thursday, March 1, 2018 1:27 PM
  • User269602965 posted

    The error is very generic and is caused by many things.

    You should be using the Oracle data provider, not the deprecated MS SQL Oracle data provider.

    I presume your web app is compiled as .NET 2.0 since you installed the.NET 2.0 UNMANAGED Oracle driver.

    I like to put my Oracle driver directly in my /BIN folder and make local reference, instead of relying in the GAC installation.

    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>
    

    In your select statement you need to use both SCHEMA name and TABLE name. 

    Example select in code behind the web page.

    Imports System.Xml.Linq.XElement
    
    Public Shared Sub selectCustomerInfo(ByVal decCustomerSeq As Decimal)
      ' Select customer name and email using Customer table key passed from application to Oracle via ODP.NET'
      Dim connectionString As String = ConfigurationManager.ConnectionStrings("{YourConnectionStringName}").ConnectionString
      Try
        Dim SQL =
        <SQL>
        SELECT CUSTOMER_SEQ, NAME_LAST, NAME_FIRST, EMAIL
        FROM  {YOURSCHEMANAME}.CUSTOMER
        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
      End Try
    End Sub 
    

    It is better to put all your SELECT, INSERT, UPDATE, DELETE in code behind your WEB PAGE, not IN the web page.

    Even better to put all your SELECT, INSERT, UPDATE, DELETE in PL/SQL on the database and call the PL/SQL Package (collection of procedures).

    And of course, do not give the web user DIRECT access to the data schema.  Set up a separate user and user role with limited specific privileges.

    Thursday, March 1, 2018 2:53 PM