Package logic

I have a package that I am trying to figure out why it doesn't go to a certain part of my logic. It constantly goes into the NO_DATA_FOUND portion of my exception

The code is below and Ive been messing with it a lot so it looks worse then it did prior to my issue.....Can someone tell me why it doesn't end up doing an INSERT into my TEMP_BASE_PCOV_NAME table and always ends up in the NO_DATA FOUND from the INSURED_ENTITY query ? I even did a join on the source(COVERAGE) to the INSURED_ENTITY table with all the criteria from the source Select statement and the INSURED_ENTITY table and got back a lot of rows?

create or replace package body pcov_test_package_jk
as
----Created by JK for NJCRIB ----

procedure BuildPCOV_NAMEKEYS_Table(p_Successful  OUT NUMBER)
is
      TYPE Pcov_NameKeys_Rec_Type IS RECORD(
            POL_RECKEY VARCHAR2(20)
            ,POLNBR VARCHAR2(18 BYTE)
            ,CVG_ID VARCHAR2(10 BYTE)
            ,COV_EFFDT DATE
            ,COV_EXPDT DATE
            ,LCF_IND CHAR(1 BYTE)
            ,CARRID CHAR(5 BYTE));
       
      TYPE Pcov_NameKeys_tab_type IS TABLE OF Pcov_NameKeys_Rec_Type
         INDEX BY BINARY_INTEGER;
       
      l_temp_NAMEKEYS Pcov_NameKeys_tab_type;    

   i BINARY_INTEGER;
   j BINARY_INTEGER;
       -----------------------------------------------------------------------
      --Source data Type variables COVERAGE table
      l_COV_polNbr VARCHAR2(18);
      l_COV_cvg_ID VARCHAR2(10 BYTE);
      l_COV_dateTest VARCHAR2(20);
      l_COV_pCARRID CHAR(5);
      l_COV_covEffDate DATE;
      l_COV_covExpDate DATE;
      l_COV_LCFInd CHAR(1 BYTE);
     

     
      --Build Key Variables
      l_FINAL_ExpYr VARCHAR2(4 BYTE);
      l_FINAL_ExpMo VARCHAR2(2 BYTE);
      l_FINAL_ExpDa VARCHAR2(2 BYTE);
      l_FINAL_EffYr VARCHAR2(4 BYTE);
      l_FINAL_EffMo VARCHAR2(2 BYTE);
      l_FINAL_EffDa VARCHAR2(2 BYTE);
      l_FINAL_Cono CHAR(5 BYTE);
     
      l_FINAL_LCFInd CHAR(1 BYTE);
      l_FINAL_SeqNum NUMBER;
      l_FINAL_polNbr VARCHAR2(18);
      l_FINAL_CVGID VARCHAR2(10 BYTE);
      l_FINAL_CARRID CHAR(5);
      l_FINAL_EffDate DATE;
      l_FINAL_ExpDate DATE;
      l_FINAL_tabKey VARCHAR2(42 BYTE);      

      /*
      Misc Variables
      */
     
      iCounter NUMBER;  --Counter for Names data set
      iNameCtr NUMBER;
      l_StartSelectionDate DATE;
      l_STARTTIME TIMESTAMP;
      l_ENDTIME TIMESTAMP;
      l_TOTAL_CR_FOUND_COUNT NUMBER;
      l_Policy_Hold VARCHAR2(18 Byte);
      l_Cov_EFFDT_HOLD DATE;
      l_MS_HOLD NUMBER;
      l_ENDORSE_CD_NBR VARCHAR2(6 BYTE);
      v_code VARCHAR(100 BYTE);
      v_errm VARCHAR(1000 BYTE);
      --INsuredsName
      l_INS_ENTITY_NAME VARCHAR2(90 BYTE);
      Prev_i INTEGER;
      jHold NUMBER;



      BEGIN
     
        EXECUTE IMMEDIATE 'TRUNCATE TABLE PCOV_TEST_MESSAGE_TAB';        
        EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_BASE_PCOV_NAMES';
       
        l_COV_covExpDate := ADD_MONTHS(Current_Date,-72);
        l_ENDORSE_CD_NBR := '290303';
        l_FINAL_SeqNum := 0;
       
        --Source Data Statement      
        SELECT '' POL_RECKEY, POLNBR, CVG_ID ,COV_EFFDT ,COV_EXPDT ,LCF_IND,CARRID
        Bulk Collect Into l_temp_NAMEKEYS
        FROM SPECTRUM.COVERAGE WHERE COV_EXPDT > l_COV_covExpDate AND (STATUS != 'V') AND (LCF_IND = 'Y')
        ORDER BY CARRID ASC, COV_EFFDT ASC;
                 
        l_TOTAL_CR_FOUND_COUNT := l_temp_NAMEKEYS.count;
        i := l_temp_NAMEKEYS.first;
       
        SYS.DBMS_OUTPUT.PUT_LINE('Total number of Coverage source rows : ' || l_TOTAL_CR_FOUND_COUNT);
 
        WHILE (i IS NOT NULL) LOOP
         
            --Set NAMEKEYS Variables
            l_COV_polNbr :=  RPAD(l_temp_NAMEKEYS(i).POLNBR, 18, ' ' );            
                       
            If RegExp_INSTR(l_COV_polNbr,'ASSIGN') > 0  Then
              Insert Into PCOV_TEST_MESSAGE_TAB(ERR_ID,ERR_MESSAGE, ERR_TIMESTAMP) Values  (ERR_ID_TAB_seq.NEXTVAL,'**********POLICY NUMBER CONTAINS ASSIGNED; BYPASSED **************************** : ' || i,SYSTIMESTAMP);
              COMMIT;              
              i := l_temp_NAMEKEYS.next(i);
              CONTINUE;
            end if;

            l_COV_covExpDate := l_temp_NAMEKEYS(i).COV_EXPDT;
            l_COV_cvg_id :=  l_temp_NAMEKEYS(i).CVG_ID;
            l_COV_covEffDate :=  l_temp_NAMEKEYS(i).COV_EFFDT;
            l_COV_pCARRID := l_temp_NAMEKEYS(i).CARRID;
            l_COV_LCFInd  := l_temp_NAMEKEYS(i).LCF_IND;
           
            --SYS.DBMS_OUTPUT.PUT_LINE('Variables for Insured Name: EffDate: ' || l_COV_covEffDate || 'COVGID: ' || l_COV_cvg_id  || ' POLNBR: ' || l_COV_polNbr);
              BEGIN
                   --QUERY ALWAYS ENDS UP IN THE NO_DATA_FOUND????
                  Select INSURED_NAME into l_INS_ENTITY_NAME from INSURED_ENTITY where CVG_ID= l_COV_cvg_id AND POLNBR= l_COV_polNbr AND COV_EFFDT = l_COV_covEffDate  AND ROWNUM = 1;
                  SYS.DBMS_OUTPUT.PUT_LINE('INSURED NAME: ' || l_INS_ENTITY_NAME);
                  if LENGTH(l_INS_ENTITY_NAME) = 0 then
                      Insert Into PCOV_TEST_MESSAGE_TAB(ERR_ID,ERR_MESSAGE, ERR_TIMESTAMP) Values  (ERR_ID_TAB_seq.NEXTVAL, IF STATEMENT INSURED_NAME LENGTH is 0; BYPASSED : ' || i,SYSTIMESTAMP);
                      COMMIT;

                  End If;
              EXCEPTION
              WHEN NO_DATA_FOUND THEN
                --ALWAYS ENDS UP IN HERE
                Insert Into PCOV_TEST_MESSAGE_TAB(ERR_ID, ERR_MESSAGE, ERR_TIMESTAMP) Values  (ERR_ID_TAB_seq.NEXTVAL, 'WHERE CVG_ID = ' || l_COV_cvg_id || ' AND POLNBR = ' || l_COV_polNbr || ' AND COV_EFFDT = ' || l_COV_covEffDate ,SYSTIMESTAMP);
                COMMIT;
                                 i := l_temp_NAMEKEYS.next(i);
                  Continue;
              END;  
            --Created Function that will return 1 if Valid Date and 0 for not a Valid Date
            If Is_Date(l_COV_covExpDate) = 0 Then    
                --BYPASS THE RECORD
                Insert Into PCOV_TEST_MESSAGE_TAB (ERR_ID,ERR_MESSAGE, ERR_TIMESTAMP) Values  (ERR_ID_TAB_seq.NEXTVAL, '*********NO EXPDATE; BYPASSED ************* : ',SYSTIMESTAMP);
                COMMIT;

                i := l_temp_NAMEKEYS.next(i);                  
                CONTINUE;
            End If;

            If Date_Diff('m', l_COV_covExpDate, l_COV_covEffDate) > 36 Then --Policies must not be greater than 3 years, I have included the extra day for leap year.              
                Insert Into PCOV_TEST_MESSAGE_TAB (ERR_ID,ERR_MESSAGE, ERR_TIMESTAMP) Values  (ERR_ID_TAB_seq.NEXTVAL,'Error Policy Greater than 3 years: ' ||  'CVG_ID: ' || l_COV_cvg_id || ' COV_EFFDT: ' || l_COV_covEffDate || ' POLNBR: ' || l_COV_polNbr,SYSTIMESTAMP);
                COMMIT;
                SYS.DBMS_OUTPUT.PUT_LINE('Error Policy Greater than 3 years: ' ||  'CVG_ID: ' || l_COV_cvg_id || ' COV_EFFDT: ' || l_COV_covEffDate || ' POLNBR: ' || l_COV_polNbr);
                --GOTO bypass_curr_rec;
                i := l_temp_NAMEKEYS.next(i);
                CONTINUE;
            End If;
           
            --Created Built function to return the Date parts of the year...Pass in Y for Year, D for Day, M for Month and a date and it will return the part of the date you passed in
            l_FINAL_ExpYr := GET_DATE_PART('y', l_COV_covExpDate);
            l_FINAL_ExpMo := GET_DATE_PART('m', l_COV_covExpDate);
            l_FINAL_ExpDa := GET_DATE_PART('d', l_COV_covExpDate);
            l_FINAL_EffDate := l_COV_covEffDate;
            l_FINAL_Cono := SUBSTR(l_COV_pCARRID, 3, 3);
           
            l_FINAL_LCFInd := l_COV_LCFInd;
            l_FINAL_CVGID := l_COV_cvg_id;
            l_FINAL_CARRID := l_COV_pCARRID;
            l_FINAL_polNbr := l_COV_polNbr;
           
            --EFF DATE
            l_FINAL_SeqNum := l_FINAL_SeqNum + 1;
            l_FINAL_SeqNum := LPAD(l_FINAL_SeqNum, 6, '0');
            l_FINAL_EffYr := GET_DATE_PART('y', l_COV_covEffDate);
            l_FINAL_EffMo := GET_DATE_PART('m', l_COV_covEffDate);
            l_FINAL_EffDa := GET_DATE_PART('d', l_COV_covEffDate);          
 
 
            l_FINAL_tabKey := Build_Table_Key(l_FINAL_EffYr, l_FINAL_EffMo, l_FINAL_EffDa, l_FINAL_CVGID, l_FINAL_SeqNum, l_FINAL_polNbr, l_FINAL_Cono);
 
            SYS.DBMS_OUTPUT.PUT_LINE('Insert should happen for Key: ' || l_FINAL_tabKey);
            --INSERT INTO NAMES TABLE
            Insert Into TEMP_BASE_PCOV_NAMES (NAME_KEY, NAME_POL_KEY, NAME_SEQ, INSURED_NAME, NAME_NJTIN) Values (i, l_FINAL_tabKey, l_FINAL_SeqNum, l_INS_ENTITY_NAME, l_FINAL_polNbr); --l_FINAL_SeqNum
            COMMIT;
           
           
            i := l_temp_NAMEKEYS.next(i);  
        END LOOP;
       
       SYS.DBMS_OUTPUT.PUT_LINE('End of iteration : ' || i);      
     


end BuildPCOV_NAMEKEYS_Table;
   
end pcov_test_package_jk;
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I would use dbms_output to display the values of:
l_COV_cvg_id
l_COV_polNbr
l_COV_covEffDate

Something in the selects where clause finds no data.

If any or all of the values is null, that might be the problem because nothing can '=' null so nothing can be returned from the select.
0
jknj72Author Commented:
Yes I did but you can see I commented it out....I also did a join from the source and the query in question and it brought back many rows. Actually brought back more rows then the source, meaning theres a one to many from Coverage to INSURED_ENTITY tables?

Did a
Selet i.INSURED_NAME, c.* from COVERAGE
inner join INSURANCE_ENTITY i on c.CVGID = i.CVG_ID
AND c.PolNBR = i.PolNBR
AND c.COV_EFFDAT = i.COV_EFFDAT
COV_EXPDT > l_COV_covExpDate AND (c.STATUS != 'V') AND (c.LCF_IND = 'Y')


and it returnedrows so Im confused?   Any thoughts?

Sorry my keyboard is messed up...
0
slightwv (䄆 Netminder) Commented:
>>I did but you can see I commented it out...

Missed that.

The query you just posted access the NSURANCE_ENTITY table.  The table in the code is INSURED_ENTITY so I'm not sure what the above query is supposed to tell me or is the the solution?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jknj72Author Commented:
From Coverage c (forgot the c)
0
jknj72Author Commented:
yyour right it does query the INSURANCE_ENTITY ta ble but the source table supplies the parameters to the WHERE clause
where CVG_ID= l_COV_cvg_id AND POLNBR= l_COV_polNbr AND COV_EFFDT = l_COV_covEffDate  AND ROWNUM = 1;
so, if Im not missing something the INNER JOIN should be the same(or almost the same anyway) as the query with the variables..No?
0
slightwv (䄆 Netminder) Commented:
The query you posted in the original query and the query you posted that returns rows uses two different tables.  I don't see how they can show where one works and the other doesn't.
0
jknj72Author Commented:
What Im trying to convey is that if the first query returns rows and the values from that query that are used to get rows back from the second query, that the 2nd query should return rows. Im doing something wrong

1st query
SELECT '' POL_RECKEY, POLNBR, CVG_ID ,COV_EFFDT ,COV_EXPDT ,LCF_IND,CARRID
Bulk Collect Into l_temp_NAMEKEYS
FROM SPECTRUM.COVERAGE WHERE COV_EXPDT > l_COV_covExpDate AND (STATUS != 'V') AND (LCF_IND = 'Y')
ORDER BY CARRID ASC, COV_EFFDT ASC;

--This might be the issue now that I see it??? Unless you see something else?
l_COV_polNbr :=  RPAD(l_temp_NAMEKEYS(i).POLNBR, 18, ' ' );      
l_COV_covExpDate := l_temp_NAMEKEYS(i).COV_EXPDT;
l_COV_cvg_id :=  l_temp_NAMEKEYS(i).CVG_ID;
l_COV_covEffDate :=  l_temp_NAMEKEYS(i).COV_EFFDT;
l_COV_pCARRID := l_temp_NAMEKEYS(i).CARRID;

2nd query using vars1 from 1st query
Select INSURED_NAME into l_INS_ENTITY_NAME
from INSURED_ENTITY
where CVG_ID= l_COV_cvg_id AND POLNBR= l_COV_polNbr AND COV_EFFDT = l_COV_covEffDate  AND ROWNUM = 1;

--So I figured Id try this
--Wouldn't this essentially tell me if I should get rows returned from the 2nd query?
 Selet i.INSURED_NAME, c.* from COVERAGE c
 inner join INSURANCE_ENTITY i on c.CVGID = i.CVG_ID
 AND c.PolNBR = i.PolNBR
 AND c.COV_EFFDAT = i.COV_EFFDAT
 Where c.COV_EXPDT > l_COV_covExpDate AND (c.STATUS != 'V') AND (c.LCF_IND = 'Y')
0
slightwv (䄆 Netminder) Commented:
What I'm saying is the original code you posted selected from
from INSURED_ENTITY

The second query:
Selet i.INSURED_NAME, c.* from COVERAGE c
 inner join INSURANCE_ENTITY

TWO DIFFERENT TABLES.

One uses  INSURED_ENTITY  the other INSURANCE_ENTITY.

I would expect the results to be different.
0
jknj72Author Commented:
Ohhhhhhh....Im sorry, I mistyped it, they are the same table....and its INSURED_ENTITY. When I free handed it I spelled it incorrectly...

Sorry bout that
0
slightwv (䄆 Netminder) Commented:
The query that returns rows has:
COV_EXPDT > l_COV_covExpDate

The query in the code has:
COV_EFFDT = l_COV_covEffDate

Again, different.

The NO_DATA_FOUND is straight forward:  The query executed returns no rows.

So, either the where clause causes this or you aren't looking at the tables/columns you think you are looking at.
0
jknj72Author Commented:
I understand what your saying. This process is in a Windows service program written in .Net and they get records from the program(to include INSURED_NAMES). There are over over 100k records being read from the source query(it goes back 6 years) and it loops through each one of those records and one of the checks is the Insured_Entity table and if theres no match it falls into no_data_found and goes to the next record. with what you can see, do you think it would be a better idea to write the source query to INNER JOIN the INSURED_ENTITY table and just bring back records that have an INSURED_NAME record? My thoughts are that if I can only get back the records with the Insured_Name I wouldn't have to loop through all the records that don't(which is the majority of the records)?
I guess my question is,....Does the query I wrote with the JOIN from Coverage to INSURED_ENTITY create the same logic than if I ran the Coverage query and then passed every rows Cov_ID, PolNbr and Cov_EffDate to the INSURED_ENTITY query? Any advice would really help.

Selet i.INSURED_NAME, c.POL_RECKEY, c.POLNBR, c.CVG_ID , c.COV_EFFDT ,c.COV_EXPDT , c.LCF_IND, c.CARRID
 FROM COVERAGE c
 INNER JOIN INSURED_ENTITY i on c.CVGID = i.CVG_ID
 AND c.PolNBR = i.PolNBR
 AND c.COV_EFFDAT = i.COV_EFFDAT  
WHERE c.COV_EXPDT > l_COV_covExpDate AND (c.STATUS != 'V') AND (c.LCF_IND = 'Y')

The INNER JOIN takes care of the INSURED_ENTITYs where clause and
The Where clause takes care of the COVERAGEs Where clause and
l_COV_covExpDate = ADD_MONTHS(Current_Date,-72) is from neither table but pulls 6 years worth of Coverage Exp_dt records so I think the end results "should" be the same or that's what Im thinking anyway?

For another question....
Also I know your against the In Memory Types Im using but to be honest it makes it a little easier for me to get to the finish line but I intend on using your example you sent to me from a prior question. As a matter of fact I have eliminated 2 or 3 Types using the example you gave me but for the Source data I wanted to make it work before I go back and try to optimize.
Id like to send you the final package after I get to where I need and maybe you can give me some advice  on maybe a better technique or approach to some of the things Im doing. Id like to really make this package as efficient as possible. It takes 8 hours to run currently and I just started this job so Id like get the run time down as much as possible...

FYI, I appreciate your help always!! So Thank you
0
slightwv (䄆 Netminder) Commented:
The code you posted looks like it will work even though I wouldn't do it that way.  The question asked is why everything hits the no_data_found exception.  You offered up a JOIN query to show there is data for the loop but there appear to be differences in columns between the pl/sql code and the JOIN statement.

If you can set up a simplified test case that reproduces the no_data_found that you are seeing we can post tested code.

>>Does the query I wrote with the JOIN

I find that most of the time a SELECT is faster than a code loop.

>>Id like to send you the final package

Unfortunately we cannot do site related stuff outside of the site.  You can post the entire package in a question and open it up to all Experts for advice.

>>It takes 8 hours to run currently

8 hours to process 100,000 records?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
I meant to send it to you as a question on the site...

And I found out today the Prod DB is running a couple of million rows and not the 100k I originally said. I found out the issue as well. This is in the Where Clause and it shouldn't have been so Its my bad!
 AND (c.LCF_IND = 'Y')

Thanks for your patience
0
jknj72Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.