我编写了一个存储过程，它将从表中提取计数，如果计数有任何不匹配，将执行一些sql查询来捕获丢失的项目。存储过程的输出使用 utl_file 记录到 .txt 文件中。
当我尝试编译该过程时，我收到错误。
注意：我已经检查了与此错误相关的其他帖子，但是由于我只是在学习PL-SQL，因此无法找出问题所在。请向我建议代码中所需的更正。
create or replace PROCEDURE "PROC_TOTAL_EVENTS"
AS
file_handle utl_file.file_type;
utl_dir VARCHAR2(255) := '/u01/apps/Miscellaneous/bin'
counts NUMBER;
counts1 NUMBER;
counts2 NUMBER;
counts3 NUMBER;
missed_a NUMBER;
missed_b NUMBER;
missed_c VARCHAR2(50);
retrieved_buffer VARCHAR2(100);
BEGIN
file_handle := utl_file.fopen(utl_dir,'spool.txt','W');
dbms_output.put_line('File opened for write');
utl_file.put_line(file_handle, 'Executing the counts for total_events');
dbms_output.put_line('--------------------------------');
select /*+ PARALLEL(PH,8)) */ count(*) into counts from
cdb_admin.CPM_PREF_EVENT_STG;
utl_file.putf(file_handle, 'Total preference events sent to CPM from CDB',
counts);
dbms_output.put_line('1 step success');
SELECT COUNT(distinct cdb_pref_event_id) into counts1 FROM
cdb_admin.cpm_pref_result WHERE
cdb_pref_event_id IN (SELECT /*+ PARALLEL(PH,8)) */ cdb_pref_event_id FROM
cdb_admin.CPM_PREF_EVENT_STG ph;
utl_file.putf(file_handle, 'distinct count of total prefResults sent
by CPM to CDB', counts1);
dbms_output.put_line('2 step success');
Select count(distinct e.cdb_pref_event_id) Total_exception_count into
counts from
(Select distinct eh.cdb_customer_id cdb_customer_id,eh.cdb_pref_event_id
cdb_pref_event_id,eh.supp_cd supp_cd
from cdb_admin.CPM_PREF_EVENT_STG eh Left outer join
CDB_ADMIN.CPM_PREF_RESULT er on (eh.cdb_customer_id=er.cdb_customer_id and
eh.cdb_pref_event_id=er.cdb_pref_event_id)
where er.cdb_pref_event_id is null and er.cdb_customer_id is null) r join
CDB_ADMIN.cpm_pref_event_exception e
on (r.cdb_customer_id=e.cdb_customer_id and
r.cdb_pref_event_id=e.cdb_pref_event_id) where e.supp_cd != 'PROFILE-NOT-
FOUND';
utl_file.putf(file_handle, 'distinct count of total exceptions sent by CPM
to CDB', counts2);
dbms_output.put_line('3 step success');
Select count(distinct e.cdb_pref_event_id),e.supp_cd from
(Select distinct eh.cdb_customer_id cdb_customer_id,eh.cdb_pref_event_id
cdb_pref_event_id,eh.supp_cd supp_cd
from (select * from cdb_admin.CPM_PREF_EVENT_STG) eh Left outer join
CDB_ADMIN.CPM_PREF_RESULT er on (eh.cdb_customer_id=er.cdb_customer_id and
eh.cdb_pref_event_id=er.cdb_pref_event_id)
where er.cdb_pref_event_id is null and er.cdb_customer_id is null) r join
CDB_ADMIN.cpm_pref_event_exception e
on (r.cdb_customer_id=e.cdb_customer_id and
r.cdb_pref_event_id=e.cdb_pref_event_id) group by e.supp_cd;
utl_file.putf(file_handle, 'Counts bifurcation', counts3);
dbms_output.put_line('4 step success');
IF counts = (counts1+counts2) THEN
utl_file.putf(file_handle, 'Counts are matching', counts,
(counts1+counts2));
ELSE
dbms_output.put_line('Counts are not matching, finding the missed
record');
SELECT DISTINCT r.cdb_customer_id, r.cdb_pref_event_id,r.supp_cd into
missed_a,missed_b,missed_c
FROM
(
SELECT DISTINCT
eh.cdb_customer_id cdb_customer_id,
eh.cdb_pref_event_id cdb_pref_event_id,
eh.supp_cd supp_cd
FROM
(select * from cdb_admin.CPM_PREF_EVENT_STG eh
LEFT OUTER JOIN cdb_admin.cpm_pref_result er ON (
eh.cdb_customer_id = er.cdb_customer_id
AND
eh.cdb_pref_event_id = er.cdb_pref_event_id )
WHERE
er.cdb_pref_event_id IS NULL
AND er.cdb_customer_id IS NULL
) r
LEFT OUTER JOIN cdb_admin.cpm_pref_event_exception e ON (
r.cdb_customer_id = e.cdb_customer_id
AND r.cdb_pref_event_id = e.cdb_pref_event_id ) WHERE
e.cdb_pref_event_id IS NULL AND e.cdb_customer_id IS NULL;
utl_file.putf(file_handle, 'missed record ID and country code',
missed_a,missed_b,missed_c);
END IF;
utl_file.fclose(file_handle);
dbms_output.put_line('File closed');
file_handle := utl_file.fopen(utl_dir,'TotEvents.txt','R');
dbms_output.put_line('File opened for read');
utl_file.get_line(file_handle, retrieved_buffer);
dbms_output.put_line('Read success');
dbms_output.put_line(retrieved_buffer);
utl_file.fclose(file_handle);
dbms_output.put_line('File closed');
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
RAISE;
dbms_output.put_line('No Data Found : Exception');
dbms_output.put_line('Errorcode: ' || sqlcode || ' Error message: ' || sqlerrm);
WHEN TOO_MANY_ROWS THEN
ROLLBACK;
RAISE;
dbms_output.put_line('Too Many Rows : Exception');
dbms_output.put_line('Errorcode: ' || sqlcode || ' Error message: ' || sqlerrm);
WHEN OTHERS THEN
ROLLBACK;
RAISE;
dbms_output.put_line('Others : Eception');
dbms_output.put_line('Errorcode: ' || sqlcode || ' Error message: ' || sqlerrm);
END;
错误详细信息：
错误(15,5(： PLS-00103：在预期以下情况之一时遇到符号"COUNTS"：* & = - + ; 在 in 是 mod 余数不是 rem <> 或 ！= 或 ~=>= <= <> 和/或喜欢 like2 like4 likec 之间 || 多集成员子多集
就格式而言，有 3 个错误 - 一个缺少分号，两个缺少右括号。我用评论标记了它们;看一看。
作为异常处理部分：一旦你RAISE错误，就不会执行任何内容，所以你不会看到你想要的任何内容(即不会运行任何dbms_output(。基本上，由于您没有处理任何内容，因此可以省略整个部分。无论如何，任何错误都会由甲骨文引发。
CREATE OR REPLACE PROCEDURE "PROC_TOTAL_EVENTS"
AS
file_handle UTL_FILE.file_type;
utl_dir VARCHAR2 (255) := '/u01/apps/Miscellaneous/bin'; --> mising semi-colon
counts NUMBER;
counts1 NUMBER;
counts2 NUMBER;
counts3 NUMBER;
missed_a NUMBER;
missed_b NUMBER;
missed_c VARCHAR2 (50);
retrieved_buffer VARCHAR2 (100);
BEGIN
file_handle := UTL_FILE.fopen (utl_dir, 'spool.txt', 'W');
DBMS_OUTPUT.put_line ('File opened for write');
UTL_FILE.put_line (file_handle, 'Executing the counts for total_events');
DBMS_OUTPUT.put_line ('--------------------------------');
SELECT /*+ PARALLEL(PH,8)) */
COUNT (*) INTO counts FROM cdb_admin.CPM_PREF_EVENT_STG;
UTL_FILE.putf (file_handle,
'Total preference events sent to CPM from CDB',
counts);
DBMS_OUTPUT.put_line ('1 step success');
SELECT COUNT (DISTINCT cdb_pref_event_id)
INTO counts1
FROM cdb_admin.cpm_pref_result
WHERE cdb_pref_event_id IN (SELECT /*+ PARALLEL(PH,8)) */
cdb_pref_event_id
FROM cdb_admin.CPM_PREF_EVENT_STG ph); --> missing closing parenthesis
UTL_FILE.putf (file_handle,
'distinct count of total prefResults sent
by CPM to CDB',
counts1);
DBMS_OUTPUT.put_line ('2 step success');
SELECT COUNT (DISTINCT e.cdb_pref_event_id) Total_exception_count
INTO counts
FROM (SELECT DISTINCT
eh.cdb_customer_id cdb_customer_id,
eh.cdb_pref_event_id cdb_pref_event_id,
eh.supp_cd supp_cd
FROM cdb_admin.CPM_PREF_EVENT_STG eh
LEFT OUTER JOIN CDB_ADMIN.CPM_PREF_RESULT er
ON ( eh.cdb_customer_id = er.cdb_customer_id
AND eh.cdb_pref_event_id = er.cdb_pref_event_id)
WHERE er.cdb_pref_event_id IS NULL
AND er.cdb_customer_id IS NULL) r
JOIN CDB_ADMIN.cpm_pref_event_exception e
ON ( r.cdb_customer_id = e.cdb_customer_id
AND r.cdb_pref_event_id = e.cdb_pref_event_id)
WHERE e.supp_cd != 'PROFILE-NOT-
FOUND';
UTL_FILE.putf (file_handle,
'distinct count of total exceptions sent by CPM
to CDB',
counts2);
DBMS_OUTPUT.put_line ('3 step success');
SELECT COUNT (DISTINCT e.cdb_pref_event_id), e.supp_cd
FROM (SELECT DISTINCT
eh.cdb_customer_id cdb_customer_id,
eh.cdb_pref_event_id cdb_pref_event_id,
eh.supp_cd supp_cd
FROM (SELECT * FROM cdb_admin.CPM_PREF_EVENT_STG) eh
LEFT OUTER JOIN CDB_ADMIN.CPM_PREF_RESULT er
ON ( eh.cdb_customer_id = er.cdb_customer_id
AND eh.cdb_pref_event_id = er.cdb_pref_event_id)
WHERE er.cdb_pref_event_id IS NULL
AND er.cdb_customer_id IS NULL) r
JOIN CDB_ADMIN.cpm_pref_event_exception e
ON ( r.cdb_customer_id = e.cdb_customer_id
AND r.cdb_pref_event_id = e.cdb_pref_event_id)
GROUP BY e.supp_cd;
UTL_FILE.putf (file_handle, 'Counts bifurcation', counts3);
DBMS_OUTPUT.put_line ('4 step success');
IF counts = (counts1 + counts2)
THEN
UTL_FILE.putf (file_handle,
'Counts are matching',
counts,
(counts1 + counts2));
ELSE
DBMS_OUTPUT.put_line ('Counts are not matching, finding the missed
record');
SELECT DISTINCT r.cdb_customer_id, r.cdb_pref_event_id, r.supp_cd
INTO missed_a, missed_b, missed_c
FROM (SELECT DISTINCT
eh.cdb_customer_id cdb_customer_id,
eh.cdb_pref_event_id cdb_pref_event_id,
eh.supp_cd supp_cd
FROM (SELECT *
FROM cdb_admin.CPM_PREF_EVENT_STG eh
LEFT OUTER JOIN cdb_admin.cpm_pref_result er
ON ( eh.cdb_customer_id =
er.cdb_customer_id
AND eh.cdb_pref_event_id =
er.cdb_pref_event_id)
WHERE er.cdb_pref_event_id IS NULL
AND er.cdb_customer_id IS NULL) r
LEFT OUTER JOIN cdb_admin.cpm_pref_event_exception e
ON ( r.cdb_customer_id = e.cdb_customer_id
AND r.cdb_pref_event_id = e.cdb_pref_event_id)
WHERE e.cdb_pref_event_id IS NULL
AND e.cdb_customer_id IS NULL); --> missing closing parenthesis
UTL_FILE.putf (file_handle,
'missed record ID and country code',
missed_a,
missed_b,
missed_c);
END IF;
UTL_FILE.fclose (file_handle);
DBMS_OUTPUT.put_line ('File closed');
file_handle := UTL_FILE.fopen (utl_dir, 'TotEvents.txt', 'R');
DBMS_OUTPUT.put_line ('File opened for read');
UTL_FILE.get_line (file_handle, retrieved_buffer);
DBMS_OUTPUT.put_line ('Read success');
DBMS_OUTPUT.put_line (retrieved_buffer);
UTL_FILE.fclose (file_handle);
DBMS_OUTPUT.put_line ('File closed');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ROLLBACK;
RAISE;
DBMS_OUTPUT.put_line ('No Data Found : Exception');
DBMS_OUTPUT.put_line (
'Errorcode: ' || SQLCODE || ' Error message: ' || SQLERRM);
WHEN TOO_MANY_ROWS
THEN
ROLLBACK;
RAISE;
DBMS_OUTPUT.put_line ('Too Many Rows : Exception');
DBMS_OUTPUT.put_line (
'Errorcode: ' || SQLCODE || ' Error message: ' || SQLERRM);
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
DBMS_OUTPUT.put_line ('Others : Eception');
DBMS_OUTPUT.put_line (
'Errorcode: ' || SQLCODE || ' Error message: ' || SQLERRM);
END;