Oracle ORA-22922错误

PLSQL中执行下面SQL没有问题,但是在Java程序运行时报错：

2013/11/05-23:03:04 >>  WARN >> http-8080-4 >> org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:100) >> SQL Error: 22922, SQLState: 99999
2013/11/05-23:03:04 >> ERROR >> http-8080-4 >> org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:101) >> ORA-22922: nonexistent LOB value

select bigtab.qsh    as qsh4439_0_,
               bigtab.sslh   as sslh4439_0_,
               bigtab.njs    as njs4439_0_,
               bigtab.yxshs  as yxshs4439_0_,
               bigtab.yxsmcs as yxsmcs4439_0_,
               bigtab.bhs    as bhs4439_0_,
               bigtab.bjmcs  as bjmcs4439_0_,
               bigtab.fdyxms as fdyxms4439_0_,
               bigtab.fdyfs  as fdyfs4439_0_,
               bigtab.xscfs  as xscfs4439_0_
          from (select b.sslh as sslh,
                       b.qsh as qsh,
                       to_char(WMSYS.WM_CONCAT(distinct x.nj)) as njs,
                       to_char(WMSYS.WM_CONCAT(distinct x.yxsh)) as yxshs,
                       to_char(WMSYS.WM_CONCAT(distinct y.YXSMC)) as yxsmcs,
                       to_char(WMSYS.WM_CONCAT(distinct x.bh)) as bhs,
                       to_char(WMSYS.WM_CONCAT(distinct bj.bjmc)) as bjmcs,
                       to_char(WMSYS.WM_CONCAT(distinct f.xm)) as fdyxms,
                       to_char(WMSYS.WM_CONCAT(distinct q.fdyfs)) as fdyfs,
                       to_char(WMSYS.WM_CONCAT(distinct q2.xscfs)) as xscfs
                  from XSSSXXB b,
                       (select t.sslh, t.qsh, t.pysj, t.fdyfs, t.xscfs
                          from QSXSPY t
                         where t.ZT = '1'
                           and t.PYSJ_FDY >=
                               to_date('2013-10-01', 'yyyy-MM-dd')
                           and t.PYSJ_FDY <=
                               to_date('2013-11-05', 'yyyy-MM-dd')) q,
                       (select t.sslh, t.qsh, t.pysj, t.fdyfs, t.xscfs
                          from QSXSPY t
                         where t.XSCZT = '2'
                           and t.PYSJ_XSC >=
                               to_date('2013-10-01', 'yyyy-MM-dd')
                           and t.PYSJ_XSC <=
                               to_date('2013-11-05', 'yyyy-MM-dd')) q2,
                       xsjbxx x,
                       bjfdyxx bf,
                       fdyjbxx f,
                       yxxx y,
                       bjjbxx bj
                 where bf.fdybh = f.zgh
                   and b.xh = x.xh
                   and x.bh = bf.bh
                   and b.sslh = q.sslh(+)
                   and b.qsh = q.qsh(+)
                   and b.sslh = q2.sslh(+)
                   and b.qsh = q2.qsh(+)
                   and y.YXSH = x.YXSH
                   and bj.bh = x.BH
                 group by b.sslh, b.qsh) bigtab
         where (bigtab.fdyfs is not null or bigtab.xscfs is not null)

 

分析原因：oracle自带的行转列函数WM_CONCAT导致的，这个函数会把varchar的字段合为lob。

解决方式：重写了一个列转行的函数

create or replace TYPE zh_concat_im
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
SCTX2 IN zh_concat_im) RETURN NUMBER
);
/
create or replace TYPE BODY zh_concat_im
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
RETURN NUMBER
IS
BEGIN
SCTX := zh_concat_im(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
SCTX2 IN zh_concat_im)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
函数：
create or replace FUNCTION zh_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;
/

效率不如WM_CONCAT但是还是解决了这个问题！