'\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 쿼리도 공부해야겠음 ㅋㅋ
