재사용성을 높이기 위해 프로시저+커서 사용중 ORA-00911: 문자가 부적합합니다 에러발생 0 16 338

by 춤과파티 [PL/SQL] [2018.03.08 09:03:23]


1.테이블 생성

CREATE TABLE PLANT_CHANGE_TABLE /*바뀌는 엑셀 데이터 테이블*/
(
NEW_COMPANY_CODE VARCHAR2(10);
NEW_PLANT_CODE VARCHAR2(10);
OLD_PLANT_CODE VARCHAR2(10);
);

2.프로시저 생성

create or replace PROCEDURE ​PLANT_CHANGE_PROCEDURE 
(
TABLE_NAME VARCHAR2(50)
)
IS

CURSOR PLANT_CHANGE_CURSOR IS

SELECT NEW_COMPANY_CODE, NEW_PLANT_CODE, OLD_PLANT 
FROM PLANT_CHANGE_TABLE;

AAA PLANT_CHANGE_TABLE.NEW_COMPANY_CODE%TYPE;
BBB PLANT_CHANGE_TABLE.NEW_PLANT_CODE%TYPE;
CCC PLANT_CHANGE_TABLE.OLD_PLANT_CODE%TYPE;

BEGIN
OPEN PLANT_CHANGE_CURSOR;
LOOP
FETCH PLANT_CHANGE_CURSOR INTO  AAA, BBB, CCC;
EXIT WHEN PLANT_CHANGE_CURSOR%NOTFOUND;

INSERT INTO TABLE_NAME /*수정 할 테이블명*/
(
HOUSE_CODE, COMPANY_CODE, OPERATING_CODE, PLANT_CODE, PLANT_NAME_LOC, PLANT_NAME_ENG, STATUS, ADD_DATE, ADD_TIME, ADD_USER_ID, ADD_USER_NAME_LOC, ADD_USER_NAME_ENG, ADD_USER_DEPT, CHANGE_DATE, CHANGE_TIME, CHANGE_USER_ID, CHANGE_USER_NAME_LOC, CHANGE_USER_NAME_ENG, CHANGE_USER_DEPT, MAIN_PLANT_FLAG, OPERATING_TYPE
)
select 
HOUSE_CODE, AAA, OPERATING_CODE, BBB, 'NEW_'||PLANT_NAME_LOC, 'NEW_'||PLANT_NAME_ENG, STATUS, ADD_DATE, ADD_TIME, ADD_USER_ID, ADD_USER_NAME_LOC, ADD_USER_NAME_ENG, ADD_USER_DEPT, CHANGE_DATE, CHANGE_TIME, CHANGE_USER_ID, CHANGE_USER_NAME_LOC, CHANGE_USER_NAME_ENG, CHANGE_USER_DEPT, MAIN_PLANT_FLAG, OPERATING_TYPE
FROM TABLE_NAME /*수정 할 테이블명*/
WHERE HOUSE_CODE = '100'
AND COMPANY_CODE = 'H100' AND  PLANT_CODE = CCC and operating_code in ('1100','1101');

COMMIT;
END LOOP;

CLOSE PLANT_CHANGE_CURSOR;
END ​PLANT_CHANGE_PROCEDURE;

3.프로시저 실행
EXECUTE ​PLANT_CHANGE_PROCEDURE(TABLE_NAME);

 

1번 테이블을 생성한후 2번 처럼 프로시저를 생성하는데 첫번째 행인

create or replace PROCEDURE ​PLANT_CHANGE_PROCEDURE 에서 

ORA-00911: 문자가 부적합합니다 에러가 발생합니다.

ORACLE 11G에서 작업했구요. 첫번째 행은 문제가 없는듯 하니 프로시저 문의 문법 중 한곳이 잘못된 걸까요? 콤마나 세미콜론 등은 확인해봤는데 이상 없었습니다.

무엇이 문제일까요,,,, 고민끝에 질문드립니다.

by 우리집아찌 [2018.03.08 09:21:50]

by 춤과파티 [2018.03.08 09:47:53]

참고 URL 감사합니다 덕분에 고쳐야 할 부분 열심히 연구하고 있습니다!


by 마농 [2018.03.08 09:44:46]

1. 프로시져 입력 인자에는 크기를 지정할 수 없구요.
  - 변경전 : VARCHAR2(50)
  - 변경후 : VARCHAR2
2. 오타도 보이구요.
  - 변경전 : OLD_PLANT
  - 변경후 : OLD_PLANT_CODE
3. SQL 구문에서의 바인드변수 처리는
  - 값에 대해서만 가능합니다. ==> aaa, bbb, ccc 가능
  - 명칭(테이블명, 컬럼명 등)에 대해서는 처리 안됩니다. ==> table_name 불가
  - 동적 쿼리 이용하셔야 합니다.


by 춤과파티 [2018.03.08 09:48:26]

오타지적 및 문법에 대한 첨언 감사합니다 항상 많은 도움받고 있습니다. 즐거운 하루 되세요^^


by 춤과파티 [2018.03.08 10:24:00]

위에 한가지 더 한 질문에 대한 제 생각인데요,,

바인드 변수 선언을 한개 더해서  change_table_name varchar2(50)

[바꿀 테이블명을 조회 해 올 쿼리문]

EXECUTE IMMEDIATE v_str INTO CHANGE_TABLE_NAME;    

요렇게 해서 밑에서 나머지 INSERT 구문을 작성하여 실행 하면 될까요?


by 춤과파티 [2018.03.08 10:17:15]

한가지 더 질문이 생겼는데요, 테이블명OR컬럼명에 바인드 변수 처리가 불가하다고 하셨는데

제가 하려고 한 작업이 3번 프로시져(입력인자)실행 할 때 테이블명 만 주면 알아서 그 테이블에 INSERT를 해주는

(재사용성 높은)쿼리를 사용할 목적으로 만든 프로시져인데 제가 하고자 하는 프로시져를 짠다고 했을때 INSERT 구문의 테이블명에 프로시져의 입력인자를 값으로 줘서 재사용성 높게 사용할 방법이 있을까요?


by 우리집아찌 [2018.03.08 10:23:56]

일단 왜 저렇게 하시는지 이유를 알고싶네요.

 


by 춤과파티 [2018.03.08 10:27:48]

저위에 작성된 같은 INSERT 구문을 실행해야할 테이블이 수백건이라 프로시저를 만들어 놓구

바꿔야할TABLE명만 입력해서 실행을 하면 돌아가게끔 해놓은 다음에 바꿔야할 바꿔야할TABLE명이 들어있는 테이블을

생성하여 반복문으로 프로시저(바꿔야할TABLE명) 실행을 돌려서 한번에 처리하려는 걸 하고자하여 질문을 드렸습니다


by 우리집아찌 [2018.03.08 10:34:02]

질문이요 ..

모든테이블의 컬럼 구조가 동일한가요?


by 춤과파티 [2018.03.08 10:38:41]

다르네요,,


by 마농 [2018.03.08 10:30:33]

테이블명을 변수로 넘기지 마시고 v_str 에 문자열 붙이기 하세요.
반대로 aaa, bbb, ccc 를 변수로 넘기세요.
 

v_str := 'INSERT INTO ' || change_table_name;
v_str := v_str || ' VALUES(..., :aaa, :bbb ... WHERE plan_code = :ccc)';
EXECUTE IMMEDIATE v_str USING aaa, bbb, ccc;

 


by 마농 [2018.03.08 10:41:35]

어익후~!! ㅎㅎ 테이블마다 쿼리가 달라져야 하겠네요?
select 쿼리의 조회 항목들도 동적으로 구성하셔야 합니다.
user_tab_columns 를 이용해야 하겠네요.


by 춤과파티 [2018.03.08 10:46:49]

맞네요,, 긴 여정이 될 거 같습니다ㅜㅜ


by 우리집아찌 [2018.03.08 10:49:30]

특정 컬럼 변경이라면 조금 수월할수도 있을것 같습니다.


by 마농 [2018.03.08 13:38:10]

조회 조건마져도 테이블마다 달라진다면? 좋은 방법은 아닐 것 같네요.
operating_code in ('1100','1101') 같은 조건이 모든 테이블 공통이라면 해볼만 합니다.
Insert 쿼리를 컬럼 다 적어가면서 하기는 힘들 듯 하네요.
CTAS 로 table_name_imsi 테이블을 만든뒤
table_name_imsi 컬럼을 Update 하고
table_name_imsi 을 이용해 그대로 table_name 에 Insert 한뒤
table_name_imsi 를 drop 하는 형태로 하는 것도 한가지 방법입니다.


by 춤과파티 [2018.03.08 13:40:54]

저도 테이블마다 컬럼명 갯수 등 다다르고 심지어 WHERE 절에 들어가는 조건의 컬럼명까지 통일 되어있지 않은 상태를 보고  아 ,, 이건 무리겠다 싶어서 방향을 전환했습니다. 새로운 방법을 가르쳐주신 점 감사합니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입