ORA-00600: 内部错误代码, 参数: [kkoipt:invalid join method]

分页查询要注意

 

SQL:

 

SELECT t2.ID FROM(    SELECT t1.ID, ROWNUM rnum FROM(    
SELECT t0.ID From(        
SELECT     p.ID,MAX(pa.GMT_CREATE) as gmt_create   FROM mkt_product p, mkt_product_activity pa, mkt_activity a   WHERE p.ID = pa.MARKETING_PRODUCT_ID AND pa.MARKETING_ACTIVITY_ID = a.ID AND a.is_valid = 1             
AND   p.scm_industry is null       GROUP BY p.id         )t0 
order by t0.gmt_create desc nulls last     ) t1       WHERE ROWNUM <= 5) t2   WHERE rnum >= 0    

 

其中ROWNUM<5改为ROWNUM<20则可运行，若为5则报错

 

数据库报错：

 

org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [60000]; error code [600];   
--- The error occurred while applying a parameter map.  
--- Check the marketingProductActivity.getApplyProductIDListByQuery-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: ORA-00600: 内部错误代码, 参数: [kkoipt:invalid join method], [1], [0], [], [], [], [], [], [], [], [], []
; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred while applying a parameter map.  
--- Check the marketingProductActivity.getApplyProductIDListByQuery-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: ORA-00600: 内部错误代码, 参数: [kkoipt:invalid join method], [1], [0], [], [], [], [], [], [], [], [], []

 

 

 

解决：

是由于分页SQL中第二层没有加上ROWNUM，加上即可：

 

SELECT t2.ID FROM(    SELECT t1.ID, ROWNUM rnum FROM(    
SELECT t0.ID, ROWNUM From(        
SELECT  p.ID,MAX(pa.GMT_CREATE) as gmt_create   FROM mkt_product p, mkt_product_activity pa, mkt_activity a   WHERE p.ID = pa.MARKETING_PRODUCT_ID AND pa.MARKETING_ACTIVITY_ID = a.ID AND a.is_valid = 1     AND     p.scm_industry is null   GROUP BY p.id         )t0 
order by t0.gmt_create desc nulls last     ) t1       WHERE ROWNUM <= 5) t2   WHERE rnum >= 0