DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: set cursor_sharing=force caused the app break

  1. #1
    Join Date
    Jan 2005
    Posts
    221

    set cursor_sharing=force caused the app break

    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:

    Code:
    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????
    Last edited by hannah00; 04-25-2005 at 02:03 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ok, what's the error?
    Jeff Hunter

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: set cursor_sharing=force caused the app break

    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:

    Code:
    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
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

    PHP Code:
    SQLalter session set cursor_sharing=force;

    Session altered.

    SQLselect substr(object_name,1,4xxx from dba_objects where rownum 3;

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

    SQL
    alter session set cursor_sharing=exact ;

    Session altered.

    SQLselect substr(object_name,1,4xxx from dba_objects where rownum 3;

    XXX
    ----
    /
    100
    /100 
    Tamil
    Last edited by tamilselvan; 04-25-2005 at 07:14 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width