Insert query in Oracle: Invalid table name

问题:

I have created a simple oracle table that I'm trying to connect to VB.net

I'm able to connect but I can't add any record to the table. I'm receiving the message: ORA-00903: Invalid table name.

Here's the preview screen of my Oracle Database Manager from which I created the table.

Oracle Enterprise Manager screenshot (Sorry it's in french)

Here is the code I'm using:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Oracle = New OracleObject("192.128.7.15", 1521, "TechnicalDatabase", "system", "admin")

        If Oracle.Connect = False Then
            Exit Sub

    End If

    Dim Sql As String

    Sql = "INSERT INTO USER (ID, FirstName) values ('1','Jim');"

    Oracle.ExecuteSubQuery(Sql)
    Oracle.Disconnect()
End Sub

    Public Function ExecuteSubQuery(ByVal SQL As String) As Boolean
    Dim Command As New OracleCommand(SQL, Connection)

    Command.CommandType = CommandType.Text

    Try
        Command.ExecuteReader()
    Catch ex As Exception
        MsgBox("Error in query !" & Chr(13) & ex.Message)
        Return False
    End Try

    Return True
End Function

Thanks.

回答1:

USER is an Oracle reserved word. You have two choices:

1. Use a different table name (for example USERS or USER_LIST).

2. Put the name in double quotes. If you do this, the table name also becomes case-sensitive, which can lead to a lot of "gotchas". I'd recommend using a different table name.

回答2:

Try replacing ExecuteReader with ExecuteNonQuery

Public Function ExecuteSubQuery(ByVal SQL As String) As Boolean
    Dim Command As New OracleCommand(SQL, Connection)

    Command.CommandType = CommandType.Text

    Try
        Command.ExecuteNonQuery()
        Return True

   Catch ex As Exception
        MsgBox("Error in query !" & Chr(13) & ex.Message)
        Return False
    End Try
End Function

However your primary problem is the word USER that is a reserved keyword as Ed Gibbs has correctly written in its answer (And given reasonable workarounds)

回答3:

1. First off, you are connecting as SYSTEM but the table is owned by COLMANTECHNICALADMIN. Assuming that you haven't created a synonym in the SYSTEM schema and that you're not doing an ALTER SESSION SET current_schema=COLMANTECHNICALADMIN, you would need to fully qualify the table name.

2. USER is a reserved word. If you really, really, really want to use a reserved word as a table name, you would need to enclose it in double quotes every time you reference it and ensure that you are always capitalizing the name correctly. It would generally be much better to choose a name that did not collide with a reserved word. But, if you insist on doing so, your query would need to be

   INSERT INTO COLMANTECHNICALADMIN."USER" (ID, FirstName) values ('1','Jim');

3. You should not be connecting to the database as either SYS or SYSTEM. Your applications should be using some application-specific account that you have created that has whatever privileges the application needs.

回答4:

Thank you guys for you answers.

Here is my final solution:

• Renamed USER table to USERS
• Run the query "INSERT INTO TECHNICALADMIN.USERS (ID, FirstName) values (1,'Jim')"

Remark: When I tried to execute the query with a semicolumn at the end, I got an error message: ORA00911: Invalid Character. So I had to remove it.

Thanks for your help.