전체 테이블에서 특정 데이터를 검색하고자 합니다.
1. Sql developer에서 쿼리 실행
SELECT 'SELECT count('||COLUMN_NAME||') cnt, '''||TABLE_NAME||'.'||COLUMN_NAME||''' target, TO_CHAR('||COLUMN_NAME||') contents FROM '||TABLE_NAME||' WHERE TO_CHAR('||COLUMN_NAME||') like ''%'||'코로나'||'%''
GROUP BY '||COLUMN_NAME||'
UNION ALL' FROM USER_TAB_COLUMNS
ORDER BY TABLE_NAME,COLUMN_ID;
위 방법대로 나온 쿼리 결과물이 10,000 라인정도가 되고, 아래 2번과 같이 한번 감싸서 실행했습니다.
2. 쿼리 실행결과를 감싸서 다시 쿼리 실행(원본은 10,000 라인 정도 됨)
select * from
(
SELECT count(BBSSEQ) cnt, 'BBSATCLT.BBSSEQ' target, TO_CHAR(BBSSEQ) contents FROM BBSATCLT WHERE TO_CHAR(BBSSEQ) like '%코로나%'
GROUP BY BBSSEQ
UNION ALL
SELECT count(HBBSID) cnt, 'BBSATCLT.HBBSID' target, TO_CHAR(HBBSID) contents FROM BBSATCLT WHERE TO_CHAR(HBBSID) like '%코로나%'
GROUP BY HBBSID
UNION ALL
SELECT count(CATSEQ) cnt, 'BBSATCLT.CATSEQ' target, TO_CHAR(CATSEQ) contents FROM BBSATCLT WHERE TO_CHAR(CATSEQ) like '%코로나%'
GROUP BY CATSEQ
UNION ALL
SELECT count(GRPNUM) cnt, 'BBSATCLT.GRPNUM' target, TO_CHAR(GRPNUM) contents FROM BBSATCLT WHERE TO_CHAR(GRPNUM) like '%코로나%'
GROUP BY GRPNUM
UNION ALL
SELECT count(GRPSEQ) cnt, 'BBSATCLT.GRPSEQ' target, TO_CHAR(GRPSEQ) contents FROM BBSATCLT WHERE TO_CHAR(GRPSEQ) like '%코로나%'
GROUP BY GRPSEQ
UNION ALL
SELECT count(INDENT) cnt, 'BBSATCLT.INDENT' target, TO_CHAR(INDENT) contents FROM BBSATCLT WHERE TO_CHAR(INDENT) like '%코로나%'
GROUP BY INDENT
UNION ALL
SELECT count(PBSSEQ) cnt, 'BBSATCLT.PBSSEQ' target, TO_CHAR(PBSSEQ) contents FROM BBSATCLT WHERE TO_CHAR(PBSSEQ) like '%코로나%'
GROUP BY PBSSEQ
UNION ALL
SELECT count(SRCSEQ) cnt, 'BBSATCLT.SRCSEQ' target, TO_CHAR(SRCSEQ) contents FROM BBSATCLT WHERE TO_CHAR(SRCSEQ) like '%코로나%'
GROUP BY SRCSEQ
UNION ALL
SELECT count(CHDCNT) cnt, 'BBSATCLT.CHDCNT' target, TO_CHAR(CHDCNT) contents FROM BBSATCLT WHERE TO_CHAR(CHDCNT) like '%코로나%'
GROUP BY CHDCNT
UNION ALL
SELECT count(BTITLE) cnt, 'BBSATCLT.BTITLE' target, TO_CHAR(BTITLE) contents FROM BBSATCLT WHERE TO_CHAR(BTITLE) like '%코로나%'
GROUP BY BTITLE
UNION ALL
SELECT count(ATICLE) cnt, 'BBSATCLT.ATICLE' target, TO_CHAR(ATICLE) contents FROM BBSATCLT WHERE TO_CHAR(ATICLE) like '%코로나%'
GROUP BY ATICLE
UNION ALL
SELECT count(FILCNT) cnt, 'BBSATCLT.FILCNT' target, TO_CHAR(FILCNT) contents FROM BBSATCLT WHERE TO_CHAR(FILCNT) like '%코로나%'
GROUP BY FILCNT
UNION ALL
SELECT count(REFCNT) cnt, 'BBSATCLT.REFCNT' target, TO_CHAR(REFCNT) contents FROM BBSATCLT WHERE TO_CHAR(REFCNT) like '%코로나%'
GROUP BY REFCNT
UNION ALL
SELECT count(CMTCNT) cnt, 'BBSATCLT.CMTCNT' target, TO_CHAR(CMTCNT) contents FROM BBSATCLT WHERE TO_CHAR(CMTCNT) like '%코로나%'
GROUP BY CMTCNT
UNION ALL
SELECT count(RCMCNT) cnt, 'BBSATCLT.RCMCNT' target, TO_CHAR(RCMCNT) contents FROM BBSATCLT WHERE TO_CHAR(RCMCNT) like '%코로나%'
GROUP BY RCMCNT
UNION ALL
SELECT count(PROCST) cnt, 'BBSATCLT.PROCST' target, TO_CHAR(PROCST) contents FROM BBSATCLT WHERE TO_CHAR(PROCST) like '%코로나%'
GROUP BY PROCST
UNION ALL
SELECT count(BBSSDT) cnt, 'BBSATCLT.BBSSDT' target, TO_CHAR(BBSSDT) contents FROM BBSATCLT WHERE TO_CHAR(BBSSDT) like '%코로나%'
GROUP BY BBSSDT
UNION ALL
SELECT count(BBSEDT) cnt, 'BBSATCLT.BBSEDT' target, TO_CHAR(BBSEDT) contents FROM BBSATCLT WHERE TO_CHAR(BBSEDT) like '%코로나%'
GROUP BY BBSEDT
UNION ALL
SELECT count(BBSNTC) cnt, 'BBSATCLT.BBSNTC' target, TO_CHAR(BBSNTC) contents FROM BBSATCLT WHERE TO_CHAR(BBSNTC) like '%코로나%'
GROUP BY BBSNTC
UNION ALL
SELECT count(BBSOPN) cnt, 'BBSATCLT.BBSOPN' target, TO_CHAR(BBSOPN) contents FROM BBSATCLT WHERE TO_CHAR(BBSOPN) like '%코로나%'
GROUP BY BBSOPN
UNION ALL
SELECT count(IMGURL) cnt, 'BBSATCLT.IMGURL' target, TO_CHAR(IMGURL) contents FROM BBSATCLT WHERE TO_CHAR(IMGURL) like '%코로나%'
GROUP BY IMGURL
UNION ALL
SELECT count(REGSNM) cnt, 'BBSATCLT.REGSNM' target, TO_CHAR(REGSNM) contents FROM BBSATCLT WHERE TO_CHAR(REGSNM) like '%코로나%'
GROUP BY REGSNM
UNION ALL
SELECT count(MODDAT) cnt, 'BBSATCLT.MODDAT' target, TO_CHAR(MODDAT) contents FROM BBSATCLT WHERE TO_CHAR(MODDAT) like '%코로나%'
GROUP BY MODDAT
UNION ALL
SELECT count(REGDAT) cnt, 'BBSATCLT.REGDAT' target, TO_CHAR(REGDAT) contents FROM BBSATCLT WHERE TO_CHAR(REGDAT) like '%코로나%'
GROUP BY REGDAT
UNION ALL
SELECT count(MBRPID) cnt, 'BBSATCLT.MBRPID' target, TO_CHAR(MBRPID) contents FROM BBSATCLT WHERE TO_CHAR(MBRPID) like '%코로나%'
GROUP BY MBRPID
UNION ALL
SELECT count(CITINO) cnt, 'BBSATCLT.CITINO' target, TO_CHAR(CITINO) contents FROM BBSATCLT WHERE TO_CHAR(CITINO) like '%코로나%'
GROUP BY CITINO
UNION ALL
SELECT count(PASSWD) cnt, 'BBSATCLT.PASSWD' target, TO_CHAR(PASSWD) contents FROM BBSATCLT WHERE TO_CHAR(PASSWD) like '%코로나%'
GROUP BY PASSWD
UNION ALL
SELECT count(TELNUM) cnt, 'BBSATCLT.TELNUM' target, TO_CHAR(TELNUM) contents FROM BBSATCLT WHERE TO_CHAR(TELNUM) like '%코로나%'
GROUP BY TELNUM
UNION ALL
SELECT count(MEMAIL) cnt, 'BBSATCLT.MEMAIL' target, TO_CHAR(MEMAIL) contents FROM BBSATCLT WHERE TO_CHAR(MEMAIL) like '%코로나%'
GROUP BY MEMAIL
UNION ALL
SELECT count(BIZTYPE) cnt, 'BBSATCLT.BIZTYPE' target, TO_CHAR(BIZTYPE) contents FROM BBSATCLT WHERE TO_CHAR(BIZTYPE) like '%코로나%'
GROUP BY BIZTYPE
UNION ALL
SELECT count(REQTYPE) cnt, 'BBSATCLT.REQTYPE' target, TO_CHAR(REQTYPE) contents FROM BBSATCLT WHERE TO_CHAR(REQTYPE) like '%코로나%'
GROUP BY REQTYPE
)
where cnt > 0;
---------------------------------------------------
3. 오류 발생
ORA-00932: 일관성 없는 데이터 유형: -이(가) 필요하지만 CLOB임
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
33행, 14열에서 오류 발생
전체 테이블에서 특정 데이터를 검색하고 싶은데 검색된 다른 정보들은 이해가 되지않아 적용하기 힘드네요.
더 쉬운 방법 있으면 알려주세요.
|