Background Installed Oracle Database 21c Express Edition in WINDOWS 11 machine. Using Oracle SQL Plus created necessary tablespace and user with DBA privilege. Also configured tnsnames.ora. These are the command in SQL Plus.
/* Creating Tablespace*/
create tablespace ts_Trading DATAFILE 'D:\Database\OracleTest\ORATest.dbf'
size 10m
autoextend on maxsize 1024m
extent management local uniform size 64k
/* Creating User */
create user ####### identified by #######
default tablespace ts_Trading
temporary tablespace temp
quota unlimited on ts_Trading
quota 0 on system
/
grant DBA to #######
Successfully test the newly created username from SQL Plus using the following style (user name / password @ instance name) #######/*****@XE
and it works.
Also successfully tested tnsping utility to test the connectivity of the Oracle client.
But failed to connect with Oracle from VB.net Application (.Net 6 framework with OracleManagedDataAccess.Core) Write the following code in windows form
Imports System.Data.OracleClient
Imports Oracle.ManagedDataAccess.Client
Imports Oracle.ManagedDataAccess.Types
Public Class frmOracle
Private Sub frmOracle_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim oradb As String = "Data Source=XE;User Id=#######;Password=*****;"
Dim conn As New OracleConnection(oradb)
conn.Open()
End Sub
End Class
It is raising the following error message (Oracle.ManagedDataAccess.Client.OracleException: 'ORA-12154: Cannot connect to database. Cannot find alias XE in DataSources, , EZConnect)
Do I need to do something more with configuring oracle? or, Anything else?
Problem Resolved
There something more to do for working with Oracle with .Net technologies. First of all, I installed Oracle Developer Tools for Visual Studio 2022. It adds an option to work with Run SQLPlus Script* from tool menu of VS2022 IDE and many other things. I implemented the solution in two different framework (.Net 8.0 and .Net Framework 4.7.2). For Different framework different versions package are needed to install from Nuget.
For .Net 8.0 packages will be
For.Net Framework 4.7.2 will be
With the help of Run SQLPlus Script*, I check the problem regarding connection string as there is an easier way connection test. It made a minor change in my connection string like it
"Data Source=DESKTOP-60AADF9;Persist Security Info=True;User ID=#####; Password=*******;"
Finaly the actual code in the form is
Imports Oracle.ManagedDataAccess.Client
Imports Oracle.ManagedDataAccess.Types
Public Class frmOracle
Private Sub frmOracle_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim oradb As String
oradb = "Data Source=DESKTOP-60AADF9;Persist Security Info=True;User ID=######; Password=*******;"
Dim conn As New OracleConnection(oradb)
conn.Open()
End Sub
End Class
Successfully made operations like data manipulation, data definition and data retrieval using OracleDataReader, OracleCommand and ExecuteReader. Stack Overflow community members help me to find the solution, thanks to all. We are software developer, development never ends. Any further better solution will be highly appreciated.