c++oracle-databaseoracle11gocci

ORA-00947: not enough values when creating object in Oracle


I created a new TYPE in Oracle in order to have parity between my table and a local c++ object (I am using OCCI interface for C++).

In the code I use

void insertRowInTable ()
  {

    string sqlStmt = "INSERT INTO MY_TABLE_T VALUES (:x)";

    try{
    stmt = con->createStatement (sqlStmt);
    ObjectDefinition *o = new ObjectDefinition ();

    o->setA(0);
    o->setB(1);
    o->setC(2);
    stmt->setObject (1, o);
    stmt->executeUpdate ();
    cout << "Insert - Success" << endl;
    delete (o);
    }catch(SQLException ex)
    {
       //exception code
    }

The code compiles, connects to db but throws the following exception

Exception thrown for insertRow Error number: 947 ORA-00947: not enough values

Do I have a problematic "sqlStmt"? Is something wrong with the syntax or the binding?

Of course I have already setup an environment and connection

 env = Environment::createEnvironment (Environment::OBJECT);
    occiobjm (env);
    con = env->createConnection (user, passwd, db);

Solution

  • How many columns are in the table? The error message indicates that you didn't provide enough values in the insert statement. If you only provide a VALUES clause, all columns in the table must be provided. Otherwise you need to list each of the columns you're providing values for:

    string sqlStmt = "INSERT INTO MY_TABLE_T (x_col) VALUES (:x)";
    

    Edit: The VALUES clause is listing placeholder arguments. I think you need to list one for each value passed, e.g.:

    string sqlStmt = "INSERT INTO MY_TABLE_T (GAME_ID, VERSION) VALUES (:x1,:x2)"
    

    Have a look at occidml.cpp in the Oracle OCCI docs for an example.