1 Reply Latest reply: Apr 3, 2014 3:30 PM by DanOtto RSS

    Problems with statement cache using OCI

    DanOtto

      Hello!

       

      We recently changed our program to use statement cache, but we found a problem and not yet a solution.

       

      We have problems in this situation:

       

      OCIEnvCreate();

      OCIHandleAlloc();

      OCILogon2(..... OCI_LOGON2_STMTCACHE);

       

      OCIStmtPrepare2("CREATE TABLE db_testeSP (cod_usuario INTEGER, usuario CHAR(20), dat_inclusao DATE)")

      OCIStmtExecute();

      OCIStmtRelease(... OCI_DEFAULT);

       

      OCIStmtPrepare2("INSERT INTO db_testeSP (1,\'user\',CURRENT_DATE");

      OCIStmtExecute();

      OCIStmtRelease(... OCI_DEFAULT);

       

      OCIStmtPrepare2("SELECT * FROM db_testeSP");

      OCIStmtExecute();

      OCIStmtRelease(... OCI_DEFAULT);

       

      OCIStmtPrepare2("DROP TABLE db_testeSP");

      OCIStmtExecute();

      OCIStmtRelease(... OCI_DEFAULT);

       

      OCIStmtPrepare2("CREATE TABLE db_testeSP (cod_usuario INTEGER, usuario CHAR(20), idade INTEGER, dat_inclusao DATE)");

      OCIStmtExecute();

      OCIStmtRelease(... OCI_DEFAULT);

       

      OCIStmtPrepare2("INSERT INTO db_testeSP (1,\'user\',20,CURRENT_DATE");

      OCIStmtExecute();

      OCIStmtRelease(... OCI_DEFAULT);

       

      OCIStmtPrepare2("SELECT * FROM db_testeSP");

      OCIStmtExecute();

      OCIStmtRelease(... OCI_DEFAULT);

       

      On the second Select (wich is in bold), returns -1 from Execute, and if I get the error with OCIErrorGet I have: ORA-00932 - inconsistent datatypes

       

      Researching I discovered that this is statement cache problem, is there a way to clear the cache of one table ? I'm asking this because I could clear whenever there is a DROP TABLE or ALTER TABLE instruction (but I don't know what statements will need to be cleared from the cache). I can't clear all the cache because I may have other statements from other tables on the cache.

       

      This situation above is just an example, but I think that this will cause other problems too.

       

      Our program is a gateway from the main program and database, so I don't know the SQL instructions before executing. How can we resolve this problem?

       

      I have tested this issue with Oracle 10g (10.2.0.4.0) and 11g (11.2.0.1.0) both 64 bits and the result is the same (the OCI is version 11.2.0).

       

      We appreciate any help.

      Thanks in advance,

      Daniel