locked
OracleBulkCopy - Excel to Oracle problem??? RRS feed

  • Question

  • User-1104215994 posted

    hi,

    I am trying to copy excel sheet data to Oracle. I am using ODP.NET the latest one.

    But getting this error:

    ORA-00604: error occurred at recursive SQL level 1

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    ORA-06512: at line 8

    ORA-00942: table or view does not exist

    How Can I fix this?

    best Regards.

    Here is the code:

    ...

     Dim command As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT [VENDORID],[REQUESTDATE],[ITEMID],[PACKAGEID],
    [QUANTITY],[TOTAL],[DELIVERYID],[REQUESTTYPE],[STATUSID],[APPROVED],[DISPATCHDATE],[APPROVEDBY],
    [APPROVEDATE] FROM [Sheet1$] where VENDORID is not null and ItemID is not null and PACKAGEID is not null"
    , connection)                                 connection.Open()                                 Using dr As DbDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)                                     'If dr.HasRows Then                                     '    While dr.Read()                                     '        'Add to ArrayList if Update Successfull                                     '        bulkArrList.Add(dr(0).ToString + "," + dr(1).ToString + "," + dr(2).ToString + "," + dr(3).ToString)                                     '    End While                                     'End If                                     Using bulkCopy As Oracle.DataAccess.Client.OracleBulkCopy = New Oracle.DataAccess.Client.OracleBulkCopy(destinationConnection)                                         bulkCopy.DestinationTableName = "VENDORREQUEST"                                         bulkCopy.ColumnMappings.Clear()                                         bulkCopy.ColumnMappings.Add("VENDORID""VENDORID")                                         'bulkCopy.ColumnMappings.Add("REQUESTDATE", "REQUESTDATE")                                         'bulkCopy.ColumnMappings.Add("ITEMID", "ITEMID")                                         'bulkCopy.ColumnMappings.Add("PACKAGEID", "PACKAGEID")                                         'bulkCopy.ColumnMappings.Add("QUANTITY", "QUANTITY")                                         'bulkCopy.ColumnMappings.Add("TOTAL", "TOTAL")                                         'bulkCopy.ColumnMappings.Add("DELIVERYID", "DELIVERYID")                                         'bulkCopy.ColumnMappings.Add("REQUESTTYPE", "REQUESTTYPE")                                         'bulkCopy.ColumnMappings.Add("STATUSID", "STATUSID")                                         'bulkCopy.ColumnMappings.Add("APPROVED", "APPROVED")                                         'bulkCopy.ColumnMappings.Add("DISPATCHDATE", "DISPATCHDATE")                                         'bulkCopy.ColumnMappings.Add("APPROVEDBY", "APPROVEDBY")                                         'bulkCopy.ColumnMappings.Add("APPROVEDATE", "APPROVEDATE")                                         bulkCopy.WriteToServer(dr)                                         bulkCopy.Close()                                     End Using                                 End Using                                 connection.Close()                             End Using
    Thursday, August 11, 2011 8:06 AM

Answers

  • User-1104215994 posted

    It should be Schema.TableName but the problem is I can NOT use OracleBulkCopy because there is a trigger on the table.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 12, 2011 9:08 AM