|
1. start with b.deptcode = 'AA' 작업하면 Update 되는데
a.n_busecd 처럼 칼럼명을 넣으면 오류발생.
update msp_destination_test a
set a.n_busename = (
select aa from
( select b.up_deptname aa from tobe_dept_code b
start with b.deptcode = 'AA' -- a.n_busecd
connect by b.deptcode = prior b.up_deptcode
and b.lvl = '5'
ORDER BY LEVEL DESC ) b
where rownum = 1)
2. 위 문제를 해결하려고 PROCEDURE 작성했는데
tdept PLS-00049: bad bind variable 오류 발생합니다.
고수님의 도움 부탁드립니다.
Start With 의 문장에 제약이 있는 것인지, 구문 오류인지 도움 부탁드립니다.
CREATE OR REPLACE procedure KOSMOS_OCS.PROC_TODEPT_AA
IS
tdept VARCHAR2(20);
CURSOR C1
IS
SELECT n_busecd
FROM destination_test
where n_busecd is not null;
BEGIN
OPEN C1;
LOOP
FETCH C1 into tdept;
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line(tdept); -- 정상으로 나옴.
update msp_destination_test a
set a.n_busename = ( select aa from
( select b.up_deptname aa from tobe_dept_code b
start with TRIM(b.deptcode) = TRIM(:tdept) -- ERROR
connect by b.deptcode = prior b.up_deptcode
and b.lvl = '5'
ORDER BY LEVEL DESC ) b
where rownum = 1)
where A.N_BUSECD is not null ;
END LOOP;
CLOSE C1;
COMMIT;
END;
|
