Simple ODPI-C statement execution example gives ORA-00922

Question

I'm trying to execute simple sql query using ODPI-C

dpiStmt* stmt;
const char* query = "CREATE TABLE schema_name.Z_TEST(\n"
                    "    person_id NUMBER NOT NULL,\n"
                    "    first_name VARCHAR2(50) NOT NULL,\n"
                    "    last_name VARCHAR2(50) NOT NULL,\n"
                    "    PRIMARY KEY(person_id)\n"
                    ");";
dpiConn_prepareStmt(conn.connection_, 0, query, strlen(query), nullptr,0, &stmt);

if(dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, nullptr) == DPI_FAILURE)
{

    throw std::runtime_error(get_context_error_string(conn.db_context_));
}

But it comes up with an error

ERROR: ORA-00922: missing or invalid option (dpiStmt_execute : execute)

Where

• conn is a struct which has dpiConn* connection_.(tested with dpiConn_ping)
• get_context_error_string(dpiContext*); returns last error of context, using dpiErrorInfo.

I hope you can help me.

P.S. the website didn't allow me to create new tag ODPI-C

EDIT: I checked everything about schema name and context/connection existence

Answer 1

The DDL statement should not have a semicolon at the end. That is a client statement separator, not part of the statement. Change it to:

const char* query = "CREATE TABLE schema_name.Z_TEST(\n"
                    "    person_id NUMBER NOT NULL,\n"
                    "    first_name VARCHAR2(50) NOT NULL,\n"
                    "    last_name VARCHAR2(50) NOT NULL,\n"
                    "    PRIMARY KEY(person_id)\n"
                    ")";

In some circumstances a stray semicolon can cause an ORA-00911 error rather than an ORA-00922 error.