OracleConnection.Open() --- wont work

  • Thread starter Thread starter Docs500
  • Start date Start date
D

Docs500

Hello folks,

Try to setup a .net web application to access an Oracle 10g database.
All the technology is new to us, so if I sound simple it merely means
I am ;)

Anyhow, the problem is, that when I try and call the Open() procecdure
from my asp application I get the following error:

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:
Oracle.DataAccess.Client.OracleException: ORA-12560: TNS:protocol
adapter error

Stack Trace:
[OracleException: ORA-12560: TNS:protocol adapter error]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32
errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx*
pOpoSqlValCtx, Object src, String procedure) +588
Oracle.DataAccess.Client.OracleConnection.Open() +2491
Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean
requery, Boolean fillRequest, CommandBehavior behavior) +311
Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) +229
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Clinisis.OracleTest2.WebForm1.Page_Load(Object sender, EventArgs e)
in c:\inetpub\wwwroot\oracletest2\webform1.aspx.cs:43

What is really annoying me is the code I use will work fine in a
Windows Form application. The connection code is as follows:

private void Page_Load(object sender, System.EventArgs e)
{
//Define SQL select
string sSql = "SELECT * FROM PMI2";

//Create a New Oracle Connection
OracleConnection orConn = new OracleConnection("User
Id=USER;Password=USER;Data Source=clinical");


//Create and instantiate a new Oracle Data Adapter
OracleDataAdapter orDataAdap = new OracleDataAdapter(sSql, orConn);
//orDataAdap.SelectCommand = new OracleCommand(sSql,orConn);
//orConn.Open();
//Create and Instantiate a new DataSet
DataSet dsOracleResults = new DataSet();

//Fill the DataSet from Oracle
orDataAdap.Fill(dsOracleResults);

this.DataGrid2.DataSource = dsOracleResults;
this.DataGrid2.DataBind();
orConn.Close();
}

I have been searching through postings and not found a fix!
I have the following entry in the tnsnames.ora file:
CLINICAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = WXP-ORACLE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CLINICAL)
)
)

And this in the sqlnet.ora:
# sqlnet.ora Network Configuration File:
C:\Oracle\product\10.1.0\Client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

What is also bugging me is that the sqlnet.log file does not seem to
get updated when this error occus - which does not help me debug!

Any help would be most appreciated!
Thanks,
Liam
 
try this change to your tnsnames.ora file:

(CONNECT_DATA =
(SERVER = DEDICATED )
(SERVICE_NAME = CLINICAL)
)

Server could also be SHARED, it will depend on which option you chose when
you created the database.

Also, the error message says "TNS:protocol > adapter error"

Make sure you installed the TCP protocol when you configured the LISTENER.

Hope this helps!

Docs500 said:
Hello folks,

Try to setup a .net web application to access an Oracle 10g database.
All the technology is new to us, so if I sound simple it merely means
I am ;)

Anyhow, the problem is, that when I try and call the Open() procecdure
from my asp application I get the following error:

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:
Oracle.DataAccess.Client.OracleException: ORA-12560: TNS:protocol
adapter error

Stack Trace:
[OracleException: ORA-12560: TNS:protocol adapter error]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32
errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx*
pOpoSqlValCtx, Object src, String procedure) +588
Oracle.DataAccess.Client.OracleConnection.Open() +2491
Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean
requery, Boolean fillRequest, CommandBehavior behavior) +311
Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) +229
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Clinisis.OracleTest2.WebForm1.Page_Load(Object sender, EventArgs e)
in c:\inetpub\wwwroot\oracletest2\webform1.aspx.cs:43

What is really annoying me is the code I use will work fine in a
Windows Form application. The connection code is as follows:

private void Page_Load(object sender, System.EventArgs e)
{
//Define SQL select
string sSql = "SELECT * FROM PMI2";

//Create a New Oracle Connection
OracleConnection orConn = new OracleConnection("User
Id=USER;Password=USER;Data Source=clinical");


//Create and instantiate a new Oracle Data Adapter
OracleDataAdapter orDataAdap = new OracleDataAdapter(sSql, orConn);
//orDataAdap.SelectCommand = new OracleCommand(sSql,orConn);
//orConn.Open();
//Create and Instantiate a new DataSet
DataSet dsOracleResults = new DataSet();

//Fill the DataSet from Oracle
orDataAdap.Fill(dsOracleResults);

this.DataGrid2.DataSource = dsOracleResults;
this.DataGrid2.DataBind();
orConn.Close();
}

I have been searching through postings and not found a fix!
I have the following entry in the tnsnames.ora file:
CLINICAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = WXP-ORACLE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CLINICAL)
)
)

And this in the sqlnet.ora:
# sqlnet.ora Network Configuration File:
C:\Oracle\product\10.1.0\Client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

What is also bugging me is that the sqlnet.log file does not seem to
get updated when this error occus - which does not help me debug!

Any help would be most appreciated!
Thanks,
Liam
Click to expand...
 
Hi Liam,

You say your ORACLE connection is working in a Windows app but not a Web
app. When a Web app runs (depending on your authentication) it runs under the
ASPNET account. From Oracle 9i on, I found we had to make sure that this user
had rights to the ORACLE home folder. Normally the error seen here is "Cannot
Load oci.dll" but it may be worth double checking this just in case.

You can also double check permissions by viewing the registry Security
Permissions for ORACLE.

I'm kind of new to .Net as well so not sure if it will fix your problem but
it solved ours after our upgrade from 8i to 9i where we couldn't run Web apps
only.

Regards
Amelia

Hello folks,

Try to setup a .net web application to access an Oracle 10g database.
All the technology is new to us, so if I sound simple it merely means
I am ;)

Anyhow, the problem is, that when I try and call the Open() procecdure
from my asp application I get the following error:

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:
Oracle.DataAccess.Client.OracleException: ORA-12560: TNS:protocol
adapter error

Stack Trace:
[OracleException: ORA-12560: TNS:protocol adapter error]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32
errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx*
pOpoSqlValCtx, Object src, String procedure) +588
Oracle.DataAccess.Client.OracleConnection.Open() +2491
Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean
requery, Boolean fillRequest, CommandBehavior behavior) +311
Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) +229
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Clinisis.OracleTest2.WebForm1.Page_Load(Object sender, EventArgs e)
in c:\inetpub\wwwroot\oracletest2\webform1.aspx.cs:43

What is really annoying me is the code I use will work fine in a
Windows Form application. The connection code is as follows:

private void Page_Load(object sender, System.EventArgs e)
{
//Define SQL select
string sSql = "SELECT * FROM PMI2";

//Create a New Oracle Connection
OracleConnection orConn = new OracleConnection("User
Id=USER;Password=USER;Data Source=clinical");


//Create and instantiate a new Oracle Data Adapter
OracleDataAdapter orDataAdap = new OracleDataAdapter(sSql, orConn);
//orDataAdap.SelectCommand = new OracleCommand(sSql,orConn);
//orConn.Open();
//Create and Instantiate a new DataSet
DataSet dsOracleResults = new DataSet();

//Fill the DataSet from Oracle
orDataAdap.Fill(dsOracleResults);

this.DataGrid2.DataSource = dsOracleResults;
this.DataGrid2.DataBind();
orConn.Close();
}

I have been searching through postings and not found a fix!
I have the following entry in the tnsnames.ora file:
CLINICAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = WXP-ORACLE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CLINICAL)
)
)

And this in the sqlnet.ora:
# sqlnet.ora Network Configuration File:
C:\Oracle\product\10.1.0\Client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

What is also bugging me is that the sqlnet.log file does not seem to
get updated when this error occus - which does not help me debug!

Any help would be most appreciated!
Thanks,
Liam
Click to expand...
 
Hello folks,

I can confirm Amelia's suggestions. I found that same issue with Oracle 10g
and ODP.net provider version 10. A windows form application had no trouble
opening the connection. However, my web application would generate a null
reference exception when trying to open the connection. Adjusting the
security settings for the aspnet account on the Oracle client and home bin
folders fixes the problem.

Regards,
Heather

Amelia said:
Hi Liam,

You say your ORACLE connection is working in a Windows app but not a Web
app. When a Web app runs (depending on your authentication) it runs under the
ASPNET account. From Oracle 9i on, I found we had to make sure that this user
had rights to the ORACLE home folder. Normally the error seen here is "Cannot
Load oci.dll" but it may be worth double checking this just in case.

You can also double check permissions by viewing the registry Security
Permissions for ORACLE.

I'm kind of new to .Net as well so not sure if it will fix your problem but
it solved ours after our upgrade from 8i to 9i where we couldn't run Web apps
only.

Regards
Amelia

Hello folks,

Try to setup a .net web application to access an Oracle 10g database.
All the technology is new to us, so if I sound simple it merely means
I am ;)

Anyhow, the problem is, that when I try and call the Open() procecdure
from my asp application I get the following error:

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:
Oracle.DataAccess.Client.OracleException: ORA-12560: TNS:protocol
adapter error

Stack Trace:
[OracleException: ORA-12560: TNS:protocol adapter error]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32
errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx*
pOpoSqlValCtx, Object src, String procedure) +588
Oracle.DataAccess.Client.OracleConnection.Open() +2491
Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean
requery, Boolean fillRequest, CommandBehavior behavior) +311
Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) +229
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Clinisis.OracleTest2.WebForm1.Page_Load(Object sender, EventArgs e)
in c:\inetpub\wwwroot\oracletest2\webform1.aspx.cs:43

What is really annoying me is the code I use will work fine in a
Windows Form application. The connection code is as follows:

private void Page_Load(object sender, System.EventArgs e)
{
//Define SQL select
string sSql = "SELECT * FROM PMI2";

//Create a New Oracle Connection
OracleConnection orConn = new OracleConnection("User
Id=USER;Password=USER;Data Source=clinical");


//Create and instantiate a new Oracle Data Adapter
OracleDataAdapter orDataAdap = new OracleDataAdapter(sSql, orConn);
//orDataAdap.SelectCommand = new OracleCommand(sSql,orConn);
//orConn.Open();
//Create and Instantiate a new DataSet
DataSet dsOracleResults = new DataSet();

//Fill the DataSet from Oracle
orDataAdap.Fill(dsOracleResults);

this.DataGrid2.DataSource = dsOracleResults;
this.DataGrid2.DataBind();
orConn.Close();
}

I have been searching through postings and not found a fix!
I have the following entry in the tnsnames.ora file:
CLINICAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = WXP-ORACLE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CLINICAL)
)
)

And this in the sqlnet.ora:
# sqlnet.ora Network Configuration File:
C:\Oracle\product\10.1.0\Client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

What is also bugging me is that the sqlnet.log file does not seem to
get updated when this error occus - which does not help me debug!

Any help would be most appreciated!
Thanks,
Liam
Click to expand...
Click to expand...
 
Back
Top