Connect VB.NET to ORACLE

[Andrzejek]

I am trying to connect my VB.NET 2008 app to ORACLE database, but I can not provide the Login Name or Password in the connection string. The User is set as external user in Oracle and has 2 granted roles: ABC00 and ABCXX (where XX is a number) ABC00 allows user to connect, but does not allow to see any tables. I need to 'turn the role ON' (ABCXX) with the passwrod stored in Oracle in order to see any information in database.

In VB 6 what I did was:

Dim Cn As ADODB.Connection

strCn = "Driver={Microsoft ODBC for Oracle};" & _
        "SERVER=XYZX;"
[green]'No UserName or Password needed[/green]

Set Cn = New ADODB.Connection
Cn.ConnectionString = strCn
Cn.CursorLocation = adUseNone
Cn.Open 
[green]'I am connected, but can NOT see any tables yet
'Get User's Privs from ORACLE[/green]
strUserPriv = "SELECT USERNAME, GRANTED_ROLE " _
    & " From USER_TABLE WHERE (GRANTED_ROLE LIKE 'ABC%')"

[green]'For Example
'USERNAME  GRANTED_ROLE
'BOB       ABC00
'BOB       ABC77 <- the one I need to get[/green]

recUserPriv.Open strUserPriv, Cn

If recUserPriv.RecordCount > 1 Then
    recUserPriv.MoveNext    'bypass first record
    gUserName = Trim$((recUserPriv!UserName) & "")
    gUserPriv = Trim$((recUserPriv!granted_role) & "")
[green]    'here I have UserName from ORACLE
    ;and his/her role - ABC77    [/green]
    recUserPriv.Close
    
    strUserPriv = "SELECT SomeField From A_Tbl " _
       & " WHERE (Fld = '" & Mid$(gUserPriv, 3, 2) & "')"
[green]    'Here I am getting the password for role 77[/green]
    recUserPriv.Open strUserPriv, Cn
        
    If recUserPriv.RecordCount = 1 Then
        strUserPriv = "Set role " & gUserPriv & " identified by " & _
               recUserPriv!SomeField & ""
        [green]'Turn the role 77 ON[/green]
        Cn.Execute strUserPriv
    End If
End If  [green]'BINGO - I am connected[/green]

My connection string in VB.NET looks like:
[tt]
Data Source=XYZX;Persist Security Info=True;User ID=XYZ888;Password=ABC666
[/tt]
and I am using ORACLE 10.2.0 client

If I omit the login name and password in the connection string, application will ask me for it. It is like connection is not possible based on role ABC00 without login name and password.

How can I connect to ORACLE (using the ABC00 role) without Logn name and password in connection string so I can have the same functionality as I had in VB 6?

Have fun.

---- Andy

 

[RiverGuy]

I rarely work with Oracle. However if you were using ODBC in VB6 and now you're using OLEDB or the ORACLE .Net driver, then if I were you, I'd first try to recreate the functionality in .Net using ODBC, and then attempt to port it over to a different driver if needed. System.Data.ODBC

 

[llmclaughlin]

The below connection string is for connection to database without having to use tnsnames
Put this code in a module class and call startsql from within your form

Public Function StartSQL() As Boolean

Try

StartSQL = True


If Not goOracleSession Is Nothing Then
If goOracleSession.State = ConnectionState.Open Then Exit Function
End If

gstrOracleDBConn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" & minstStationInfo.Item(0).IPAddress & ")(PORT=1521))(CONNECT_DATA=(SID=" & minstStationInfo.Item(0).DatabaseName & ")))"
str1 = "User Id=username;" & _
"Password=password" & ";"

gstrOracleDBConn = "Data Source=" & gstrOracleDBConn & ";" & str1

goOracleSession = New OracleConnection(gstrOracleDBConn)

goOracleSession.Open()
goOracleSession.Close()

goOracleSession.Open()


Catch ex As Exception
StartSQL = False
MsgBox(ex.ToString & vbCrLf & "At line# :" & Erl())
End Try


End Function

Louie

 

[Andrzejek]

llmclaughlin, I get whole bunch of undclared variables with your code.

Besides,

str1 = "User Id=username;" & _
"Password=password" & ";"

I can not use Username or Password

Have fun.

---- Andy