Printable View
We have a Visual Basic application that uses ODBC to connect to the database. When the database has been up and running for a few months we start getting problems using ODBC. On connecting to the database we get numeric value errors, but we can connect via sqlplus OK. We don't get any errors from the alert.log.
We have found that if I issue the command 'alter system flush shared pool;', or stop/start the database, the errors disappear. Does anyone have any ideas? Thanks.
Are you using bind variables? This may have a bearing on your system.
People often cheat and hardcode SQL when sending it through ODBC like:
rs.Open "SELECT * FROM my_table WHERE id = " & vId, conn
instead of using a command object and using a parameterized query. This approach will fill the shared pool with almost identicle copies of the same statement. If you use bind variables your shared pool will become more efficient and you'll save loads of CPU.
Since rewriting your application my take some time you might want to consider using:
cursort_sharing=force
This is not a complete fix.
Cheers