- 论坛徽章:
- 0
|
请教高手:shell-关于在循环中做oracle连接的问题
其中@${APP_SQL_NAME} ${INPUT_DIR} ${ORIG_FILE} ${SYS_TEMP_DIR} ${JOB_ID} 是执行sql的语句,
{APP_SQL_NAME}中包含路径和sql文件的名字.
现将sql文件也帖出来
set pagesize 0
set heading off
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') "JOB STARTED" FROM DUAL;
DECLARE
V_INPUT_HANDLE UTL_FILE.FILE_TYPE;
V_ACTLOG_HANDLE UTL_FILE.FILE_TYPE;
V_ACTLOG_FILE VARCHAR(50);
V_LINE VARCHAR(100);
V_INPUT_DIR VARCHAR(100);
V_LOG_DIR VARCHAR(100);
V_JOB_ID VARCHAR(15);
V_FILE_NAME VARCHAR(100);
V_STATUS NUMBER;
V_ACCOUNT_NO VARCHAR(15);
V_STAT_DATE VARCHAR(15);
V_PDUE_DATE VARCHAR(15);
ERR_NUM NUMBER(5);
ERR_MSG VARCHAR(100);
V_COUNTER NUMBER(5) ;
BEGIN
V_INPUT_DIR := '&1';
V_FILE_NAME := '&2';
V_LOG_DIR := '&3';
V_JOB_ID := '&4';
V_ACTLOG_FILE := V_JOB_ID||'_ACTLOG.log';
V_COUNTER := 0;
V_INPUT_HANDLE :=UTL_FILE.FOPEN(V_INPUT_DIR, V_FILE_NAME, 'R');
V_ACTLOG_HANDLE:=UTL_FILE.FOPEN(V_LOG_DIR, V_ACTLOG_FILE , 'W');
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'------Start Process -----');
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'filename=' || V_FILE_NAME);
/* process detail records */
LOOP
UTL_FILE.GET_LINE(V_INPUT_HANDLE,V_LINE);
V_ACCOUNT_NO := SUBSTR(V_LINE,1,10);
V_STAT_DATE := SUBSTR(V_LINE,12, ;
V_PDUE_DATE := SUBSTR(V_LINE,21, ;
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE, 'A/C, PAYMENTDATE:' ||V_ACCOUNT_NO ||','|| V_STAT_DATE );
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE, 'A/C, PAYMENTDATE:' ||V_ACCOUNT_NO ||','|| V_PDUE_DATE );
/* to restore inactive_date */
UPDATE CCARD_TRANS SET CC_TRANS_STATUS = 0
WHERE ACCOUNT_NO = V_ACCOUNT_NO AND STATEMENT_DATE = TO_DATE(V_STAT_DATE,'YYYYMMDD')
AND PAYMENT_DUE_DATE = TO_DATE(V_PDUE_DATE,'YYYYMMDD');
V_COUNTER := V_COUNTER + 1;
IF ( V_COUNTER > 100 ) THEN
BEGIN
COMMIT;
V_COUNTER := 0;
END;
END IF;
END LOOP;
COMMIT;
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'------Process End--- ');
UTL_FILE.FCLOSE(V_INPUT_HANDLE);
UTL_FILE.FCLOSE(V_ACTLOG_HANDLE);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH OR UTL_FILE.INVALID_MODE OR UTL_FILE.INVALID_FILEHANDLE OR UTL_FILE.INVALID_OPERATION OR UTL_FILE.INTERNAL_ERROR OR UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Program Error! File Handler Error!');
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,' File Handler Error!');
UTL_FILE.FCLOSE(V_INPUT_HANDLE);
UTL_FILE.FCLOSE(V_ACTLOG_HANDLE);
WHEN NO_DATA_FOUND THEN
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'the end of file ');
UTL_FILE.FCLOSE(V_INPUT_HANDLE);
UTL_FILE.FCLOSE(V_ACTLOG_HANDLE);
WHEN OTHERS THEN
ERR_NUM := SQLCODE;
ERR_MSG := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE('Unhandled Error!');
DBMS_OUTPUT.PUT_LINE(err_num || ' ' || err_msg);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE, err_num || ' ' || err_msg);
UTL_FILE.FCLOSE(V_INPUT_HANDLE);
UTL_FILE.FCLOSE(V_ACTLOG_HANDLE);
END;
/
thanks ... |
|