oracle - PL/SQL : ORA-44201: cursor needs to be reparsed

Question

我有以下在 TOAD 中成功执行的 sql 代码(没有 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 ) := '<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b9d6d0dacecfd8dadad0cad8f9cecf97ded6cf" rel="noreferrer noopener nofollow">[email protected]</a>';
        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;

Answer 1

显然是“FETCH FIRST ROW ONLY”代码导致了错误。有关创建背景或辅助光标以仅拉出第一行的内容。我猜这就是关于动态 sql 的错误出现的地方。感谢大家的帮助!