DECLARE
CURSOR C_NAME_LIST IS
The SELECT NAME_ID, UDFN21
The FROM NAME_VIEW
WHERE NVL (UDFN21, 0) & gt;=18;
V_TEMP_CT NUMBER:=0;
V_RECORD_CT NUMBER:=0;
V_FILE_CONTEN CLOB:=';
V_TEMP_LOG CLOB:=';
V_PATH VARCHAR2 (1000) :='& amp; 1 '.
V_RESORT_NAME VARCHAR2 (30) :='& amp; 2 '.
V_COMMIT_CT NUMBER:=100;
V_LOG_TIME_FORMAT VARCHAR2 (30) :='dd - Mon - yyyy hh24: mi: ss';
V_START_TIME VARCHAR2 (30);
The BEGIN
V_START_TIME:=TO_CHAR (SYSDATE V_LOG_TIME_FORMAT);
DBMS_OUTPUT. PUT_LINE (V_START_TIME | | '- Start patch');
The BEGIN
The SELECT RESORT mixes INTO V_RESORT_NAME from DATABASE_RESORTS WHERE DATABASE_ID='ORS';
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
='Resort mixes V_FILE_CONTEN: Name:' | | V_RESORT_NAME | | CRH (13) | | CRH (10) | | 'NAME_ID, UDFN21' | | CRH (13) | | CRH (10);
The BEGIN
FOR REC IN C_NAME_LIST LOOP
The UPDATE NAME_VIEW SET UDFN21=0 WHERE NAME_ID=REC. NAME_ID;
V_TEMP_LOG:=V_TEMP_LOG | | '[' | | REC. NAME_ID | |', '| | REC. UDFN21 | |'] '| | CRH (13) | | CRH (10);
V_TEMP_CT:=V_TEMP_CT + 1;
V_RECORD_CT:=V_RECORD_CT + 1;
IF V_TEMP_CT & gt;=V_COMMIT_CT THEN
V_TEMP_CT:=0;
V_FILE_CONTEN:=V_FILE_CONTEN | | V_TEMP_LOG;
V_TEMP_LOG:=';
COMMIT;
END the IF;
END LOOP;
V_FILE_CONTEN:=V_FILE_CONTEN | | V_TEMP_LOG;
COMMIT;
V_FILE_CONTEN:=V_FILE_CONTEN | | '* * * * * * * * * * * * * *' | | V_RECORD_CT | | 'records has had changed.' | | '* * * * * * * * * * * * * *' | | CRH (13) | | CRH (10);
V_FILE_CONTEN:=V_FILE_CONTEN | | 'End Of the File;
DBMS_OUTPUT. PUT_LINE (TO_CHAR (SYSDATE, V_LOG_TIME_FORMAT) | | '- End patch');
EXCEPTION WHEN OTHERS THEN
V_FILE_CONTEN:=V_FILE_CONTEN | | DBMS_UTILITY. FORMAT_ERROR_BACKTRACE | | CRH (13) | | CRH (10);
V_FILE_CONTEN:=V_FILE_CONTEN | | DBMS_UTILITY. FORMAT_ERROR_STACK | | CRH (13) | | CRH (10);
V_FILE_CONTEN:=V_FILE_CONTEN | | '/* * * * * * * * * * * * * * * * * * * * * * * * * * */' | | CRH (13) | | CRH (10) | | '/* Completed with error. */' | | CRH (13) | | CRH (10) | | '/* * * * * * * * * * * * * * * * * * * * * * * * * * */';
END;
V_FILE_CONTEN:='Start time:' | | V_START_TIME | | 'End time:' | | TO_CHAR (SYSDATE, V_LOG_TIME_FORMAT) | | CRH (13) | | CRH (10) | | V_FILE_CONTEN;
DBMS_OUTPUT. PUT_LINE (TO_CHAR (SYSDATE, V_LOG_TIME_FORMAT) | | '- Start the export file');
DBMS_XSLPROCESSOR. CLOB2FILE (V_FILE_CONTEN V_PATH, 'UPD_UDFN21_RESULT_' | | V_RESORT_NAME | | '. TXT ', 0).
DBMS_OUTPUT. PUT_LINE (TO_CHAR (SYSDATE, V_LOG_TIME_FORMAT) | | '- the End of the export file');
END;
The EXIT;
CodePudding user response:
Both modify files, and pull!