데이터베이스 질문과 답변
특정 문자열이 들어가는 컬럼을 찾고 싶습니다.(유저, 테이블, 컬럼명)
이전에 비슷한 계시물을 봐서 그것을 토대로 짜보았는데
dba 권한을 가진 한 유저에서 전체를 찾고자 합니다.
아래와 같이 짜보았는데 계속 에러가 나네요 무엇이 문제인지 알려주셨으면 합니다.
_________________________________________________________________________________________________________________
create or replace function get_colnames(puser varchar2, p_tablename varchar2, p_keyword varchar2)
return varchar2 is
v_colnames varchar2(30000);
begin
select nvl(wm_concat(COLUMN_NAME),'no data found')
into v_colnames
from
(
select COLUMN_NAME
from dba_tab_columns
WHERE OWNER = upper(p_user) AND TABLE_NAME = upper(p_tablename)
and to_number(
dbms_xmlgen.getxmltype(
'select 1,2,3 from '||p_user||''.''||TABLE_NAME||' where '||COLUMN_NAME||' like ''%'||p_keyword||'%'' and rownum = 1'
).extract('//text()')
) = 1
order by COLUMN_NAME
);
return v_colnames;
end;
_________________________________________________________________________________________________________________
SELECT get_colnames('유저명,'테이블명','A`') FROM dual;
에러 메시지를 같이 적어주시면 더 찾기 쉽겠네요~
일단 눈에 보이는 거는
puser <-> p_user
select 1,2,3 -> select 1
그리고 컬럼 데이터 타입에 따라서 런타임 에러도 날 수 있습니다.
조회하는 키워드가 날짜형인지, 숫자형인지, 문자형인지도 같이 특정되어야 오류를 피할 수 있겠습니다.
create or replace function get_colnames(p_user varchar2, p_tablename varchar2, p_keyword varchar2)
return varchar2 is
v_colnames varchar2(30000);
begin
select nvl(wm_concat(COLUMN_NAME),'no data found')
into v_colnames
from
(
select COLUMN_NAME
from dba_tab_columns
WHERE OWNER = upper(p_user) AND TABLE_NAME = upper(p_tablename)
and to_number(
dbms_xmlgen.getxmltype(
'select 1 from '||p_user||''.''||TABLE_NAME||' where '||COLUMN_NAME||' like ''%'||p_keyword||'%'' and rownum = 1'
).extract('//text()')
) = 1
order by COLUMN_NAME
);
return v_colnames;
end;
수정하였습니다 에러메세지는 아래와 같습니다.
6/3 PL/SQL: SQL Statement ignored
15/46 PL/SQL: ORA-00907: missing right parenthesis
메시지만 보면 우괄호가 누락되었다는 건데.. 어디가 누락된 거죠?
'||p_user||''.''||TABLE_NAME
-> '||p_user||'.'||TABLE_NAME
프로젝트 나와있다보니 빌드권한이 없어서 직접 못 해보겠네요ㅎㅎ;
create or replace function get_colnames(p_user varchar2, p_tablename varchar2, p_keyword varchar2)
return varchar2 is
v_colnames varchar2(30000);
begin
select nvl(wm_concat(COLUMN_NAME),'no data found')
into v_colnames
from
(
select COLUMN_NAME
from dba_tab_columns
WHERE OWNER = upper(p_user) AND TABLE_NAME = upper(p_tablename)
and to_number(
dbms_xmlgen.getxmltype(
'select 1 from '||p_user||'.'||TABLE_NAME||' where '||COLUMN_NAME||' like ''%'||p_keyword||'%'' and rownum = 1'
).extract('//text()')
) = 1
order by COLUMN_NAME
);
return v_colnames;
end;
/
이걸로 빌드하니
sysdba로 접속하니 생성이 되는데 dba계정으로 접속했을땐 테이블이 존재하지 않다고 나오네요
dba_tab_columns 테이블을 대체할만한 테이블이 모 없을까요?
all_tab_columns?
1. 구문 오류는 따옴표 사용이 부적절해서 난거구요. 이부분(''.'')
2. 기타 오류는 다음 URL 댓글 참조하세요. - http://gurubee.net/article/61164
3. DBA_ 대체 정보는 ALL_
create or replace function get_colnames(p_user varchar2, p_tablename varchar2, p_keyword varchar2)
return varchar2 is
v_colnames varchar2(30000);
begin
select nvl(wm_concat(COLUMN_NAME),'no data found')
into v_colnames
from
(
select COLUMN_NAME
from ALL_TAB_COLUMNS
WHERE OWNER = upper(p_user) AND TABLE_NAME = upper(p_tablename)
and to_number(
dbms_xmlgen.getxmltype(
'select 1 from '||p_user||'.'||TABLE_NAME||' where '||COLUMN_NAME||' like ''%'||p_keyword||'%'' and rownum = 1'
).extract('//text()')
) = 1
order by COLUMN_NAME
);
return v_colnames;
end;
/
알려주신데로 하니까 잘 생성까진 됩니다.
하지만 같은 계정의 데이터는 다른 계정 테이블을 검사할때는 조회가 되지 않습니다.
dba계정이라 될것 같았는데 모가 문제일까요?
1. 구문 오류 외에도 기타 예상치 못한 오류가 발생 될 가능성이 있으니
- CHAR 타입의 컬럼만 걸러내는 조건이 추가되어야 할 것 같네요.
- http://gurubee.net/article/61164
2. wm_concat 은 비공식 함수입니다.
- LISTAGG 사용하세요.
- http://gurubee.net/article/55512
3. 조회가 되지 않는다?
- 조회 결과가 없는 건지? 에러가 나는 건지?
- 조회 결과가 없다면 왜 없는지 확인해 보셔야 하구요.
- 에러가 난다면? 왜 에러가 나는지 확인해 보셔야 합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.