'\n' 이 문자열 그대로의 의미가 아닌 엔터(줄바꿈)를 말씀하시는 거라면?
CHR(13) || CHR(10) 을 찾아 바꾸시면 됩니다.
http://www.oracleclub.com/article/49759
생성하고자 하는 DB LINK의 password에 마지막 문자에 !가 있는 경우 windows cmd 창에서 SQL*Plus로는 에러가 나는 경우가 있습니다.
Unix 환경에서도 ""로 감싸줘야 에러없이 생성가능합니다.
Unix 환경에서도 ""로 감싸줘야 에러없이 생성가능합니다.
CREATE PUBLIC DATABASE LINK db_link_nm CONNECT TO id IDENTIFIED BY "password!" USING db_service_nm;
WITH test AS ( SELECT '40' code, 30 cnt FROM dual UNION ALL SELECT '30', 50 FROM dual UNION ALL SELECT '10', 20 FROM dual ) SELECT code , SUM(cnt) cnt , RATIO_TO_REPORT(SUM(cnt)) OVER(PARTITION BY GROUPING(code)) * 100 ratio FROM test GROUP BY ROLLUP(code) ;
column viewname format a100 with tab as ( select '0001' code, 'A' name from dual union all select '0001' code, 'B' name from dual union all select '0001' code, 'C' name from dual union all select '0002' code, 'D' name from dual union all select '0002' code, 'A' name from dual union all select '0003' code, 'F' name from dual union all select '0003' code, 'E' name from dual union all select '0003' code, 'B' name from dual union all select '0003' code, 'C' name from dual ) select x.code ,substr(max(sys_connect_by_path(x.name,', ')),2) viewname from ( select code ,name ,row_number() over (partition by code order by code ,name) rn from tab ) x start with x.rn = 1 connect by prior x.code = x.code and prior x.rn = x.rn - 1 group by x.code /connect by 앞에서 항상 약해지는 나 ㅡ.ㅜ
with tab as ( select '0001' code, 'A' name from dual union all select '0001' code, 'B' name from dual union all select '0001' code, 'C' name from dual union all select '0002' code, 'D' name from dual union all select '0002' code, 'A' name from dual union all select '0003' code, 'F' name from dual union all select '0003' code, 'E' name from dual union all select '0003' code, 'B' name from dual union all select '0003' code, 'C' name from dual ) select x.code ,substr(xmlagg(xmlelement(x,' ,',x.name).extract('//text()')),3) viewnames from tab x group by x.code /connect by 안쓰고 같은 효과내는 방법 xml 쿼리도 공부해야겠음 ㅋㅋ