没有数据在oracle中找到错误 [英] No data Found error in oracle

问题描述

大家好，



在我目前的应用程序中，我有一个临时表和2个交易表。

我需要写SP从一个临时表中获取数据并更新或插入到事务表中的2个。

我的示例程序如下，但它不起作用。



我改变SP但得到错误没有找到数据。



如果有人有任何替代方法，请告诉我。



我使用的是CURSOR。

Hi all,

In my current app, I am having one temporary table and 2 transaction table.
I need to write the SP to take data from one temporary table and update or insert to 2 of the transaction table.
My sample procedure is like below, but its not working.

I alter the SP but got the error "No Data Found".

If anybody have any alternative way to do it please let me know.

I am using CURSOR.

Procedure    USP_OTC_TEMPTOACTUAL_DETAILS
   IS
 ERR_DESC VARCHAR2(4000);
 TCUSTOMER_CODE VARCHAR2(100);
 CUSTCODE VARCHAR2(100);
 TCUSTOMER_NAME VARCHAR2(100);
 TTOTAL_GROSS_DUE NUMBER;
 TOPENING_AMT NUMBER;
 TCURRENT_INVOICE_AMT NUMBER;
 TCOLLECTION_AMT NUMBER;
 TTOTAL_DUE NUMBER;
 TCLOSING_AMT NUMBER;
 TUNAPPLIED_AMT NUMBER;
 TNET_TOTAL_DUE NUMBER;
 TIS_ACTIVE VARCHAR2(10);
 TTEAM_LEAD VARCHAR2(100);
 TACC_MGR VARCHAR2(100);
 CUSTCNT NUMBER;
 TOTC_DESC1 VARCHAR2(100); TOTC_AMT1 NUMBER; TOTC_REPORTING_DATE1 DATE; TBD_REMARKS1 VARCHAR2(100); TRESOLUTION_DATE1 DATE;
TOTC_DESC2 VARCHAR2(100); TOTC_AMT2 NUMBER; TOTC_REPORTING_DATE2 DATE; TBD_REMARKS2 VARCHAR2(100); TRESOLUTION_DATE2 DATE;
TOTC_DESC3 VARCHAR2(100); TOTC_AMT3 NUMBER; TOTC_REPORTING_DATE3 DATE; TBD_REMARKS3 VARCHAR2(100); TRESOLUTION_DATE3 DATE;
TOTC_DESC4 VARCHAR2(100); TOTC_AMT4 NUMBER; TOTC_REPORTING_DATE4 DATE; TBD_REMARKS4 VARCHAR2(100); TRESOLUTION_DATE4 DATE;
TOTC_DESC5 VARCHAR2(100); TOTC_AMT5 NUMBER; TOTC_REPORTING_DATE5 DATE; TBD_REMARKS5 VARCHAR2(100); TRESOLUTION_DATE5 DATE;
TOTC_ID NUMBER;


    CURSOR C1 IS
    SELECT CUSTOMER_CODE,CUSTOMER_NAME,TOTAL_GROSS_DUE,OPENING_AMT,CURRENT_INVOICE_AMT,COLLECTION_AMT,TOTAL_DUE,
                CLOSING_AMT,UNAPPLIED_AMT,NET_TOTAL_DUE,IS_ACTIVE,TEAM_LEAD,ACC_MGR,
                OTC_DESC1,OTC_AMT1,OTC_REPORTING_DATE1,BD_REMARKS1,RESOLUTION_DATE1,
                OTC_DESC2,OTC_AMT2,OTC_REPORTING_DATE2,BD_REMARKS2,RESOLUTION_DATE2,
                OTC_DESC3,OTC_AMT3,OTC_REPORTING_DATE3,BD_REMARKS3,RESOLUTION_DATE3,
                OTC_DESC4,OTC_AMT4,OTC_REPORTING_DATE4,BD_REMARKS4,RESOLUTION_DATE4,
                OTC_DESC5,OTC_AMT5,OTC_REPORTING_DATE5,BD_REMARKS5,RESOLUTION_DATE5
     FROM OTC_TEMP_UPLOAD;
   
   BEGIN
    OPEN C1;
    LOOP
    FETCH C1 INTO TCUSTOMER_CODE,TCUSTOMER_NAME,TTOTAL_GROSS_DUE,TOPENING_AMT,TCURRENT_INVOICE_AMT,TCOLLECTION_AMT,TTOTAL_DUE,
                TCLOSING_AMT,TUNAPPLIED_AMT,TNET_TOTAL_DUE,TIS_ACTIVE,TTEAM_LEAD,TACC_MGR,
                TOTC_DESC1,TOTC_AMT1,TOTC_REPORTING_DATE1,TBD_REMARKS1,TRESOLUTION_DATE1,
                TOTC_DESC2,TOTC_AMT2,TOTC_REPORTING_DATE2,TBD_REMARKS2,TRESOLUTION_DATE2,
                TOTC_DESC3,TOTC_AMT3,TOTC_REPORTING_DATE3,TBD_REMARKS3,TRESOLUTION_DATE3,
                TOTC_DESC4,TOTC_AMT4,TOTC_REPORTING_DATE4,TBD_REMARKS4,TRESOLUTION_DATE4,
                TOTC_DESC5,TOTC_AMT5,TOTC_REPORTING_DATE5,TBD_REMARKS5,TRESOLUTION_DATE5;
   --INSERT INTO TEMP (VALS) VALUES('CUSTOMER DETAILS ' || TCUSTOMER_CODE);
   SELECT COUNT(*) INTO CUSTCNT FROM OTC_CUSTOMER_DETAILS WHERE CUSTOMER_CODE=TCUSTOMER_CODE;
   --INSERT INTO TEMP (VALS) VALUES('TOTAL GROSS DUE ' || TTOTAL_GROSS_DUE);
   --INSERT INTO TEMP (VALS) VALUES('CUSTOMER DETAILS ' || CUSTCNT);

    IF CUSTCNT>0 THEN
        SELECT DISTINCT CUSTOMER_CODE INTO CUSTCODE FROM OTC_CUSTOMER_DETAILS WHERE CUSTOMER_CODE=TCUSTOMER_CODE;
        --INSERT INTO TEMP (VALS) VALUES('CUSTOMERCODE ' || CUSTCODE);
        --COMMIT;
    END IF;

    IF (CUSTCNT=0 OR TCUSTOMER_CODE != CUSTCODE) THEN
        BEGIN

            INSERT INTO OTC_CUSTOMER_DETAILS
             (
               CUSTOMER_ID,CUSTOMER_CODE,CUSTOMER_NAME,TOTAL_GROSS_DUE,OPENING_AMT,CURRENT_INVOICE_AMT,
               COLLECTION_AMT,TOTAL_DUE,CLOSING_AMT,UNAPPLIED_AMT,NET_TOTAL_DUE,IS_ACTIVE,TEAM_LEAD,ACC_MGR
               )
               VALUES               
               (1,TCUSTOMER_CODE,TCUSTOMER_NAME,ROUND(TTOTAL_GROSS_DUE,5),ROUND(TOPENING_AMT,5),ROUND(TCURRENT_INVOICE_AMT,5),ROUND(TCOLLECTION_AMT,5),
                ROUND(TTOTAL_DUE,5),ROUND(TCLOSING_AMT,5),ROUND(TUNAPPLIED_AMT,5),ROUND(TNET_TOTAL_DUE,5),TIS_ACTIVE,TTEAM_LEAD,TACC_MGR);

              IF (LENGTH(TOTC_DESC1)>0) THEN
               SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC1;
               INSERT INTO OTC_DETAILS(OTC_ID,CUSTOMER_CODE,OTC_AMT,OTC_REPORTING_DATE,BD_REMARKS,RESOLUTION_DATE)
                VALUES (TOTC_ID,TCUSTOMER_CODE,ROUND(TOTC_AMT1,5),TOTC_REPORTING_DATE1,TBD_REMARKS1,TRESOLUTION_DATE1);
               --INSERT INTO TEMP (VALS) VALUES('OTCID ' || TOTC_ID);
              END IF;

            IF (LENGTH(TOTC_DESC2)>0) THEN
                BEGIN
                SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC2;
                INSERT INTO OTC_DETAILS(OTC_ID,CUSTOMER_CODE,OTC_AMT,OTC_REPORTING_DATE,BD_REMARKS,RESOLUTION_DATE)
                    VALUES (TOTC_ID,TCUSTOMER_CODE,ROUND(TOTC_AMT2,5),TOTC_REPORTING_DATE2,TBD_REMARKS2,TRESOLUTION_DATE2);
                END;
            END IF;

            IF (LENGTH(TOTC_DESC3)>0) THEN
                SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC3;
              INSERT INTO OTC_DETAILS(OTC_ID,CUSTOMER_CODE,OTC_AMT,OTC_REPORTING_DATE,BD_REMARKS,RESOLUTION_DATE)
                VALUES (TOTC_ID,TCUSTOMER_CODE,ROUND(TOTC_AMT3,5),TOTC_REPORTING_DATE3,TBD_REMARKS3,TRESOLUTION_DATE3);
            END IF;

            IF (LENGTH(TOTC_DESC4)>0) THEN
                SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC4;
              INSERT INTO OTC_DETAILS(OTC_ID,CUSTOMER_CODE,OTC_AMT,OTC_REPORTING_DATE,BD_REMARKS,RESOLUTION_DATE)
                VALUES (TOTC_ID,TCUSTOMER_CODE,ROUND(TOTC_AMT4,5),TOTC_REPORTING_DATE4,TBD_REMARKS4,TRESOLUTION_DATE4);
            END IF;

            IF (LENGTH(TOTC_DESC5)>0) THEN
                SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC5;
              INSERT INTO OTC_DETAILS(OTC_ID,CUSTOMER_CODE,OTC_AMT,OTC_REPORTING_DATE,BD_REMARKS,RESOLUTION_DATE)
                VALUES (TOTC_ID,TCUSTOMER_CODE,ROUND(TOTC_AMT5,5),TOTC_REPORTING_DATE5,TBD_REMARKS5,TRESOLUTION_DATE5);
            END IF;
        COMMIT;
        END;
    ELSE
    BEGIN
          UPDATE OTC_CUSTOMER_DETAILS set
               CUSTOMER_CODE=TCUSTOMER_CODE,CUSTOMER_NAME=TCUSTOMER_NAME,TOTAL_GROSS_DUE=ROUND(TTOTAL_GROSS_DUE,5),
               OPENING_AMT=ROUND(TOPENING_AMT,5),CURRENT_INVOICE_AMT=ROUND(TCURRENT_INVOICE_AMT,5),
               COLLECTION_AMT=ROUND(TCOLLECTION_AMT,5),TOTAL_DUE=ROUND(TTOTAL_DUE,5),CLOSING_AMT=ROUND(TCLOSING_AMT,5),
               UNAPPLIED_AMT=ROUND(TUNAPPLIED_AMT,5),NET_TOTAL_DUE=ROUND(TNET_TOTAL_DUE,5),
               IS_ACTIVE=TIS_ACTIVE,TEAM_LEAD=TTEAM_LEAD,ACC_MGR=TACC_MGR             
          
         WHERE CUSTOMER_CODE=TCUSTOMER_CODE;
         --INSERT INTO TEMP (VALS) VALUES('CUSTOMEE NAME ' || TCUSTOMER_NAME);
              IF (LENGTH(TOTC_DESC1)>0) THEN
                  SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC2;
                   UPDATE OTC_DETAILS SET
                        OTC_ID=TOTC_ID,
                        OTC_AMT=ROUND(TOTC_AMT1,5),OTC_REPORTING_DATE=TOTC_REPORTING_DATE1,
                        BD_REMARKS=TBD_REMARKS1,RESOLUTION_DATE=TRESOLUTION_DATE1
                   WHERE CUSTOMER_CODE=TCUSTOMER_CODE;
              END IF;

            IF (LENGTH(TOTC_DESC2)>0) THEN
                SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC2;
              UPDATE OTC_DETAILS SET
                        OTC_ID=TOTC_ID,
                        OTC_AMT=ROUND(TOTC_AMT2,5),OTC_REPORTING_DATE=TOTC_REPORTING_DATE2,
                        BD_REMARKS=TBD_REMARKS2,RESOLUTION_DATE=TRESOLUTION_DATE2
                   WHERE CUSTOMER_CODE=TCUSTOMER_CODE;
            END IF;

            IF (LENGTH(TOTC_DESC3)>0) THEN
                SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC3;
              UPDATE OTC_DETAILS SET
                        OTC_ID=TOTC_ID,
                        OTC_AMT=ROUND(TOTC_AMT3,5),OTC_REPORTING_DATE=TOTC_REPORTING_DATE3,
                        BD_REMARKS=TBD_REMARKS3,RESOLUTION_DATE=TRESOLUTION_DATE3
                   WHERE CUSTOMER_CODE=TCUSTOMER_CODE;
            END IF;

            IF (LENGTH(TOTC_DESC4)>0) THEN
                SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC4;
              UPDATE OTC_DETAILS SET
                        OTC_ID=TOTC_ID,
                        OTC_AMT=ROUND(TOTC_AMT4,5),OTC_REPORTING_DATE=TOTC_REPORTING_DATE4,
                        BD_REMARKS=TBD_REMARKS4,RESOLUTION_DATE=TRESOLUTION_DATE4
                   WHERE CUSTOMER_CODE=TCUSTOMER_CODE;
            END IF;

            IF (LENGTH(TOTC_DESC5)>0) THEN
                SELECT OTC_ID INTO TOTC_ID FROM OTC_MASTER WHERE OTC_DESC=TOTC_DESC5;
              UPDATE OTC_DETAILS SET
                        OTC_ID=TOTC_ID,
                        OTC_AMT=ROUND(TOTC_AMT5,5),OTC_REPORTING_DATE=TOTC_REPORTING_DATE5,
                        BD_REMARKS=TBD_REMARKS5,RESOLUTION_DATE=TRESOLUTION_DATE5
                   WHERE CUSTOMER_CODE=TCUSTOMER_CODE;
            END IF;
            COMMIT;
         END;
    END IF;
 END LOOP;
 CLOSE C1;

EXCEPTION
     WHEN others THEN
     ROLLBACK;
       ERR_DESC:=SQLERRM;
        INSERT INTO OTC_EXCEPTION(ERROR_DESCRIPTION,SP_NAME) VALUES(ERR_DESC,'USP_OTC_TEMPTOACTUAL_DETAILS') ;
            COMMIT;
END; -- Procedure USP_OTC_TEMPTOATCUAL_UPDATE_N


-- End of DDL script for USP_OTC_TEMPTOACTUAL_DETAILS

在此先感谢

Yogesh

Thanks In Advance
Yogesh

推荐答案

您好



查看以下链接



ORA-01403：未找到数据提示 [ ^ ]

Oracle/PLSQL：避免PLSQL代码中出现未找到数据错误 [ ^ ]



问候，

GVPrabu

Hi

Check the following links

ORA-01403: no data found tips[^]
Oracle/PLSQL: Avoid "data not found" error in PLSQL code[^]

Regards,
GVPrabu

这篇关于没有数据在oracle中找到错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！