CURSOR_SHARING=FORCE

[Babette Turner-Underwood]

We have noticed an interesting side "effect" of using CURSOR_SHARING=force. When using SQL (simple INSERT, UPDATE, SELECT...), and you check v$SQLAREA you see that yes, Oracle indeed replaced hard-coded values with bind variables TRY: SELECT DEPTNO, LOC from DEPT where LOC='Boston'; select sql_text from v$sqlarea where sql_text like 'SELECT DEPTNO'; BUT this does not work for parameters to procedures or functions. TRY: create procedure upd_dept ( in_deptno number, in_loc varchar2) begin    update dept set loc = in_loc; end; exec upd_dept ( 20, 'BOSTON'); select sql_text from v$sqlarea where sql_text like '%upd_dept%'; Thus we need to change calling our table APIs from : upd_dept( 20, 'BOSTON'); to define my_deptno := 20; define my_location := 'BOSTON'; upd_dept( :my_deptno, :my_location); To use bind variables. Thus making extensive use of table APIs will have multiple copies of SQL in shared pool UNLESS done this way and CURSOR_SHARING has no effect. According to Oracle, it is how it is supposed to work, but we were not expecting the behaviour. Just another one of those pleasant surprises from Oracle :-) Babette [EMAIL PROTECTED]