Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: ORA-07445: exception encountered with CASE statement

    I recently upgraded to 10g from 9i by installing 10g on a new system (hosted in VMWare) and imported the 9i export file. Everything worked fine before, and most of my application hasn't had any trouble with 10g. However, I keep getting a core dump with a specific query.

    In particular, it seems to be the CASE statement that it is objecting to in combination with using a select count(*) of a subquery. The use of select count(*) from (subquery) syntax works fine for other subqueries and the subquery in my statement works fine by itself, but combined together causes the error.

    Note: I realize the sql is not the most elegant and could be written shorter and thus not generate an error, but it serves other purposes in the application.

    I'm not sure what parts of the log are important, but I've tried to inlcude the items that look like they might have the more pertinent information.

    Any help in resolving this would be greatly appreciated.

    Code:
    Sat Jun 27 09:09:37 2009
    ORACLE V10.2.0.1.0 - Production vsnsta=0
    vsnsql=14 vsnxtr=3
    Oracle Database 10g Release 10.2.0.1.0 - Production
    Windows Server 2003 Version V5.2 Service Pack 1
    CPU                 : 1 - type 586, 1 Physical Cores
    Process Affinity    : 0x00000000
    Memory (Avail/Total): Ph:481M/1023M, Ph+PgF:1279M/1705M, VA:1695M/2047M
    Instance name: orcl
    
    Redo thread mounted by this instance: 1
    
    Oracle process number: 21
    
    Windows thread id: 3584, image: ORACLE.EXE (SHAD)
    
    
    *** ACTION NAME:(SQL Window - select count(*) fro) 2009-06-27 09:09:37.046
    *** MODULE NAME:(PL/SQL Developer) 2009-06-27 09:09:37.046
    *** SERVICE NAME:(orcl) 2009-06-27 09:09:37.046
    *** SESSION ID:(131.224) 2009-06-27 09:09:37.046
    *** 2009-06-27 09:09:37.046
    ksedmp: internal or fatal error
    ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_nsoexc+145] [PC:0x1DBD355] [ADDR:0x120] [UNABLE_TO_READ] []
    Current SQL statement for this session:
    select count(*)
      from (SELECT U.*,
    			app.lastDateApproved,
    			(CASE
    				WHEN EXISTS (SELECT userID
    					   FROM DRUser m
    					  WHERE m.managerID = u.userID) THEN
    				 1
    				ELSE
    				 0
    			END) as hasMembers,
    			fd.description as empStatus
    		FROM DRUserFull U
    		LEFT JOIN drFieldDecode fd ON u.state = fd.abbr
    		LEFT JOIN (select userid, max(approvalDate) as lastDateApproved
    				  from fm_LAPlan_approval_history
    				 group by userid) app ON U.userid = app.userid
    	    WHERE U.managerID = 21
    		 AND fd.field = 'DRUser.state'
    		 AND U.state IN ('A', 'L')
    	    ORDER BY lastName, firstName)
    ----- Call Stack Trace -----
    ...
    ----- End of Call Stack Trace -----
    ===================================================
    PROCESS STATE
    -------------
    Process global information:
         process: 6D24E99C, call: 6D35FBEC, xact: 00000000, curses: 6D31E434, usrses: 6D31E434
      ----------------------------------------
      SO: 6D24E99C, type: 2, owner: 00000000, flag: INIT/-/-/0x00
      (process) Oracle pid=21, calls cur/top: 6D35FBEC/6D35FD80, flag: (0) -
                int error: 0, call error: 0, sess error: 0, txn error 0
      (post info) last post received: 0 0 0
                  last post received-location: No post
                  last process to post me: none
                  last post sent: 0 0 0
                  last post sent-location: No post
                  last process posted by me: none
        (latch info) wait_event=0 bits=0
        Process Group: DEFAULT, pseudo proc: 6D27F358
        O/S info: user: SYSTEM, term: serverdev1, ospid: 3584
        OSD pid info: Windows thread id: 3584, image: ORACLE.EXE (SHAD)
    Dump of memory from 0x6D23A968 to 0x6D23AAEC
    6D23A960                   00000004 6C4F0250          [....P.Ol]
    6D23A970 00000010 0003139D 6D35FD80 00000003  [..........5m....]
    6D23A980 0003139D 6C8AD8A8 0000000B 0003139D  [.......l........]
    6D23A990 6D31E434 00000004 00031291 00000000  [4.1m............]
    6D23A9A0 00000000 00000000 00000000 00000000  [................]
            Repeat 19 times
    6D23AAE0 00000000 00000000 00000000           [............]    
        ----------------------------------------
        SO: 6D31E434, type: 4, owner: 6D24E99C, flag: INIT/-/-/0x00
        (session) sid: 131 trans: 00000000, creator: 6D24E99C, flag: (41) USR/- BSY/-/-/-/-/-
                  DID: 0001-0015-00000015, short-term DID: 0000-0000-00000000
                  txn branch: 00000000
                  oct: 3, prv: 0, sql: 66A645FC, psql: 669AED44, user: 55/J2DEVL
        O/S info: user: serverdev1\robertt, term: RTLAPTOP2, ospid: 2984:5508, machine: WORKGROUP\RTLAPTOP2
                  program: plsqldev.exe
        application name: PL/SQL Developer, hash value=1190136663
        action name: SQL Window - select count(*) fro, hash value=88848990
        last wait for 'SQL*Net message from client' blocking sess=0x00000000 seq=28 wait_time=1539 seconds since wait started=0
                    driver id=54435000, #bytes=1, =0
        Dumping Session Wait History
         for 'SQL*Net message from client' count=1 wait_time=1539
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message to client' count=1 wait_time=6
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message from client' count=1 wait_time=102721
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message to client' count=1 wait_time=7
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message from client' count=1 wait_time=5644
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message to client' count=1 wait_time=7
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message from client' count=1 wait_time=10510
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message to client' count=1 wait_time=6
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message from client' count=1 wait_time=8345
                    driver id=54435000, #bytes=1, =0
         for 'SQL*Net message to client' count=1 wait_time=7
                    driver id=54435000, #bytes=1, =0
        temporary object counter: 0

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
    Oracle bug requiring you to submit a Service Request to Metalink.
    Last edited by anacedent; 06-28-09 at 22:26.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Posts
    184
    Thanks. Unfortunately, since I'm not developing for internal use, I'm only using the free version which doesn't come with support. I've put in a request for a price quote for support, but I suspect the cost will far outweigh the use of 10g and I'll have to switch back to 9i.

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Use Decode inplace of CASE

    SELECT U.*,
    app.lastDateApproved,
    (SELECT DECODE(userId,null,0,1)
    FROM DRUser m
    WHERE m.managerID = u.userID) as hasMembers,
    fd.description as empStatus

  5. #5
    Join Date
    May 2004
    Posts
    184
    Carloa,

    Thanks. Unfortunately, this would mean a release of new code to the customer (who isn't having the problem in their environments), when it appears to be a configuration/installation problem on my end.

    Thanks.

Posting Permissions

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