none
What is best way to connect to Oracle 11g? RRS feed

  • Question

  • Hi. I've been trying to connect to Oracle 11g using different methods, but all seem to fail. The tables (vendors') are huge and when I try to get data from any table - I get either out of memory error or 'ORA-01013: user requested cancel of current operation' error or 'vshost.exe has stopped working' and asks to close the program. If I connect using ODBC from Access - it works very fast; if I connect from VB to the same table and try to filter out data by date - takes forever to pull few thousands rows and if I try to use date range - it bombs out completely. My question is - what is the best way to pull data from Oracle using VB? ODBC connection, using typed DataSets? If yes - how to control TimeOut settings - couldn't find it. OleDBConnection and what provider if yes - msdora or OraOLEDB.Oracle or something else? OracleConnection - Oracle.DataAccess.Client or is it retired and no longer recomended?

    Below there is a sample of the code that gives me 'vshost.exe' error described above. What is wrong with this code? As I mentioned - Oracle table is huge and I'm trying to extract about 160000 records out of few millions..

    Private Sub Sample3()
    
      Dim sConnectionString As String = "Provider=msdaora;Data Source=Name;User Id=username;Password=password;"
    
      Dim mySelectQuery As String _
    
       = "SELECT TO_DATE(COMPLETE_DATE, 'DD-MON-YY') as RhbDate,COMPLETE_DATE, START_TIME, END_TIME FROM TBL.SOMETABLE WHERE TO_DATE(COMPLETE_DATE, 'DD-MON-YY') >= ?"
    
    
    
      Dim myConnection As New OleDbConnection(sConnectionString)
    
      Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)
    
      
    
      'Set the parameter value.
    
      myCommand.Parameters.Add("@p1", OleDbType.DBDate).Value = Format(cmbBegPPD.Text, "DD-MON-YY")
    
       'Open connection to Oracle database.
    
      myConnection.Open()
    
    
    
      'Populate the DataReader - that's where I get the VSHOST error:
    
      Dim myReader As OleDbDataReader = myCommand.ExecuteReader()
    
      Dim RecordCount As Integer
    
      Try
    
       While myReader.Read()
    
        RecordCount = RecordCount + 1
    
        MessageBox.Show(myReader.GetString(0).ToString())
    
       End While
    
       If RecordCount = 0 Then
    
        MessageBox.Show("No data returned")
    
       Else
    
        MessageBox.Show("Number of records returned: " & RecordCount)
    
       End If
    
      Catch ex As Exception
    
       MessageBox.Show(ex.ToString())
    
      Finally
    
    
    
       'Close all objects.
    
       myReader.Close()
    
       myConnection.Close()
    
      End Try
    
      
    
     End Sub
    
    

     


    Alla Sanders
    • Edited by Alla2552 Tuesday, August 9, 2011 8:59 PM naming
    • Moved by Liliane Teng Thursday, August 11, 2011 7:16 AM more appropriate forum (From:Visual Basic General)
    Tuesday, August 9, 2011 8:56 PM

Answers

  • Sorry - didn't close this post earlier. I had resolved all issues, by using ODP.net type of connection (OracleConnection command) and defined each parameter as OracleParameter. I used OracleDataReader to get data from Oracle and used OleDbCommand to insert filtered data into Access database. Also had to specify each parameter of insert into Access command (OleDbType). Hope it helps someone else. I know it's just overview to get the idea. If someone needs a code - let me know.

    Alla Sanders

    • Marked as answer by Alla2552 Monday, March 26, 2012 4:14 PM
    Monday, March 26, 2012 4:13 PM

All replies

  • Hello. Currently I'm pulling data from Oracle into Access directly, but was asked to move the processing into Visual Basic project that would pull data automatically at night. Oracle database is setup by vendor - so I can not change/add/edit data in any way. I'm working on writing the VB project and have a few problems.

    - I setup 2 datasets - one is connected to Oracle; other one is connected to Access database. What is the better way to extract data from Oracle dataset and insert into Access database?

    - I don't need all data from the Oracle table, but need to filter it by date. The Oracle tables are huge and previewing data immediately causes OutOf Memory exceptions. Another problem - the field in Oracle is defined as DateTime (shows as System.DateTime type in DataSet) - yet in preview panel it shows as Date only. Converting field to time didn't help - adds bogus time stamp instead of showing actual time stored in the field. Please note that in Access or dbexplorer the same field shows date and time with no problem. How can I pull time value?

    - Tried to setup a command to pull data from Oracle into Access directly, but keep getting an error. It seems that it doesn't like to have filter in the command. As I mentioned before - there are millions of records in that table and I need to extract about 160000 records. Ideally I'd need to pull the data from Oracle, link it with another table in Access, manipulate some of the columns that I got from Oracle (for example - extract only time from dateTime field) and then import the results into Access table. But it seems that I can't even accomplish filtering data... Below there is a command I tried to use:

     cnToAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccFileName & ""
        cnToAccess.Open()
     Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [AccessTableName] SELECT TO_DATE(COMPLETE_DATE, 'MM/DD/YYYY') as SpecDate,COMPLETE_DATE, DEPT_ID as DEPT_CODE, NOW() as DatePulled FROM [OPTC.OracleTableName] WHERE COMPLETE_DATE >=#" & StartDate & "# IN '' [ODBC;Driver={Microsoft ODBC For Oracle};Server=SrvName;uid=username;pwd=password;]", cnToAccess)
    AccessCommand.ExecuteNonQuery()
          cnToAccess.Close()
    


    I'm getting error on ExecuteNonQuery - "In operator without () query expression 'COMPLETE_DATE>=...". I'm wondering if there is another way to do it using DataSets? Ironic, that something that was so easy to setup in Access is very challenging in VB... I appreciate your help!


    Alla Sanders
    • Merged by Liliane Teng Thursday, August 11, 2011 7:08 AM op has the same concern
    Friday, August 5, 2011 1:29 PM
  • "INSERT INTO [AccessTableName] SELECT TO_DATE(COMPLETE_DATE, 'MM/DD/YYYY') as SpecDate,COMPLETE_DATE, DEPT_ID as DEPT_CODE, NOW() as DatePulled FROM [OPTC.OracleTableName] WHERE COMPLETE_DATE >=#" & StartDate & "# IN '' [ODBC;Driver={Microsoft ODBC For Oracle};Server=SrvName;uid=username;pwd=password;]"

    Hi Alla,

    Thanks for your post.

    Based on my understanding, you could first extract the values from Oracle and then insert these values to Access database. There maybe something wrong with this AccessCommand. After "IN" is a data collection not a connectionstring. Please check on your side.

    By the way, there is a tutorial about how to use the Oracle Data Provider for .NET to access data in an Oracle Database.

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/GettingStartedVBVersion/GettingStartedNET_VBVersion.htm
    (Getting Started with Oracle Data Provider for .NET (VB.NET Version))

    http://www.oracle.com/technetwork/topics/dotnet/index-085163.html
    (Oracle Data Provider for .NET)

    If you have any concerns, please feel free to follow up.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Monday, August 8, 2011 7:34 AM
  • Thank you, Liliane. The command that I used - I actually copied from the response by MVP (see http://social.msdn.microsoft.com/forums/en-us/adodotnetdataproviders/thread/D61D3D4F-0F8D-49DD-8484-71E02DA98AF2): Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [TABLE_NAME] SELECT * FROM [SCHEMA.TABLE_NAME] IN '' [ODBC;Driver={Microsoft ODBC For Oracle};Server=SERVER_NAME;Uid=xxx;Pwd=xxx;]", AccessConn) The command seems identical to mine, except for Where part that I need. Am I missing something? Thank you!
    Alla Sanders
    Monday, August 8, 2011 3:36 PM
  • Have you looked that ODP.NET from Oracle?
    Tom Shelton
    Tuesday, August 9, 2011 9:26 PM
  • Yes, but getting an error as well - on execute reader command (Database error: ORA-00911: invalid character). Are you saying that ODP.NET is the best way to connect to Oracle? Isn't it the method that won't be supported anymore?

    Anyway - here is the code that I used. Please let me know - what could cause 'invalid character' error?

     Try
    
         conn.ConnectionString = "User Id=user;Password=pass;Data Source=datasource;"
    
       conn.Open()
    
       
    
       Dim sql1 As String = "SELECT TO_DATE(COMPLETE_DATE, 'MM/DD/YYYY') as RhbDate, START_TIME, END_TIME FROM TABLENAME WHERE TO_DATE(COMPLETE_DATE, 'd-MMM-yy') >= ?"
    
       Dim cmd As New OracleCommand(sql1, conn)
    
       cmd.CommandType = CommandType.Text
    
       cmd.Parameters.Add("@p1", OracleDbType.Date).Value = Format(cmbBegPPD.Text, "d-MMM-yy")
    
    '**** getting error on next command ****
    
       Dim dr As OracleDataReader = cmd.ExecuteReader()
    
       Try
    
        While dr.Read()
    
         MessageBox.Show(dr.GetString(0).ToString)
    
        End While
    
       Catch ex As Exception
    
        MessageBox.Show(ex.ToString)
    
       End Try
    
       dr.Close()
    
       dr.Dispose()
    
       conn.Dispose()
    

    Catch

    ex As OracleException

    ' catches only Oracle errors

     

    Select Case ex.Number

     

    Case 1

    MessageBox.Show(

    "Error attempting to insert duplicate data.")

     

    Case 12560

    MessageBox.Show(

    "The database is unavailable.")

     

    Case Else
     

    MessageBox.Show(

    "Database error: " + ex.Message.ToString())

     

    End Select

     

     

    Finally
     

     

    conn.Dispose()

     

    End Try

     


    Alla Sanders
    • Edited by Alla2552 Wednesday, August 10, 2011 12:38 PM missed code
    Wednesday, August 10, 2011 12:29 PM
  • Hi Alla2552,

    If you set a breakpoint at this line Dim AccessCommand As New System.Data.OleDb.OleDbCommand(....), after debug step by step, please check AccessCommand variable, what is the result? is it a right SQL query sentence?

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 10, 2011 2:30 PM
  • ODP.NET is a speparate from System.Data.OracleClient.  System.Data.OracleClient is deprecated as of .NET 4.  ODP.NET is from Oracle, and is still supported.

     

    Without an extensive look, the thing that strikes me immediately, is that your parameter syntax is wrong for oracle.  It should be more like:

    Dim sql As String = "SELECT * FROM SOME_TABLE WHERE SOME_FIELD = :THE_PARAM"
    Using connection As New OracleConnection (connectionString)
      Using command As New OracleCommand (sql, connection)
        command.Parameters.Add ("THE_PARAM", theValue)
        Using reader As OracelDataReader = command.ExecuteReader()
         Do While reader.Read()
           ' do cool stuff
         Loop
        End Using
      End Using
    End Using
    


    That may not be 100% syntactically correct - it's off the cuff code :)  But, the idea is that the parameters in Oracle use the : not an @.

    HTH


    Tom Shelton
    Wednesday, August 10, 2011 2:33 PM
  • Thank you, Tom. With all types of connections available out there I got overwhelmed and it's nice that I can concentrate on 1 specific type. I tried your suggestion and got a little further this time - passed ExecuteReader, but got an error below:

    ContextSwitchDeadlock was detected
    Message: The CLR has been unable to transition from COM context 0x5a7928 to COM context 0x5a7a98 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

    Then, after I said continue it didn't actually pulled any rows - went into 'try' block - read part and returned no rows. I'm thinking that it might have something to do with date parameter. Seems that Oracle stores date differently from SQL or Access. Do I need to add any special conversions for date parameter?


    Alla Sanders
    Wednesday, August 10, 2011 4:34 PM
  • I've seen that error before while debuging in VS.  Usually, when dealing with large records sets.  The couple of times I've had it rebooting would usually clears it up.  Beyond that, I would need to see more specific code - what you posted above, is well - unreadable in the current format.

    As for dates, if you are using the oracle date datatype, the provider should handle the conversion to date for you.


    Tom Shelton
    Wednesday, August 10, 2011 5:38 PM
  • Thank you, Tom! I closed Visual Studio and re-opened and it didn't give me the same error during debug. It looks like it worked this time and returned rows! Now the big question - how can I insert all rows that were returned from Oracle into table in Access? I appreciate your help!
    Alla Sanders
    Wednesday, August 10, 2011 6:13 PM
  • Yes - sql command looks fine. When I had syntax error before - it would give me syntax error immediately. I still think it's a 'where' clause that messing up the command. I've seen similar command in multiple posts, including the one I mentioned above, but all examples didn't have any filters/parameters set. I had experimented with multiple filters thinking that may be the problem is with the date filter, but I'm getting the same error with text filter as well as with different ways of setting up the parameters. I wonder if that type of command doesn't accept any filters/parameters at all?

    Is there different way to insert rows from Oracle into Access db, using VB? I had setup different way of connecting to Oracle, using ODP.NET and was able to read the rows, but not sure where to go from there. How do I insert those rows in Access table? Thanks!


    Alla Sanders
    Wednesday, August 10, 2011 8:06 PM
  • Hi Alla2552,

    I moved this thread to ADO.NET Managed Providers forum which is more appropriate for this issue. Here you may get more better support since more this aspect experts live here. Thanks for your understanding.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 11, 2011 7:19 AM
  • I'm not sure why was my thread moved from Visual Basic forum? I need help with the VB code... Anyway - figured out that WHERE clause needed to be in the end of the string. Still getting an error - but different one this time - 'Unspecified Error'. I need to research further.
    Alla Sanders
    Thursday, August 11, 2011 4:14 PM
  • Hi Alla,

    Welcome!

    Base on my understanding and my experience, I think you may try another way. From your reply, you could retrieve records which is too big from Oracle database, and your question is how to insert these big records to Access Database, right?

    My reply is: You can try to page your records, we can easy to transform small records by ado.net.

    How to page your records:we can use "Select Top(100) * from XXX where ID not in (Select Top(page*100) Id from XX)". we need two connections and use memory to transform records then insert into Access database.

    I'm not sure about my suggestion, please feel free to let me know your feedback.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 12, 2011 9:46 AM
    Moderator
  • Thank you, Alan. Would I use the same command as before, just add top 100? As I mentioned - usually it's about 160000 records. Do I setup a loop to get all records? How would I know in advance - how many records (it varies) in order to setup such loop?

    Would new command look like this:

    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [AccessTableName] SELECT TOP(100) TO_DATE(COMPLETE_DATE, 'MM/DD/YYYY') as SpecDate,COMPLETE_DATE, DEPT_ID as DEPT_CODE, NOW() as DatePulled FROM [OPTC.OracleTableName] IN '' [ODBC;Driver={Microsoft ODBC For Oracle};Server=SrvName;uid=username;pwd=password;] WHERE COMPLETE_DATE >=?", cnToAccess)
    
    

    Please advise
     


    Alla Sanders
    Monday, August 15, 2011 1:12 PM
  • Hi Alla,

    Thanks for your feedback.

    To tell truth, I haven't the environment on my computer, it's hard for me to repro your scenario. 

    You may misunderstand me, my suggestion is transfer records by memory, in order to avoid the stack overflow, we can page the records. For example, we can easy to retrieve 100 records from Oracle and put them in dataset, then we insert the dataset into Access by ADO.net. I know this's not a good way to handle this, the performance is bad. Thanks for understanding. 

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Wednesday, August 24, 2011 7:26 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 1, 2011 6:18 AM
    Moderator
  • Sorry that it took so long to reply. I'm finally back working on that project again. I was able to read the data (using executeReader) - connect to Oracle, but not sure how to insert all that data into Access from that reader. Not sure if there is a way to bypass the reader and insert it directly. With the insert command that I mentioned in earlier posts - I keep getting 'Unspecified Error' which doesn't give me any information.

    P.S. Select top(100) doesn't work as well.


    Alla Sanders
    Tuesday, October 11, 2011 7:59 PM
  • There is no direct method from the DataReader to an Access database. You will have to insert a row at a time. You can use ExecuteNonQuery for each row, or add all the rows to a DataTable and Update to the Access database.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, October 12, 2011 4:41 PM
  • Hello, Paul! I'm so glad you come across this post! You have helped me in the past (long while ago) and I hope that it will be the case once again... I had tried to use ExecuteNonQuery and keep getting 'Unspecified Error'. Please see the latest version of the code below. About inserting 1 row at a time - I'm afraid it won't work, since we are talking about inserting 130000 - 160000 records... With the code below I'm getting 'Unspecified Error' on ExecuteNonQuery line. Please help!!!

     Dim ConnectionString As String
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccFileName & ""
           
            Using cnToAccess As New OleDbConnection(ConnectionString)
                cnToAccess.Open()
    
                Dim errorMessages As String
                Dim i As Integer
                errorMessages = ""
                Try
                    Date1.OracleDbType = OracleDbType.Date
                    Date1.Value = CDate(cmbBegPPD.Text)
                    Date2.OracleDbType = OracleDbType.Date
                    Date2.Value = CDate(cmbEndPPD.Text)
                    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tblName] ([SomeDATE], [DEPT_ID], [START_TIME]) SELECT SomeDATE, DEPT_ID, START_TIME FROM [OPTC.ORACLETABLENAME] IN '' [ODBC;Driver={Microsoft ODBC for Oracle};Server=SrvName;uid=username;pwd=passw;] WHERE TO_DATE(START_TIME, 'MM/DD/YYYY')>=:Date1 and TO_DATE(START_TIME, 'MM/DD/YYYY')<=:Date2", cnToAccess)
                    AccessCommand.CommandTimeout = 360
                    AccessCommand.Parameters.AddWithValue("Date1", OracleDbType.Date).Value = Date1.Value
                    AccessCommand.Parameters.AddWithValue("Date2", OracleDbType.Date).Value = Date2.Value
                    AccessCommand.ExecuteNonQuery()
                    cnToAccess.Close()
                    AccessCommand.Dispose()
                    Catch ex As OleDbException
                    MsgBox(ex.Message)
                    cnToAccess.Close()
                    cnToAccess.Dispose()
                    For i = 0 To ex.Errors.Count - 1
                        errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
                                       & "Message: " & ex.Errors(i).Message & ControlChars.Cr _
                                       & "NativeError: " & ex.Errors(i).NativeError & ControlChars.Cr _
                                       & "Source: " & ex.Errors(i).Source & ControlChars.Cr _
                                       & "SQLState: " & ex.Errors(i).SQLState & ControlChars.Cr
                    Next i
    
    
                End Try
            End Using
    

     


    Alla Sanders
    Wednesday, October 12, 2011 6:10 PM
  • Below is an example that works for me. Make sure to set up the parameters correctly. You should be using the OleDbType data types (e.g. System.Data.OleDb.OleDbType.Date). Also, the example uses Jet OLEDB but I'm pretty sure this works with the ACE OLEDB Provider as well.

            Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Test Files\db1 XP.mdb;" & _
                                                                    "Jet OLEDB:Engine Type=5")
    
            AccessConn.Open()
    
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [TableName] SELECT * FROM [SCHEMANAME.TABLENAME] IN '' [ODBC;Driver={Microsoft ODBC For Oracle};Server=SERVERNAME;Uid=UserID;Pwd=Password;]", AccessConn)
    
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()
    
    
    




    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 13, 2011 12:21 PM
  • Still no luck. I changed the parameters to OleDbType, but getting the same 'unspecified error'. Tried formatting dates in different ways, tried hardcoding parameter - all with the same result. Is there any other way to do it? I appreciate your help.
    Alla Sanders
    Thursday, October 13, 2011 2:56 PM
  • What does your code look like now?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 13, 2011 3:48 PM
  • Here is updated code. I removed some fields just for testing - for now.

     Dim d1 As New OleDbParameter("Date1", OleDbType.DBDate)
            Dim d2 As New OleDbParameter("Date2", OleDbType.DBDate)
            Dim ConnectionString As String
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccFileName & ""
           
            Using cnToAccess As New OleDbConnection(ConnectionString)
                cnToAccess.Open()
    
                         Dim errorMessages As String
                Dim i As Integer
                errorMessages = ""
                Try
                                   Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [AccessTableName] ([START_TIME], [THERAPIST_ID], [FACILITY_KEY], [SOME_ID]) SELECT [START_TIME], [THERAPIST_ID], [FACILITY_KEY], [SOME_ID] FROM [OPTC.OracleTableName] IN '' [ODBC;Driver={Microsoft ODBC for Oracle};Server=Name;uid=username;pwd=passw;] WHERE START_TIME>=:d1 and START_TIME<=:d2", cnToAccess)
    
                         AccessCommand.CommandTimeout = 6000
                         AccessCommand.Parameters.Add(d1)
                    AccessCommand.Parameters.Add(d2)
                                   d1.Value = CDate(cmbBegPPD.Text)
                    d2.Value = CDate(cmbEndPPD.Text)
                                   AccessCommand.ExecuteNonQuery()
                    cnToAccess.Close()
                    AccessCommand.Dispose()
                   Catch ex As OracleException
                    Select Case ex.Number
                        Case 1
                            MessageBox.Show("Error attempting to insert duplicate data.")
                        Case 12545
                            MessageBox.Show("The database is unavailable.")
                        Case Else
                            MessageBox.Show("Database error: " + ex.Message.ToString())
                    End Select
               
                Catch ex As OleDbException
                    MsgBox(ex.Message)
                    cnToAccess.Close()
                    cnToAccess.Dispose()
                    For i = 0 To ex.Errors.Count - 1
                        errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
                                       & "Message: " & ex.Errors(i).Message & ControlChars.Cr _
                                       & "NativeError: " & ex.Errors(i).NativeError & ControlChars.Cr _
                                       & "Source: " & ex.Errors(i).Source & ControlChars.Cr _
                                       & "SQLState: " & ex.Errors(i).SQLState & ControlChars.Cr
                    Next i
    
    
                End Try
            End Using
    

     


    Alla Sanders
    Thursday, October 13, 2011 4:01 PM
  • The parameter placeholders (e.g. :d1 and :d2) need to use OLEDB notation, so replace those with question mark characters.

    If you still get an error after the change, please indicate what it is.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 13, 2011 4:23 PM
  • If I understood you correctly - you wanted to update condition to following -

    WHERE START_TIME>=? and START_TIME<=?

    I had tried it before and it still gives me the same error. I don't think it's a problem with parameters at all - tried to remove condition and parameters completely and still getting the same error on ExecuteNonQuery command.


    Alla Sanders
    Thursday, October 13, 2011 5:42 PM
  • Correct to your question. Which error are you getting now? Did you check the InnerException message?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 13, 2011 5:50 PM
  • The same frustrating message 'Unspecified Error' on ExecuteNonQuery line. I tried to catch different type of errors, using IOException, DataException, OracleTypeException, OracleException, ArgumentException, AccessViolationException, EvaluateException.

    But it usually ignores those and goes to

    OleDbException or Exception. I don't see InnerException as a choice for exceptions (?)


    Alla Sanders
    Thursday, October 13, 2011 6:07 PM
  • Sorry - found command ex.InnerException. Well - it has value of 'Nothing'....
    Alla Sanders
    Thursday, October 13, 2011 6:11 PM
  • Removed exception type and got a little more details in the error:

       System.Data.OleDb.OleDbException was unhandled

      ErrorCode=-2147467259

      Message="Unspecified error"

      Source="Microsoft Access Database Engine"

      StackTrace:

           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)

           at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

           at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)

           at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

           at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

           at ProductivityOracle.frmMain.frmMain_Load(Object sender, EventArgs e) in \\somepath\frmMain.vb:line 111

           at System.EventHandler.Invoke(Object sender, EventArgs e)

           at System.Windows.Forms.Form.OnLoad(EventArgs e)

           at System.Windows.Forms.Form.OnCreateControl()

           at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)

           at System.Windows.Forms.Control.CreateControl()

           at System.Windows.Forms.Control.WmShowWindow(Message& m)

           at System.Windows.Forms.Control.WndProc(Message& m)

           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)

           at System.Windows.Forms.ContainerControl.WndProc(Message& m)

           at System.Windows.Forms.Form.WmShowWindow(Message& m)

           at System.Windows.Forms.Form.WndProc(Message& m)

           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

           at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)

           at System.Windows.Forms.Control.SetVisibleCore(Boolean value)

           at System.Windows.Forms.Form.SetVisibleCore(Boolean value)

           at System.Windows.Forms.Control.set_Visible(Boolean value)

           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

           at System.Windows.Forms.Application.Run(ApplicationContext context)

           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

           at ProductivityOracle.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)

           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

           at System.Threading.ThreadHelper.ThreadStart()

      InnerException: Nothing

     


    Alla Sanders
    Thursday, October 13, 2011 6:23 PM
  • Any chance the amount of data you are inserting exceeds 2GB? Does the code run for a while or does the exception occur almost immediately.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 13, 2011 6:40 PM
  • I get the error pretty much immediately. There are a lot of records in Oracle, but if I do SQL pass-through connection query directly in Access database to connect to Oracle (ODBC type of connection) - it is able to open the table and extract the data I need (around 158000 rows of data). The problem is that I was asked to move that process to Visual Basic and something that was so easy to do in Access is taking me forever to accomplish and still not even close to being resolved.


    Alla Sanders
    Thursday, October 13, 2011 6:47 PM
  • Are you running 64-bit Windows?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 13, 2011 7:01 PM
  • No - 32 bit... Sorry if I sound frustrated, it is directed to myself!
    Alla Sanders
    Thursday, October 13, 2011 7:03 PM
  • A good way to troubleshoot is to test with the simplest INSERT query just to see if you can get that working. I would try an Oracle table with just a couple of columns, and no parameters, just to see if you can do an export.

    I'll try a couple of scenarios on my end to see if I encounter any issues.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 13, 2011 7:29 PM
  • I tried. Even if I have 1 column to insert, no conditions, no parameters - still getting the same 'unspecified error'.
    Alla Sanders
    Thursday, October 13, 2011 7:41 PM
  • Tried to insert 1 column in Access table from different table in the same Access database - it worked. It added over 174000 rows in about 3 min. So I know that connection to Access is working. The command I used was similar, but without Oracle connection, of course.
    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO tblAccessTest1 (SomeID) SELECT SomeID FROM tblAccessTest2", cnToAccess)
    
    


    Alla Sanders
    • Edited by Alla2552 Thursday, October 13, 2011 8:04 PM typo
    Thursday, October 13, 2011 8:03 PM
  • Can you connect to Oracle through ODBC. Try the below example. You will need to change the connection string for your database:

            Dim ODBCConnection As New System.Data.Odbc.OdbcConnection
    
    
            ODBCConnection.ConnectionString = "DRIVER={Microsoft ODBC for Oracle};" & _
                                                "SERVER=Name;" & _
                                               "UID=username;PWD=passw;"
    
            ODBCConnection.Open()
    
            Dim Command As New Odbc.OdbcCommand("SELECT * FROM OPTC.OracleTableName WHERE START_TIME > ?", ODBCConnection)
            Dim Parameter As New Odbc.OdbcParameter("param1", #9/30/2011#)
            Command.Parameters.Add(Parameter)
            Dim Reader As Odbc.OdbcDataReader = Command.ExecuteReader
    
            If Reader.Read Then
                MsgBox("OK")
            End If
    
            ODBCConnection.Close()
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 13, 2011 8:19 PM
  • Yes - I was able to connect using your sample code.
    Alla Sanders
    Thursday, October 13, 2011 8:27 PM
  • What do I need to do to make OleDbCommand work? Or is there a way to use ODBC Command to insert data into Access? I'm not sure what I need to do... Seems that I can connect to Access and I can connect to Oracle - as separate commands, but not together. Thank you! 
    Alla Sanders
    Thursday, October 13, 2011 8:58 PM
  • Since I didn't get any more responses - I'm guessing that there is no direct way to insert data from Oracle directly into Access, right?

    I went ahead and split it into connecting to Oracle, reading data and inserting data line by line into Access. It takes about an hour to process about 200,000 records and periodically I would get the message below. I already increased the timeout for the commands to 180000, but it still happening. Help!

    ContextSwitchDeadlock was detected

    Message: The CLR has been unable to transition from COM context 0x195838 to COM context 0x1959a8 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

     


    Alla Sanders
    Thursday, October 20, 2011 2:27 PM
  • You may want to post the code you were working with after the suggested changes. I was never able to reproduce the exception you were generating when using the syntax I suggested.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 20, 2011 2:50 PM
  • I thought I was using pretty much the same syntax that you suggested?! Does it mean that you are able to insert Oracle data directly into Access, using OleDbCommand?

    Anyway - here is the latest version of the code:

     Private Sub PullDataFromOracle()
    
            Dim Date1 As OracleParameter = New OracleParameter()
            Dim Date2 As OracleParameter = New OracleParameter()
            Dim FacAbbr As OracleParameter = New OracleParameter()
            Dim DirMin As Double
            Dim ConcMin As Double
            Dim GrpMin As Double
            Dim sql2 As String
            Dim FacAbbrNew As String
            Dim rst1 As New ADODB.Recordset
            conn.ConnectionString = "User Id=username;Password=password;Data Source=name;"
            conn.Open()
            g_objCn.Provider = "Microsoft.ACE.OLEDB.12.0"
            g_objCn.Open(g_strConnectionString)
            sql2 = "DELETE tblAccessTable.* FROM tblAccessTable"
            rst1.Open(sql2, g_objCn)
            Date1.OracleDbType = OracleDbType.Date
            Date1.Value = CDate(cmbBegPPD.Text)
            Date2.OracleDbType = OracleDbType.Date
            Date2.Value = CDate(cmbEndPPD.Text & " 11:59:59 PM")
            Dim sql1 As String = "SELECT COMPLETE_DATE, DEPT_ID, SOME_ID, START_TIME, END_TIME FROM OPTC.ORACLETABLENAME WHERE (START_TIME >=:Date1 and START_TIME<=:Date2) and (END_TIME >=:Date1 and END_TIME<=:Date2))"
           
            Dim cmd As New OracleCommand(sql1, conn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.Add("Date1", Date1.Value)
            cmd.Parameters.Add("Date2", Date2.Value)
            cmd.CommandTimeout = 180000
            Dim dr As OracleDataReader = cmd.ExecuteReader()
                   
            Try
                g_objCn.CommandTimeout = 180000
                While dr.Read()
                 g_objCn.Execute("INSERT INTO [tblAccessTable] ([COMPLETE_DATE], [DEPT_CODE], [SOME_ID],  [START_TIME], [END_TIME]) VALUES (#" & Format(dr.Item("COMPLETE_DATE"), "MM/dd/yyyy") & "#, '" & dr.Item("DEPT_ID") & "', '" & dr.Item("SOME_ID") & "', #" & dr.Item("START_TIME") & "#, #" & dr.Item("END_TIME") & "#)")
                End While
                conn.Close()
    
             Catch ex As OracleException
                Select Case ex.Number
                    Case 1
                        MessageBox.Show("Error attempting to insert duplicate data.")
                    Case 12545
                        MessageBox.Show("The database is unavailable.")
                    Case Else
                        MessageBox.Show("Database error: " + ex.Message.ToString())
                End Select
                conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
                conn.Close()
            End Try
            If rst1.State <> 0 Then
                rst1.Close()
            End If
            g_objCn.Close()
    
        End Sub
    

     


    Alla Sanders
    • Edited by Alla2552 Thursday, October 20, 2011 9:10 PM typo
    Thursday, October 20, 2011 3:06 PM
  • OK, well this code looks like it's inserting one row at a time. First, you should use parameters for your VALUES in the INSERT statement. Second, formatting of the dates is unecessary since they are stored as numeric values. You should only format when displaying the data. Using parameters should increase the performance.

    Yes, I was able to do a INSERT INTO...SELECT FROM directly to Access from Oracle. I posted an example on 10/13. There are instances where this will not work, such as when you have CLOB or BLOB columns, but otherwise it should not be an issue.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 20, 2011 3:31 PM
  • Correct - I had to use inserting 1 row at a time, since I wasn't able to make it work the other way. I'm still not sure why it didn't work though - the only difference in your sample from 10/13 and my sample of the code is the Access provider, right? Am I missing something else?

    I'm formatting the date to strip it off the time value that I don't need; there are other columns that I didn't list that I'm taking only time value, and removing the date. I have a lot of code (and Access queries) that was built on those columns being formatted certain way, that's why I'm trying to keep it consistent. About parameters - should I change the code to VALUES (?,?,?,?,?) instead of specifying actual values? What would be the command for assigning the values? I have g_objCn defined as Adodb.connection and not sure how to assign parameters for this type of connection. Should I change type of connection to something else?

    Public g_objCn As New ADODB.Connection

    I appreciate your help, as always...


    Alla Sanders
    Thursday, October 20, 2011 4:57 PM
  • Yes, use the question marks as parameter placeholders.

    The object g_objCn is a Command object so it's relatively easy to add Parameters:

    g_objCn.CommandText = "< SQL command goes here >"
    
    While dr.Read()
       g_objCn.Parameters.Clear()
       g_objCn.Parameters.AddWithValue("@param1", dr.Item("COMPLETE_DATE")) 
       g_objCn.Parameters.AddWithValue("@param2", dr.Item("DEPT_ID")) 
       g_objCn.Parameters.AddWithValue("@param3", dr.Item("SOME_ID")) 
       'etc.
       g_objCn.ExecuteNonQuery()
    End While
    
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 20, 2011 8:31 PM
  • Thank you, Paul. I've been experimenting with it and so far with parameters I'm getting type mismatch error. I'm guessing that I'd need to specify each parameter type first.

    On the other hand - what type of connection is better for connecting to Access - Adodb.connection or OleDbConnection? I've read that ODBC is not recommended, right? Thank you!


    Alla Sanders
    Thursday, October 20, 2011 8:43 PM
  • Use ADO.NET (System.Data.OleDb) instead of Classic ADO (ADODB).

    I assumed that COMPLETE_DATE was a Date data type in your Access table. If it isn't then you probably need to add ToString to the end of the DataReader column reference.

    BTW, that could very well cause problems in the INSERT INTO...SELECT FROM statement as well. You can't insert if the column data types of the source and destination are different.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 20, 2011 9:03 PM
  • Thank you. Yes - it is date parameter. I had added each type specifically, but still getting type mismatch error. I have 15 parameters. Will try to experiment tomorrow with less to try to figure out which one is it. Ironically - my code that I posted earlier today works just fine with those data types, but not new way...
    Alla Sanders
    Thursday, October 20, 2011 9:09 PM
  • Got all parameters straitened out and it works now. It still takes a long time to pull data - it took about 40 min to pull 157000 records into Access. I think it's because it has to load all records using ExecuteReader and then write it one by one. I had unchecked the error check for ContextSwitchDeadlock to avoid the error I mentioned above. Is it OK to do so or not? I saw it as a suggestion in a different post, but not sure if it might cause any problems... Do you have any other suggestions on how I can improve the time it takes to accomplish the task? Thank you! 
    Alla Sanders
    Friday, October 21, 2011 5:36 PM
  • As long as your code is running OK you can probably turn off ContextSwitchDeadlock.

    I don't know that there is a better method for the export unless you can get the SQL method working. Unfortunately, Access doesn't really have a bulk insert method.

    Just out of curiosity, can you successfully execute a SELECT INTO query?

    SELECT * INTO [AccessTableName] FROM [OPTC.OracleTableName] IN '' [ODBC;Driver={Microsoft ODBC For Oracle};Server=ServerName;Uid=userID;Pwd=password;]
    

    ...where AccessTableName doesn't currently exist.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, October 21, 2011 6:17 PM
  • Thank you. Nope - I tried that type of insert as well - got the same frustrating "unspecified error". I also tried to pull data from different Oracle table with the same results. Even if I just pull 1 string type column - same error message... Not sure who invented "unspecified error", but I'm definitely not happy with that person! Lol
    Alla Sanders
    Friday, October 21, 2011 6:27 PM
  • Sorry - didn't close this post earlier. I had resolved all issues, by using ODP.net type of connection (OracleConnection command) and defined each parameter as OracleParameter. I used OracleDataReader to get data from Oracle and used OleDbCommand to insert filtered data into Access database. Also had to specify each parameter of insert into Access command (OleDbType). Hope it helps someone else. I know it's just overview to get the idea. If someone needs a code - let me know.

    Alla Sanders

    • Marked as answer by Alla2552 Monday, March 26, 2012 4:14 PM
    Monday, March 26, 2012 4:13 PM