|
藍森林 http://www.lslnet.com 2006年6月26日 11:18
 請教高手:shell-關於在循環中做oracle連接的問題
出現問題的情況是這樣的,
我需要讀取一個文件,按行讀取,直至讀完.
每讀一行的時候根據讀取的值生成update語句,然後update數據庫.
現在小弟在循環中做連接的時候總是過不了,return code 為非正常值,
且無提示信息.以下為出現問題的代碼,請指教.
temp_file=${APP_INPUT_DIR}temp.dat
cat ${inputfile} >> ${temp_file}
while read -r line
do
accoutno=`echo "$line" | cut -c 1-10`
statementdate=`echo "$line" | cut -c 12-18`
payduedate=`echo "$line" | cut -c 20-28`
echo "accoutno : " ${accoutno}
echo "statementdate : " ${statementdate}
echo "payduedate : " ${payduedate}
sqlplus -s ${DB_LOGIN}/${DB_PASSWORD}@${DB_NAME}<<EOF
UPDATE CCARD_trans ECCARD_TRANS SET CC_TRANS_STATUS = 0 WHERE ACCOUNT_NO = ${accoutno} AND STATEMENT_DATE = TO_DATE('${statementdate}','YYYYMMDD') AND PAYMENT_DUE_DATE = TO_DATE('${payduedate}','YYYYMMDD');
exit;
updEOF
EOF
done < ${temp_file}
rm ${temp_file}
如有其他方法能完成同樣的功能,也行,小弟先謝了. |
請教高手:shell-關於在循環中做oracle連接的問題
one update statement will open a connection to database , even it works but still a very very bad strategy of using database . suggest to use shell generate a sql file first , the file could be looks like this
update .. set .. where .. and .. and ..and.. ;
update .. set .. where .. and .. and ..and.. ;
....
commit;
to generate the sql ,same as what you do , using while read loop through the file , use echo command can simply generate the sql statement .
Ex:
do
echo "update table set .. =$accountno.." >> update_table.sql
done
then connect to database , run update_table.sql at once . |
請教高手:shell-關於在循環中做oracle連接的問題
thanks to 精靈王,
問題已經解決,前段時間較忙,現在才上來回復,讓此帖有更意義
解決方法確實是先生成sql文件,然後再一次執行,現將代碼帖在下面
for file in $FILE_LIST
do
ORIG_FILE=`basename $file`
INPUT_DIR=`dirname $file`
echo
sqlplus -s /nolog << EOF
connect ${DB_LOGIN}/${DB_PASSWORD}@${DB_NAME}
[color=red]@${APP_SQL_NAME} ${INPUT_DIR} ${ORIG_FILE}[/color] ${SYS_TEMP_DIR} ${JOB_ID}
exit;
/
EOF
我拿掉了while循環,直接在sql文件中做處理,然後執行這個sql文件就行了. |
請教高手:shell-關於在循環中做oracle連接的問題
其中@${APP_SQL_NAME} ${INPUT_DIR} ${ORIG_FILE} ${SYS_TEMP_DIR} ${JOB_ID} 是執行sql的語句,
{APP_SQL_NAME}中包含路徑和sql文件的名字.
現將sql文件也帖出來
[color=green]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, 8);
V_PDUE_DATE := SUBSTR(V_LINE,21, 8);
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;
/[/color]
thanks ... |
| |
