#oracle #plsql
#Oracle #plsql
Вопрос:
У меня есть входной файл имен, и мне нужно выполнить поиск всех совпадений в базе данных. Я использую цикл while для пошагового просмотра входного файла и цикл for для пошагового просмотра курсора. Я получаю следующую ошибку:
ORA-06550: строка 289, столбец 3: PLS-00103: Столкнулся с символом «END» при ожидании одного из следующих действий: ( begin case объявить выход для goto, если цикл mod null поднять возврат выбрать обновление, а с помощью << продолжить закрыть текущее удаление выборка блокировка вставить открыть откат точка сохранения установить sql выполнить фиксациюдля всех очистка канала слияния json_exists json_value json_query json_object json_array
код:
--
-- ************************************************************
--
-- Program Name: DataGathering_Step1.sql
-- Date Written: Dec 9, 2020
-- Author: St. Norbert College - Computer Services
-- Supporting Members:
-- Requesting Dept ITS
-- Dept Contact
-- Operation Date xx/xx/xxxx
--
-- Program Notes,Purpose and Comments
--
-- Special Rules or Notes
--
-- Changes - Date, who and comments on change.
--
--
--
CLEAR screen
/* SET echo OFF;
SET SHOW OFF;
SET ver OFF;
SET feed OFF;
*/
--
-- Get external parameters and general setup
--
define DataInFile = amp;1
define DataOutFile = amp;2
define EmailTo1 = amp;3
SET SERVEROUTPUT ON size 50000;
DECLARE
/* define the cursor for selecting a person based on first and last name. */
CURSOR c_person (v_fname varchar2, vlname varchar2) IS
select distinct i.spriden_id c_id,
i.spriden_first_name c_fname,
i.spriden_mi c_mi,
i.spriden_last_name c_lname,
p.spbpers_name_suffix c_suffix,
a.spraddr_street_line1 c_street1,
a.spraddr_street_line2 c_street2,
a.spraddr_street_line3 c_street3,
a.spraddr_city c_city,
a.spraddr_stat_code c_state,
a.spraddr_zip c_zip,
p.spbpers_birth_date c_bdate,
p.spbpers_ssn c_ssn
from spriden i, spbpers p, spraddr a
where i.spriden_pidm = p.spbpers_pidm
and i.spriden_pidm = a.spraddr_pidm
and a.spraddr_atyp_code = 'P'
and a.spraddr_to_date is null
and upper(i.spriden_first_name) like upper('v_fname%')
and upper(i.spriden_last_name) like upper('v_lname');
v_FileLocation VARCHAR2(290) := 'L';
-- variables for input data file
v_workfield VARCHAR2(255) := NULL;
v_crn_count NUMBER(4) := 0;
-- END variables for input data file
-- variables needed to verify input information and build output data file
v_id SPRIDEN.SPRIDEN_ID%TYPE := NULL;
v_pidm SPRIDEN.SPRIDEN_PIDM%TYPE := NULL;
v_last_name SPRIDEN.SPRIDEN_LAST_NAME%TYPE := NULL;
v_first_name SPRIDEN.SPRIDEN_FIRST_NAME%TYPE := NULL;
v_middle SPRIDEN.SPRIDEN_MI%TYPE := NULL;
v_suffix SPBPERS.SPBPERS_NAME_SUFFIX%TYPE := NULL;
v_birthdate_in SPBPERS.SPBPERS_BIRTH_DATE%TYPE := NULL;
v_birthdate SPBPERS.SPBPERS_BIRTH_DATE%TYPE := NULL;
v_ssn_in SPBPERS.SPBPERS_SSN%TYPE := NULL;
v_ssn SPBPERS.SPBPERS_SSN%TYPE := NULL;
v_street1 SPRADDR.SPRADDR_STREET_LINE1%TYPE := NULL;
v_street2 SPRADDR.SPRADDR_STREET_LINE2%TYPE := NULL;
v_street3 SPRADDR.SPRADDR_STREET_LINE3%TYPE := NULL;
v_city SPRADDR.SPRADDR_CITY%TYPE := NULL;
v_state SPRADDR.SPRADDR_STAT_CODE%TYPE := NULL;
v_zip SPRADDR.SPRADDR_ZIP%TYPE := NULL;
v_notes VARCHAR2(300) := ' ';
v_lookup_ind VARCHAR2(1) := NULL;
v_process_ind VARCHAR2(13) := NULL;
-- END variables needed to verify input information and build output data file
v_master_error VARCHAR2(1) := 'N';
v_process_flag VARCHAR2(3) := 'Y';
v_process_ctr NUMBER := '0';
v_insert_ctr NUMBER := '0';
v_found_ctr NUMBER := '0';
v_master_error_ctr NUMBER := '0';
v_fatal_comment VARCHAR2(13) := NULL;
v_error_code VARCHAR2(13) := NULL;
v_error_messages VARCHAR2(13) := NULL;
v_row_error VARCHAR2(13) := NULL;
-- variables for output and input data files
v_field_sep VARCHAR2(4) := CHR(009);
v_InPathname varchar2(55) := 'SNC_READ_AREA';
v_InFilename varchar2(355) := 'amp;DataInFile';
v_LinebuffIn varchar2(300) := NULL;
v_FileHandleIn UTL_FILE.FILE_TYPE;
v_crrt VARCHAR2(4) := CHR(013);
v_OutPathname VARCHAR2(55) := 'SNC_WRITE_AREA';
v_OutFilename VARCHAR2(355) := 'amp;DataOutFile';
v_LinebuffOut VARCHAR2(6000);
v_FileHandleOut UTL_FILE.FILE_TYPE;
-- END variables for output and input data files
-- variables for Emailing
message VARCHAR2(31000) := NULL;
v_tab VARCHAR2(6) := CHR(009);
crlf VARCHAR2(2) := CHR(13) || CHR(10);
mailhost VARCHAR2(30) := 'cwisrelay.snc.edu';
mail_conn utl_smtp.connection;
sender VARCHAR2(90) := 'darlene.blaney@snc.edu';
v_recipient VARCHAR2(90) := 'amp;EmailTo1';
v_email_subject VARCHAR2(160) := 'CS - Data Processing for ' || v_InFilename;
v_email_date VARCHAR2(250) := NULL;
-- END variables for Emailing
-- Core program
BEGIN
v_FileHandleOut := UTL_FILE.FOPEN(v_OutPathname,v_OutFilename,'W');
v_LinebuffOut := ('"SequenceNo"'
|| v_field_sep || '"Banner ID"'
|| v_field_sep || '"First Name"'
|| v_field_sep || '"Middle"'
|| v_field_sep || '"Last Name"'
|| v_field_sep || '"Suffix"'
|| v_field_sep || '"Street1"'
|| v_field_sep || '"Street2"'
|| v_field_sep || '"Street3"'
|| v_field_sep || '"City"'
|| v_field_sep || '"State"'
|| v_field_sep || '"Zip"'
|| v_field_sep || '"BirthDate"'
|| v_field_sep || '"SSN"'
|| v_field_sep || '"Notes"'
|| v_crrt);
UTL_FILE.PUT_LINE(v_FileHandleOut,v_LinebuffOut);
v_FileHandleIn := utl_file.fopen (v_InPathname,v_InFilename, 'R');
<<curs_loop>>
WHILE v_process_flag = 'Y' loop
-- v_fatal_comment := NULL;
-- v_master_error := 'N';
v_process_flag := 'Y';
-- Get the next row and get the necessary values
BEGIN
utl_file.get_line (v_FileHandleIn, v_LinebuffIn);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('INVALID_PATH');
utl_file.fclose_all;
WHEN NO_DATA_FOUND THEN
v_process_flag := 'N';
GOTO NextRow;
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_messages := v_error_messages || SQLERRM || crlf;
DBMS_OUTPUT.PUT_LINE ('Error: ' || v_error_code || ' ' || SQLERRM || ' ' || v_process_ctr);
v_process_flag := 'N';
GOTO NextRow;
END;
-- Input File - Position 1 - First Name
v_workfield := SUBSTR(v_LinebuffIn, 1, INSTR(v_LinebuffIn, v_field_sep, 1, 1)-1);
v_workfield := UPPER(v_workfield);
v_workfield := TRIM(v_workfield);
v_workfield := NVL(v_workfield,'END');
v_first_name := v_workfield;
DBMS_OUTPUT.PUT_LINE ('Position 1: ' || v_workfield);
IF v_workfield = 'END' THEN
v_process_flag := 'N';
v_row_error := 'N';
GOTO NextRow;
ELSIF v_workfield = 'FIRST' THEN
GOTO NextRow;
END IF;
-- Input File - Position 2 - Middle Name
v_workfield := SUBSTR(v_LinebuffIn, INSTR(v_LinebuffIn, v_field_sep, 1, 1) 1, INSTR(v_LinebuffIn, v_field_sep, 1, 2)-INSTR(v_LinebuffIn, v_field_sep, 1, 1)-1);
v_workfield := UPPER(v_workfield);
v_workfield := TRIM(v_workfield);
v_middle := NVL(v_workfield,'');
DBMS_OUTPUT.PUT_LINE ('Position 2: ' || v_middle);
-- Input File = Position 3 - Last Name
v_workfield := SUBSTR(v_LinebuffIn, INSTR(v_LinebuffIn, v_field_sep, 1, 2) 1, INSTR(v_LinebuffIn, v_field_sep, 1, 3)-INSTR(v_LinebuffIn, v_field_sep, 1, 2)-1);
v_workfield := TRIM(v_workfield);
v_workfield := NVL(v_workfield,'');
v_last_name := v_workfield;
DBMS_OUTPUT.PUT_LINE ('Position 3: ' || v_last_name);
-- Input File - Position 4 - Suffix
v_workfield := SUBSTR(v_LinebuffIn, INSTR(v_LinebuffIn, v_field_sep, 1, 3) 1, INSTR(v_LinebuffIn, v_field_sep, 1, 4)-INSTR(v_LinebuffIn, v_field_sep, 1, 3)-1);
v_workfield := TRIM(v_workfield);
v_workfield := UPPER(v_workfield);
v_suffix := NVL(v_suffix,'');
DBMS_OUTPUT.PUT_LINE ('Position 4: ' || v_suffix);
-- Input File - Position 5 - Date of Birth
v_workfield := SUBSTR(v_LinebuffIn, INSTR(v_LinebuffIn, v_field_sep, 1, 4) 1, INSTR(v_LinebuffIn, v_field_sep, 1, 5)-INSTR(v_LinebuffIn, v_field_sep, 1, 4)-1);
v_workfield := TRIM(v_workfield);
v_workfield := UPPER(v_workfield);
v_workfield := REPLACE(v_workfield,' ','');
v_workfield := REPLACE(v_workfield,',','');
v_workfield := NVL(v_workfield,'NODATE');
IF (SUBSTR(v_workfield,2,1) <> '/' AND SUBSTR(v_workfield,3,1) <> '/') THEN
v_workfield := 'NODATE';
END IF;
IF v_workfield = 'NODATE' THEN
v_birthdate_in := NULL;
ELSE
BEGIN
v_birthdate_in := TO_DATE(v_workfield,'mm/dd/YYYY');
EXCEPTION
WHEN VALUE_ERROR THEN
v_birthdate_in := NULL;
v_row_error := 'Y';
v_error_messages := v_error_messages || ' Fatal: R' || v_process_ctr || ' Invalid Date (birthdate).' || v_crlf;
END;
END IF;
DBMS_OUTPUT.PUT_LINE ('Position 5: ' || v_birthdate);
-- Input File - Position 6 - SSN
v_workfield := SUBSTR(v_LinebuffIn, INSTR(v_LinebuffIn, v_field_sep, 1, 5) 1, INSTR(v_LinebuffIn, v_field_sep, 1, 6)-INSTR(v_LinebuffIn, v_field_sep, 1, 5)-1);
v_workfield := TRIM(v_workfield);
v_workfield := UPPER(v_workfield);
v_workfield := NVL(v_workfield,'00');
v_ssn := LPAD(v_ssn,9,'0');
DBMS_OUTPUT.PUT_LINE ('Position 6: ' || v_ssn);
-- loop through cursor to get people
v_found_ctr := 0;
<<curs1_loop>>
FOR curs1 IN c_person (v_first_name, v_last_name) LOOP
BEGIN
v_found_ctr := v_found_ctr 1;
-- Write information to output file for review
v_LinebuffOut := ('"' || v_found_ctr
|| '"' || v_field_sep || '"' || curs1.c_id
|| '"' || v_field_sep || '"' || curs1.c_fname
|| '"' || v_field_sep || '"' || curs1.c_mi
|| '"' || v_field_sep || '"' || curs1.c_lname
|| '"' || v_field_sep || '"' || curs1.c_suffix
|| '"' || v_field_sep || '"' || curs1.c_street1
|| '"' || v_field_sep || '"' || curs1.c_street2
|| '"' || v_field_sep || '"' || curs1.c_street3
|| '"' || v_field_sep || '"' || curs1.c_city
|| '"' || v_field_sep || '"' || curs1.c_state
|| '"' || v_field_sep || '"' || curs1.c_zip
|| '"' || v_field_sep || '"' || curs1.c_bdate
|| '"' || v_field_sep || '"' || curs1.c_ssn
|| '"' || v_field_sep || '"' || ' '
|| '"' || v_crrt);
UTL_FILE.PUT_LINE(v_FileHandleOut,v_LinebuffOut);
END;
END LOOP curs1_loop;
IF v_found_ctr = 0 THEN
v_notes := 'No info found';
v_LinebuffOut := ('"' || v_found_ctr
|| '"' || v_field_sep || '"' || v_id
|| '"' || v_field_sep || '"' || v_first_name
|| '"' || v_field_sep || '"' || v_middle
|| '"' || v_field_sep || '"' || v_last_name
|| '"' || v_field_sep || '"' || v_suffix
|| '"' || v_field_sep || '"' || ' '
|| '"' || v_field_sep || '"' || ' '
|| '"' || v_field_sep || '"' || ' '
|| '"' || v_field_sep || '"' || ' '
|| '"' || v_field_sep || '"' || ' '
|| '"' || v_field_sep || '"' || ' '
|| '"' || v_field_sep || '"' || v_birthdate
|| '"' || v_field_sep || '"' || v_ssn
|| '"' || v_field_sep || '"' || v_notes
|| '"' || v_crrt);
UTL_FILE.PUT_LINE(v_FileHandleOut,v_LinebuffOut);
END IF;
<<NextRow>>
END LOOP curs_loop;
-- Send email
v_email_date := TO_CHAR(sysdate,'FMDY, dd MON YYYY HH24:MI:SS') || ' -0600 (CST)';
BEGIN
message := 'Date: ' || v_email_date || crlf ||
'From: ' || v_recipient3 || crlf ||
'Subject: ' || v_email_subject || crlf ||
'To: ' || v_recipient || crlf || crlf;
message := message || 'CS office,'
|| crlf
|| crlf
|| crlf
|| crlf
|| ' ' || crlf || crlf;
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, v_recipient);
utl_smtp.rcpt(mail_conn, v_recipient2);
utl_smtp.rcpt(mail_conn, v_recipient3);
utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
Ответ №1:
Выглядит как пропущенный END сразу после <<NextRow>> . Вероятно, должно быть так:
DECLARE
/* define the cursor for selecting a person based on first and last name. */
CURSOR c_person (v_fname VARCHAR2, vlname VARCHAR2)
IS
SELECT DISTINCT i.spriden_id c_id,
i.spriden_first_name c_fname,
i.spriden_mi c_mi,
i.spriden_last_name c_lname,
p.spbpers_name_suffix c_suffix,
a.spraddr_street_line1 c_street1,
a.spraddr_street_line2 c_street2,
a.spraddr_street_line3 c_street3,
a.spraddr_city c_city,
a.spraddr_stat_code c_state,
a.spraddr_zip c_zip,
p.spbpers_birth_date c_bdate,
p.spbpers_ssn c_ssn
FROM spriden i, spbpers p, spraddr a
WHERE i.spriden_pidm = p.spbpers_pidm
AND i.spriden_pidm = a.spraddr_pidm
AND a.spraddr_atyp_code = 'P'
AND a.spraddr_to_date IS NULL
AND UPPER (i.spriden_first_name) LIKE UPPER ('v_fname%')
AND UPPER (i.spriden_last_name) LIKE UPPER ('v_lname');
v_FileLocation VARCHAR2 (290) := 'L';
-- variables for input data file
v_workfield VARCHAR2 (255) := NULL;
v_crn_count NUMBER (4) := 0;
-- END variables for input data file
-- variables needed to verify input information and build output data file
v_id SPRIDEN.SPRIDEN_ID%TYPE := NULL;
v_pidm SPRIDEN.SPRIDEN_PIDM%TYPE := NULL;
v_last_name SPRIDEN.SPRIDEN_LAST_NAME%TYPE := NULL;
v_first_name SPRIDEN.SPRIDEN_FIRST_NAME%TYPE := NULL;
v_middle SPRIDEN.SPRIDEN_MI%TYPE := NULL;
v_suffix SPBPERS.SPBPERS_NAME_SUFFIX%TYPE := NULL;
v_birthdate_in SPBPERS.SPBPERS_BIRTH_DATE%TYPE := NULL;
v_birthdate SPBPERS.SPBPERS_BIRTH_DATE%TYPE := NULL;
v_ssn_in SPBPERS.SPBPERS_SSN%TYPE := NULL;
v_ssn SPBPERS.SPBPERS_SSN%TYPE := NULL;
v_street1 SPRADDR.SPRADDR_STREET_LINE1%TYPE := NULL;
v_street2 SPRADDR.SPRADDR_STREET_LINE2%TYPE := NULL;
v_street3 SPRADDR.SPRADDR_STREET_LINE3%TYPE := NULL;
v_city SPRADDR.SPRADDR_CITY%TYPE := NULL;
v_state SPRADDR.SPRADDR_STAT_CODE%TYPE := NULL;
v_zip SPRADDR.SPRADDR_ZIP%TYPE := NULL;
v_notes VARCHAR2 (300) := ' ';
v_lookup_ind VARCHAR2 (1) := NULL;
v_process_ind VARCHAR2 (13) := NULL;
-- END variables needed to verify input information and build output data file
v_master_error VARCHAR2 (1) := 'N';
v_process_flag VARCHAR2 (3) := 'Y';
v_process_ctr NUMBER := '0';
v_insert_ctr NUMBER := '0';
v_found_ctr NUMBER := '0';
v_master_error_ctr NUMBER := '0';
v_fatal_comment VARCHAR2 (13) := NULL;
v_error_code VARCHAR2 (13) := NULL;
v_error_messages VARCHAR2 (13) := NULL;
v_row_error VARCHAR2 (13) := NULL;
-- variables for output and input data files
v_field_sep VARCHAR2 (4) := CHR (009);
v_InPathname VARCHAR2 (55) := 'SNC_READ_AREA';
v_InFilename VARCHAR2 (355) := 'amp;DataInFile';
v_LinebuffIn VARCHAR2 (300) := NULL;
v_FileHandleIn UTL_FILE.FILE_TYPE;
v_crrt VARCHAR2 (4) := CHR (013);
v_OutPathname VARCHAR2 (55) := 'SNC_WRITE_AREA';
v_OutFilename VARCHAR2 (355) := 'amp;DataOutFile';
v_LinebuffOut VARCHAR2 (6000);
v_FileHandleOut UTL_FILE.FILE_TYPE;
-- END variables for output and input data files
-- variables for Emailing
MESSAGE VARCHAR2 (31000) := NULL;
v_tab VARCHAR2 (6) := CHR (009);
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
mailhost VARCHAR2 (30) := 'cwisrelay.snc.edu';
mail_conn UTL_SMTP.connection;
sender VARCHAR2 (90) := 'darlene.blaney@snc.edu';
v_recipient VARCHAR2 (90) := 'amp;EmailTo1';
v_email_subject VARCHAR2 (160)
:= 'CS - Data Processing for ' || v_InFilename;
v_email_date VARCHAR2 (250) := NULL;
-- END variables for Emailing
-- Core program
BEGIN
v_FileHandleOut := UTL_FILE.FOPEN (v_OutPathname, v_OutFilename, 'W');
v_LinebuffOut :=
( '"SequenceNo"'
|| v_field_sep
|| '"Banner ID"'
|| v_field_sep
|| '"First Name"'
|| v_field_sep
|| '"Middle"'
|| v_field_sep
|| '"Last Name"'
|| v_field_sep
|| '"Suffix"'
|| v_field_sep
|| '"Street1"'
|| v_field_sep
|| '"Street2"'
|| v_field_sep
|| '"Street3"'
|| v_field_sep
|| '"City"'
|| v_field_sep
|| '"State"'
|| v_field_sep
|| '"Zip"'
|| v_field_sep
|| '"BirthDate"'
|| v_field_sep
|| '"SSN"'
|| v_field_sep
|| '"Notes"'
|| v_crrt);
UTL_FILE.PUT_LINE (v_FileHandleOut, v_LinebuffOut);
v_FileHandleIn := UTL_FILE.fopen (v_InPathname, v_InFilename, 'R');
<<curs_loop>>
WHILE v_process_flag = 'Y'
LOOP
-- v_fatal_comment := NULL;
-- v_master_error := 'N';
v_process_flag := 'Y';
-- Get the next row and get the necessary values
BEGIN
UTL_FILE.get_line (v_FileHandleIn, v_LinebuffIn);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN
DBMS_OUTPUT.PUT_LINE ('INVALID_PATH');
UTL_FILE.fclose_all;
WHEN NO_DATA_FOUND
THEN
v_process_flag := 'N';
GOTO NextRow;
WHEN OTHERS
THEN
v_error_code := SQLCODE;
v_error_messages := v_error_messages || SQLERRM || crlf;
DBMS_OUTPUT.PUT_LINE (
'Error: '
|| v_error_code
|| ' '
|| SQLERRM
|| ' '
|| v_process_ctr);
v_process_flag := 'N';
GOTO NextRow;
END;
-- Input File - Position 1 - First Name
v_workfield :=
SUBSTR (v_LinebuffIn,
1,
INSTR (v_LinebuffIn,
v_field_sep,
1,
1)
- 1);
v_workfield := UPPER (v_workfield);
v_workfield := TRIM (v_workfield);
v_workfield := NVL (v_workfield, 'END');
v_first_name := v_workfield;
DBMS_OUTPUT.PUT_LINE ('Position 1: ' || v_workfield);
IF v_workfield = 'END'
THEN
v_process_flag := 'N';
v_row_error := 'N';
GOTO NextRow;
ELSIF v_workfield = 'FIRST'
THEN
GOTO NextRow;
END IF;
-- Input File - Position 2 - Middle Name
v_workfield :=
SUBSTR (v_LinebuffIn,
INSTR (v_LinebuffIn,
v_field_sep,
1,
1)
1,
INSTR (v_LinebuffIn,
v_field_sep,
1,
2)
- INSTR (v_LinebuffIn,
v_field_sep,
1,
1)
- 1);
v_workfield := UPPER (v_workfield);
v_workfield := TRIM (v_workfield);
v_middle := NVL (v_workfield, '');
DBMS_OUTPUT.PUT_LINE ('Position 2: ' || v_middle);
-- Input File = Position 3 - Last Name
v_workfield :=
SUBSTR (v_LinebuffIn,
INSTR (v_LinebuffIn,
v_field_sep,
1,
2)
1,
INSTR (v_LinebuffIn,
v_field_sep,
1,
3)
- INSTR (v_LinebuffIn,
v_field_sep,
1,
2)
- 1);
v_workfield := TRIM (v_workfield);
v_workfield := NVL (v_workfield, '');
v_last_name := v_workfield;
DBMS_OUTPUT.PUT_LINE ('Position 3: ' || v_last_name);
-- Input File - Position 4 - Suffix
v_workfield :=
SUBSTR (v_LinebuffIn,
INSTR (v_LinebuffIn,
v_field_sep,
1,
3)
1,
INSTR (v_LinebuffIn,
v_field_sep,
1,
4)
- INSTR (v_LinebuffIn,
v_field_sep,
1,
3)
- 1);
v_workfield := TRIM (v_workfield);
v_workfield := UPPER (v_workfield);
v_suffix := NVL (v_suffix, '');
DBMS_OUTPUT.PUT_LINE ('Position 4: ' || v_suffix);
-- Input File - Position 5 - Date of Birth
v_workfield :=
SUBSTR (v_LinebuffIn,
INSTR (v_LinebuffIn,
v_field_sep,
1,
4)
1,
INSTR (v_LinebuffIn,
v_field_sep,
1,
5)
- INSTR (v_LinebuffIn,
v_field_sep,
1,
4)
- 1);
v_workfield := TRIM (v_workfield);
v_workfield := UPPER (v_workfield);
v_workfield := REPLACE (v_workfield, ' ', '');
v_workfield := REPLACE (v_workfield, ',', '');
v_workfield := NVL (v_workfield, 'NODATE');
IF ( SUBSTR (v_workfield, 2, 1) <> '/'
AND SUBSTR (v_workfield, 3, 1) <> '/')
THEN
v_workfield := 'NODATE';
END IF;
IF v_workfield = 'NODATE'
THEN
v_birthdate_in := NULL;
ELSE
BEGIN
v_birthdate_in := TO_DATE (v_workfield, 'mm/dd/YYYY');
EXCEPTION
WHEN VALUE_ERROR
THEN
v_birthdate_in := NULL;
v_row_error := 'Y';
v_error_messages :=
v_error_messages
|| ' Fatal: R'
|| v_process_ctr
|| ' Invalid Date (birthdate).'
|| v_crlf;
END;
END IF;
DBMS_OUTPUT.PUT_LINE ('Position 5: ' || v_birthdate);
-- Input File - Position 6 - SSN
v_workfield :=
SUBSTR (v_LinebuffIn,
INSTR (v_LinebuffIn,
v_field_sep,
1,
5)
1,
INSTR (v_LinebuffIn,
v_field_sep,
1,
6)
- INSTR (v_LinebuffIn,
v_field_sep,
1,
5)
- 1);
v_workfield := TRIM (v_workfield);
v_workfield := UPPER (v_workfield);
v_workfield := NVL (v_workfield, '00');
v_ssn := LPAD (v_ssn, 9, '0');
DBMS_OUTPUT.PUT_LINE ('Position 6: ' || v_ssn);
-- loop through cursor to get people
v_found_ctr := 0;
<<curs1_loop>>
FOR curs1 IN c_person (v_first_name, v_last_name)
LOOP
BEGIN
v_found_ctr := v_found_ctr 1;
-- Write information to output file for review
v_LinebuffOut :=
( '"'
|| v_found_ctr
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_id
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_fname
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_mi
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_lname
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_suffix
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_street1
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_street2
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_street3
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_city
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_state
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_zip
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_bdate
|| '"'
|| v_field_sep
|| '"'
|| curs1.c_ssn
|| '"'
|| v_field_sep
|| '"'
|| ' '
|| '"'
|| v_crrt);
UTL_FILE.PUT_LINE (v_FileHandleOut, v_LinebuffOut);
END;
END LOOP curs1_loop;
IF v_found_ctr = 0
THEN
v_notes := 'No info found';
v_LinebuffOut :=
( '"'
|| v_found_ctr
|| '"'
|| v_field_sep
|| '"'
|| v_id
|| '"'
|| v_field_sep
|| '"'
|| v_first_name
|| '"'
|| v_field_sep
|| '"'
|| v_middle
|| '"'
|| v_field_sep
|| '"'
|| v_last_name
|| '"'
|| v_field_sep
|| '"'
|| v_suffix
|| '"'
|| v_field_sep
|| '"'
|| ' '
|| '"'
|| v_field_sep
|| '"'
|| ' '
|| '"'
|| v_field_sep
|| '"'
|| ' '
|| '"'
|| v_field_sep
|| '"'
|| ' '
|| '"'
|| v_field_sep
|| '"'
|| ' '
|| '"'
|| v_field_sep
|| '"'
|| ' '
|| '"'
|| v_field_sep
|| '"'
|| v_birthdate
|| '"'
|| v_field_sep
|| '"'
|| v_ssn
|| '"'
|| v_field_sep
|| '"'
|| v_notes
|| '"'
|| v_crrt);
UTL_FILE.PUT_LINE (v_FileHandleOut, v_LinebuffOut);
END IF;
<<NextRow>>
end;
END LOOP curs_loop;
-- Send email
v_email_date :=
TO_CHAR (SYSDATE, 'FMDY, dd MON YYYY HH24:MI:SS') || ' -0600 (CST)';
BEGIN
MESSAGE :=
'Date: '
|| v_email_date
|| crlf
|| 'From: '
|| v_recipient3
|| crlf
|| 'Subject: '
|| v_email_subject
|| crlf
|| 'To: '
|| v_recipient
|| crlf
|| crlf;
MESSAGE :=
MESSAGE
|| 'CS office,'
|| crlf
|| crlf
|| crlf
|| crlf
|| ' '
|| crlf
|| crlf;
mail_conn := UTL_SMTP.open_connection (mailhost, 25);
UTL_SMTP.helo (mail_conn, mailhost);
UTL_SMTP.mail (mail_conn, sender);
UTL_SMTP.rcpt (mail_conn, v_recipient);
UTL_SMTP.rcpt (mail_conn, v_recipient2);
UTL_SMTP.rcpt (mail_conn, v_recipient3);
UTL_SMTP.data (mail_conn, MESSAGE);
UTL_SMTP.quit (mail_conn);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END;
/