set cursor_sharing=force caused the app break

[hannah00]

Hi all,

when I set cursor_sharing=force, some of the functionality of the app break and I am runnning on 9i, below is the statement that would break on the app:

SELECT sum(PCT_INT) TOTRI FROM PARTY_INTERESTS WHERE GROUNP_NUM = @GROUP " + _
            " AND PIP_TYPE_CODE IN ('RI', 'ROI') "

 

 

"SELECT GROUNP_NUM, SUM(PCT_INT) TOTRI FROM PARTY_INTERESTS" + _
            "WHERE GROUNP_NUM IN (SELECT GROUNP_NUM FROM TEAM WHERE AGMT_NUM = @AGMT_NUM AND SUBS_NUM <> '000') " + _
            "AND PIP_TYPE_CODE IN ('RI', 'ROI') " + _
            "GROUP BY GROUNP_NUM"

the statement executed ok from SQLPLUS; however, it will brek from the app. why????

[marist89]

ok, what's the error?

[stmontgo]

Originally posted by hannah00
Hi all,

when I set cursor_sharing=force, some of the functionality of the app break and I am runnning on 9i, below is the statement that would break on the app:

SELECT sum(PCT_INT) TOTRI FROM PARTY_INTERESTS WHERE GROUNP_NUM = @GROUP " + _
            " AND PIP_TYPE_CODE IN ('RI', 'ROI') "


 

 

"SELECT GROUNP_NUM, SUM(PCT_INT) TOTRI FROM PARTY_INTERESTS" + _
            "WHERE GROUNP_NUM IN (SELECT GROUNP_NUM FROM TEAM WHERE AGMT_NUM = @AGMT_NUM AND SUBS_NUM <> '000') " + _
            "AND PIP_TYPE_CODE IN ('RI', 'ROI') " + _
            "GROUP BY GROUNP_NUM"

the statement executed ok from SQLPLUS; however, it will brek from the app. why????

don't do that then

[tamilselvan]

cursor_sharing = force

will not work always as you expected because the lengths of columns returned to the application will vary. For example, the query expected varchar2(5) but oracle may return 30 characters. That's why your application failed.

You should not use "FORCE" in your prod env.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select substr(object_name,1,4) xxx from dba_objects where rownum < 3;

XXX
--------------------------------------------------------------------------------
/100
/100

SQL> alter session set cursor_sharing=exact ;

Session altered.

SQL> select substr(object_name,1,4) xxx from dba_objects where rownum < 3;

XXX
----
/100
/100 

Tamil