CREATE OR REPLACE PROCEDURE SP_CHECK_TEST_02 ( P_LOG_ID IN VARCHAR2 := '' ,C_TYPE IN VARCHAR2 := 'D' ,COL_NAME IN VARCHAR2 := '' ,BSTART_DATE IN VARCHAR2 := '' ,BEND_DATE IN VARCHAR2 := '' ,WRITER IN VARCHAR2 := '' ) IS /* 사용자정의 변수 */ i NUMBER := 0; V_QUERY VARCHAR2(4000); /* array */ TYPE TB_TABLE IS TABLE OF OLAP.CHECK_OLAP_TABLE_CM%ROWTYPE; V_AR_TB_TABLE TB_TABLE := TB_TABLE(); TYPE CUR_CHECK IS REF CURSOR; V_C_CUR_CHECK CUR_CHECK; TYPE CUR_T IS REF CURSOR; V_C_CUR_T CUR_T; -- DECLARE CURSOR CUR_TABLE IS select SVC, TB_NM, SALE_DT, POS_AMT, HPC_AMT, HPC_S_AMT, HPC_U_AMT,USE_FLAG,ETL_DATE from olap.CHECK_OLAP_TABLE_CM; VR_DEP CUR_TABLE%ROWTYPE; BEGIN OPEN CUR_TABLE; LOOP i := i + 1; V_AR_TB_TABLE.EXTEND; FETCH CUR_TABLE INTO V_AR_TB_TABLE(i).SVC ,V_AR_TB_TABLE(i).TB_NM, V_AR_TB_TABLE(i).SALE_DT, V_AR_TB_TABLE(i).POS_AMT, V_AR_TB_TABLE(i).HPC_AMT, V_AR_TB_TABLE(i).HPC_S_AMT, V_AR_TB_TABLE(i).HPC_U_AMT, V_AR_TB_TABLE(i).USE_FLAG, V_AR_TB_TABLE(i).ETL_DATE; EXIT WHEN CUR_TABLE%NOTFOUND; V_QUERY:= 'INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP ('; V_QUERY:= V_QUERY || 'SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE)'; V_QUERY:= V_QUERY || ' SELECT /*+ FULL(T01) PARALLEL(8) */'; V_QUERY:= V_QUERY || ' '''||V_AR_TB_TABLE(i).SVC ||''' AS SVC'; V_QUERY:= V_QUERY || ' , '''||V_AR_TB_TABLE(i).TB_NM || ''' AS TB_NM'; V_QUERY:= V_QUERY || ' , REPLACE('||V_AR_TB_TABLE(i).SALE_DT || ',''-'','''') AS SALE_DT'; V_QUERY:= V_QUERY || ' , T01.COMPANY_CD AS COMPANY_CD'; V_QUERY:= V_QUERY || ' , T01.COMPANY_NM AS COMPANY_NM'; V_QUERY:= V_QUERY || ' , SUM('||V_AR_TB_TABLE(i).POS_AMT || ') AS POS_AMT'; V_QUERY:= V_QUERY || ' , SUM('||V_AR_TB_TABLE(i).HPC_AMT || ') AS HPC_AMT'; V_QUERY:= V_QUERY || ' , SUM('||V_AR_TB_TABLE(i).HPC_S_AMT || ') AS HPC_S_AMT'; V_QUERY:= V_QUERY || ' , SUM('||V_AR_TB_TABLE(i).HPC_U_AMT || ') AS HPC_U_AMT'; V_QUERY:= V_QUERY || ' , SYSDATE AS ETL_DATE'; V_QUERY:= V_QUERY || ' FROM '||V_AR_TB_TABLE(i).TB_NM||' T01'; V_QUERY:= V_QUERY || ' WHERE REPLACE('||V_AR_TB_TABLE(i).SALE_DT|| ',''-'','''') = ''20181222'''; V_QUERY:= V_QUERY || ' AND T01.COMPANY_CD IN (''PC00'',''SL00'',''BR00'')'; V_QUERY:= V_QUERY || ' GROUP BY '||V_AR_TB_TABLE(i).SALE_DT||',COMPANY_CD,COMPANY_NM;'; V_QUERY:= V_QUERY || ' COMMIT;'; DBMS_OUTPUT.PUT_LINE(V_QUERY); END LOOP; /* 실행 */ EXECUTE IMMEDIATE V_QUERY using V_AR_TB_TABLE(i); CLOSE CUR_TABLE; V_AR_TB_TABLE.DELETE; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERR CODE : ' || TO_CHAR(SQLCODE)); DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); RAISE_APPLICATION_ERROR(-20001, '자식 프로시저 에서 ERROR가 발생되어 프로시저가 종료되었습니다.'); ROLLBACK; END;
이런식으로 V_QUERY 이라는 변수 안에 insert 쿼리를 넣어서 EXECUTE IMMEDIATE 를 이용해서
여러건의 데이터를 한 테이블에 집어넣으려 하는데 에러가 납니다. 스텝별로 실행해 보면 V_QUERY 이 변수 안에
null값이 라는 메시지가 뜨네요. 로그보면 변수안에 제대로 들어가는듯 합니다. EXECUTE IMMEDIATE 이 구문쪽에서
잘못된거 같은데 도통 모르겠네요. 동적쿼리를 잘 안써버릇하다보니까 저 문법이 정확한건지도 잘 모르겠고,,,
여러 고수님들 답변 부탁드립니다.
-------------------------[Start Time: 2018/12/24 20:06:23]------------------------- SQL> INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_CARD_SALE_RATE_D' AS TB_NM , REPLACE(SALE_DT,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(TRX_AMT) AS POS_AMT , SUM(0) AS HPC_AMT , SUM(SAVE_PNT) AS HPC_S_AMT , SUM(USE_PNT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_CARD_SALE_RATE_D T01 WHERE REPLACE(SALE_DT,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY SALE_DT,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_HPC_DAILY_CLOSE_B' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(POS_SALE_AMT) AS POS_AMT , SUM(AUTH_AMT) AS HPC_AMT , SUM(AUTH_S_AMT) AS HPC_S_AMT , SUM(AUTH_U_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_HPC_DAILY_CLOSE_B T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_HPC_DAILY_CLOSE_D' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(POS_SALE_AMT) AS POS_AMT , SUM(HPC_AUTH_AMT) AS HPC_AMT , SUM(HPC_SAV_AMT) AS HPC_S_AMT , SUM(HPC_USE_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_HPC_DAILY_CLOSE_D T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_HPC_DAILY_CLOSE_P' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(0) AS POS_AMT , SUM(AUTH_AMT) AS HPC_AMT , SUM(AUTH_S_AMT) AS HPC_S_AMT , SUM(AUTH_U_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_HPC_DAILY_CLOSE_P T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_HPC_DAILY_CL_STOR_TYPE' AS TB_NM , REPLACE(TRAN_DT,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(SALE_AMT) AS POS_AMT , SUM(AUTH_AMT) AS HPC_AMT , SUM(AUTH_S_AMT) AS HPC_S_AMT , SUM(AUTH_U_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_HPC_DAILY_CL_STOR_TYPE T01 WHERE REPLACE(TRAN_DT,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DT,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_HPC_DAILY_CL_ST_SA' AS TB_NM , REPLACE(TRAN_DT,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(0) AS POS_AMT , SUM(AUTH_AMT) AS HPC_AMT , SUM(AUTH_S_AMT) AS HPC_S_AMT , SUM(AUTH_U_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_HPC_DAILY_CL_ST_SA T01 WHERE REPLACE(TRAN_DT,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DT,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_HPC_DAILY_SEX_AGE' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(0) AS POS_AMT , SUM(HPC_AMT) AS HPC_AMT , SUM(SAVE_PNT) AS HPC_S_AMT , SUM(USE_PNT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_HPC_DAILY_SEX_AGE T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_POS_DAILY_ITEM' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(SALE_AMT) AS POS_AMT , SUM(0) AS HPC_AMT , SUM(0) AS HPC_S_AMT , SUM(0) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_POS_DAILY_ITEM T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_POS_ITEM_M_CATEGORY_D' AS TB_NM , REPLACE(TRAN_DT,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(0) AS POS_AMT , SUM(HPC_AMT) AS HPC_AMT , SUM(0) AS HPC_S_AMT , SUM(0) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_POS_ITEM_M_CATEGORY_D T01 WHERE REPLACE(TRAN_DT,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DT,COMPANY_CD,COMPANY_NM; COMMIT; INSERT /*+ APPEND */ INTO OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT /*+ FULL(T01) PARALLEL(8) */ 'OLAP' AS SVC , 'OL_F_POS_PAY_DIV_D' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(SALE_AMT) AS POS_AMT , SUM(0) AS HPC_AMT , SUM(0) AS HPC_S_AMT , SUM(0) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM OL_F_POS_PAY_DIV_D T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT; 111111111111 ERR CODE : -6535 ERR MESSAGE : ORA-06535: EXECUTE IMMEDIATE 에 있는 문장 문자열이 NULL 이거나 0 길이입니다
이렇게 나옵니다,. 저 쿼리 직접 실행하면 이상없이 insert되구요,,
CREATE OR REPLACE PROCEDURE sp_check_test_02 IS tb_table olap.check_olap_table_cm%ROWTYPE; v_query VARCHAR2(4000); CURSOR cur_table IS SELECT * FROM olap.check_olap_table_cm ; BEGIN OPEN cur_table; LOOP FETCH cur_table INTO tb_table; EXIT WHEN cur_table%NOTFOUND; v_query := 'INSERT INTO olap.check_olap_table_tmp' || '( svc, tb_nm, sale_dt, company_cd, company_nm' || ', pos_amt, hpc_amt, hpc_s_amt, hpc_u_amt, etl_date )' || 'SELECT :v_svc AS svc' || ', :v_tb_nm AS tb_nm' || ', REPLACE(' || tb_table.sale_dt || ', ''-'') AS sale_dt' || ', company_cd' || ', company_nm' || ', SUM(' || tb_table.pos_amt || ') AS pos_amt' || ', SUM(' || tb_table.hpc_amt || ') AS hpc_amt' || ', SUM(' || tb_table.hpc_s_amt || ') AS hpc_s_amt' || ', SUM(' || tb_table.hpc_u_amt || ') AS hpc_u_amt' || ', sysdate AS etl_date' || ' FROM ' || tb_table.tb_nm || ' WHERE ' || tb_table.sale_dt || ' = :v_sale_dt' || ' AND company_cd IN (''PC00'', ''SL00'', ''BR00'')' || ' GROUP BY ' || tb_table.sale_dt || ', company_cd, company_nm' ; -- DBMS_OUTPUT.PUT_LINE(v_query); EXECUTE IMMEDIATE v_query USING tb_table.svc, tb_table.tb_nm, '2018-12-22'; COMMIT; END LOOP; CLOSE cur_table; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERR CODE : ' || TO_CHAR(SQLCODE)); DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); RAISE_APPLICATION_ERROR(-20001, '자식 프로시저 에서 ERROR가 발생되어 프로시저가 종료되었습니다.'); ROLLBACK; END; /