0

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?

Improve this question
2

2 Answers 2

Reset to default
0

Couple of things

  1. It is unlikely you want to be connecting to XE (if XE is referring to the container root). A default 21c express edition instance will have XE as the root, and XEPDB1 as the pluggable. All work should be done in the pluggable.

  2. If you'd like to avoid the hassles of tnsnames.ora and environment variables etc etc, then use the EZ connect syntax.

    Data Source=//localhost/XEPDB1

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

  • Microsoft.EntityFrameworkCore 9.0.0-rc
  • Oracle.ManagedDataAccess.Core 23.6

For.Net Framework 4.7.2 will be

  • EntityFramework 6.5.1
  • Oracle.ManagedDataAccess 23.6

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.