I have a situation where I have two 8.1.6 databases which are configured to be identical. An application I am running on top of the database is issuing SQLs to both of the databases, and I ran into a case where a statement ran okay against one of the databases but not the other. I snipped out the SQL statement and ran it in SQL*Plus to find that it would run against one sucessfully, but fail against the other. Both of the databases have the same tables and columns, etc.
The situation was the statement was an update which did not have a space after the literal string and the where clause, and the statement was essentially this: update emp set emp_lname = 'Jones'where emp_id='34523'; Note the missing space between 'Jones' and the 'where' in the following clause. This statement ran against one of the the 8.1.6 databases in SQL*Plus, but then failed against the other with the error 'ORA-00933: SQL Command not properly ended'
Can this be a parameter on one DB which is affecting this?
Found the answer!!!
One of the servers had the parameter cursor_sharing set to FORCE, which apparently caused it to not like the fact that there was not space between the literal string and the where clause. The cursor_sharing parameter is new to 8i, and apparently allows Oracle to use bind variables wherever it sees fit to prevent reparsing of slightly different SQLs.
Click Here to Expand Forum to Full Width