我有下面的SQL代码，它在TOAD中成功执行（没有INTO语句）。然而，当我将代码插入一个sql程序（使用INTO）并编译时，“from C”行被突出显示，我得到了ORA-44201错误：游标需要重新解析。我是一个新手，所以我一无所知，我的DBA朋友从来没有见过这个错误。有什么想法吗
CREATE OR REPLACE PROCEDURE Distribute_CA_Accounts
IS
BEGIN
/* This procedure replaces RM103r */
DECLARE
/* Output File Variables */
file_location VARCHAR2 ( 60 ) := 'WCIS_OUTPUT';
file_name VARCHAR2 ( 60 ) := 'UEF_Assign.csv';
vOutHandle UTL_FILE.file_type;
sep VARCHAR2 ( 1 ) := ',';
header_line VARCHAR2 ( 500 );
/* Processing Variables */
old_ca VARCHAR2 ( 5 );
old_ca_name VARCHAR2 ( 30 ) := 'UNASSIGNED';
new_ca VARCHAR2 ( 5 );
new_ca_name VARCHAR2 ( 30 );
volunteer_ca VARCHAR2 ( 5 ) := 'D743';
ca_code_in VARCHAR ( 5 );
uef_pct NUMBER;
ca_name VARCHAR ( 20 );
ca_code VARCHAR ( 5 );
clm_count VARCHAR ( 5 );
calc_code VARCHAR ( 5 );
calc_name VARCHAR ( 40 );
calc_diff NUMBER;
calc_uef_pct NUMBER;
calc_pct NUMBER;
/* Email Variables */
email_list VARCHAR2 ( 1 );
output_line VARCHAR2 ( 500 );
mailed_from VARCHAR2 ( 500 ) := 'oicwvaccisa@wv.gov';
send_mail_to VARCHAR2 ( 500 );
email_subject VARCHAR2 ( 60 );
email_message VARCHAR2 ( 500 );
/* individual_id VARCHAR2(5); */
individual_address VARCHAR2 ( 60 );
/* Pulls accounts for main procedure */
/* THIS CODE EXCLUDES RSLVD ACCOUNTS */
CURSOR get_unassigned_recs
IS
SELECT
policy_num account_num,
bus_seq_num account_seq,
cm_unit_code b_code,
NVL ( feid_num, '11-1111111' ) feid_num,
NVL ( manual_class, '9999' ) manual_class
FROM
business B
WHERE
fund_type_code IN ( 'P',
'U',
'N') AND
collection_status_code IN ('UNINS',
'COVRD',
'INSUR',
'DEFLT',
'INJ',
'UNCOL',
'PEND',
'PEHRG' ) AND
cm_unit_code IS NULL;
TYPE unarray IS TABLE OF get_unassigned_recs%ROWTYPE;
curarray1 unarray;
/* Pulls list of users for distribution of report */
/* Pulls list of users for distribution of report */
CURSOR get_email_group
IS
SELECT
email_addr
FROM
report_dist
WHERE
so_module = 'RM103R' AND
email_addr IS NOT NULL;
TYPE uarray IS TABLE OF get_email_group%ROWTYPE;
curarray2 uarray;
BEGIN
/* sets up output file */
header_line :=
'Account #' ||
sep ||
'New CA' ||
sep ||
'New CA Name';
vOutHandle :=
UTL_FILE.fopen ( file_location, file_name, 'w' );
UTL_FILE.put_line ( vOutHandle, header_line );
/* sets up email */
email_subject := 'CA Assign';
email_message :=
'Attached is list of unassigned accounts ' ||
'assigned a Credit Analyst : ';
email_message :=
email_message ||
CHR ( 13 )||
CHR ( 10 )||
' ' ||
CHR ( 13 )||
CHR ( 10 )||
CHR ( 13 )||
CHR ( 10 )||
'If there is a problem please notify IT. Thank you.';
/* This is the main procedure */
OPEN get_unassigned_recs;
LOOP
FETCH get_unassigned_recs
BULK
COLLECT INTO curarray1;
FOR i IN 1 .. curarray1.COUNT
LOOP
IF ( curarray1 ( i ).manual_class = '8840' OR
curarray1 ( i ).manual_class = '7707' OR
curarray1 ( i ).manual_class = '7711' AND
( curarray1 ( i ).feid_num NOT IN ('11-1111111', '111-11-1111', '00-0000000', '000-00-0000')))
THEN
new_ca := volunteer_ca;
new_ca_name := 'DWHITE2';
ELSE
/* CALL ALALYTICS */
WITH C AS (SELECT
b.cm_unit_code,
NVL(c.uef_percentage, 0) as uef_pct,
c.user_name,
ca_name,
count(*) as clm_count
FROM business b right outer join cm_unit_code c
on c.CODE = b.CM_UNIT_CODE
WHERE
b.cm_unit_code NOT IN ('9747',
'BANK',
'9999',
'SELF') AND
b.fund_type_code IN ('P',
'U',
'N') AND
b.collection_status_code IN ('UNINS',
'COVRD',
'INSUR',
'DEFLT',
'INJ',
'UNCOL',
'PEND',
'PEHRG') AND
c.uef_percentage > 0
group by b.cm_unit_code, c.user_name, c.UEF_PERCENTAGE)
SELECT cm_unit_code ca_code,
uef_pct - (clm_count/ sum (clm_count) over ())*100,
uef_pct,
(clm_count/ sum (clm_count) over ())*100,
ca_name
INTO
calc_code,
calc_diff,
calc_uef_pct,
calc_pct,
calc_name
from C
order by 2 desc
FETCH FIRST ROW ONLY;
new_ca := calc_code;
new_ca_name := calc_name;
/* new_ca := ca_code;
new_ca_name := ca_name; */
END IF;
output_line :=
curarray1 ( i ).account_num ||
'-' ||
curarray1 ( i ).account_seq ||
sep ||
new_ca ||
sep ||
new_ca_name;
UTL_FILE.put_line ( vOutHandle, output_line );
UPDATE
business
SET
cm_unit_code = new_ca
WHERE
policy_num = curarray1 ( i ).account_num AND
bus_seq_num = curarray1 ( i ).account_seq;
INSERT INTO
endorsement (
endorse_seq_num,
policy_num,
bus_seq_num,
endorse_code,
endorse_date,
old_value,
new_value,
user_name
)
VALUES
(
endorsement_seq.NEXTVAL,
curarray1 ( i ).account_num,
curarray1 ( i ).account_seq,
'751',
TRUNC ( SYSDATE ),
old_ca,
new_ca,
'WVACCISA'
);
END LOOP;
OPEN get_email_group;
LOOP
FETCH get_email_group
BULK
COLLECT INTO curarray2;
FOR i IN 1 .. curarray2.COUNT
LOOP
IF ( send_mail_to IS NULL ) THEN
send_mail_to := curarray2 ( i ).email_addr;
ELSE
send_mail_to :=
send_mail_to ||
',' ||
curarray2 ( i ).email_addr;
END IF;
END LOOP;
EXIT WHEN curarray2.COUNT = 0;
END LOOP;
CLOSE get_email_group;
send_email.send ( ToList => send_mail_to,
Subject => email_subject,
Body => email_message,
FromEmail => mailed_from,
AttachList => file_name,
Directory => file_location
);
EXIT WHEN curarray1.COUNT = 0;
END LOOP;
CLOSE get_unassigned_recs;
/* COMMIT; */
ROLLBACK;
UTL_FILE.FCLOSE_ALL;
END;
END Distribute_CA_Accounts;字符串
2条答案
按热度按时间368yc8dk1#
显然是“FETCH FIRST ROW ONLY”代码导致了错误。关于创建一个背景或辅助光标只是拉第一行的东西。我想这就是关于动态SQL的错误出现的地方。感谢各位的帮助！
ie3xauqp2#
不是FETCH FIRST ROW ONLY而是ORDER BY导致此错误。