Function returns same value for every iteration

I am looping through a dataset and calling a function everytime I loop. The value I am getting back from this function is the same for every record...Heres what Im doing..

SYS.DBMS_OUTPUT.PUT_LINE('BEFORE GetInsuredName :  ' || 'CovID' || l_POLICY_TAB(i).CVG_ID || ' PolNumber: ' ||  l_POLICY_TAB(i).POLNBR || ' CovEffDate: ' ||   l_POLICY_TAB(i).COV_EFFDT || ' for record ' || i);
l_COV_INSURED_NAME := GET_INSURED_NAME(l_POLICY_TAB(i).CVG_ID, l_POLICY_TAB(i).POLNBR, l_POLICY_TAB(i).COV_EFFDT);--17-JUN-08
SYS.DBMS_OUTPUT.PUT_LINE('After function INSURED NAME is ' || l_COV_INSURED_NAME || ' and CovEffDt is ' || l_POLICY_TAB(i).COV_EFFDT || ' for record ' || i);
   
--Heres what is my output looks like
BEFORE GetInsuredName :  CovID0365809 PolNumber: WCP080234215 CovEffDate: 27-MAY-08 for record 1
After function INSURED NAME is 'COMNPANY Z' and CovEffDt is 27-MAY-08 for record 1
BEFORE GetInsuredName :  CovID0392958 PolNumber: WCP090305202 CovEffDate: 27-MAY-08 for record 2
After function INSURED NAME is 'COMNPANY Z' and CovEffDt is 27-MAY-08 for record 2
BEFORE GetInsuredName :  CovID0306060 PolNumber: WCP090160704 CovEffDate: 27-MAY-08 for record 3
After function INSURED NAME is 'COMNPANY Z' and CovEffDt is 27-MAY-08 for record 3


And if I run the Select statement with the values above they come out with different InsuredName values?


--This is my function
create or replace FUNCTION GET_INSURED_NAME(cvg_ID IN VARCHAR2, polNbr IN VARCHAR2, cov_effdt IN DATE)
   RETURN VARCHAR2
IS
   v_new_key VARCHAR2(50);
BEGIN
   Select INSURED_ENTITY.INSURED_NAME into v_new_key from INSURED_ENTITY where CVG_ID= cvg_ID AND POLNBR= polNbr AND COV_EFFDT = cov_effdt AND ROWNUM = 1;  
   RETURN  v_new_key;
   
EXCEPTION
WHEN NO_DATA_FOUND THEN
   RETURN '0';
WHEN VALUE_ERROR THEN
   RETURN '0';
WHEN OTHERS THEN
   RETURN '0';
END GET_INSURED_NAME;
jknj72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

johnsoneSenior Oracle DBACommented:
It is your variable names.  They are case insensitive.  You have variables with the same names as the columns in the table.  Change your function to something like this:
create or replace FUNCTION GET_INSURED_NAME(v_cvg_ID IN VARCHAR2, v_polNbr IN VARCHAR2, v_cov_effdt IN DATE)
   RETURN VARCHAR2
IS
   v_new_key VARCHAR2(50);
BEGIN
   Select INSURED_ENTITY.INSURED_NAME into v_new_key from INSURED_ENTITY where CVG_ID= v_cvg_ID AND POLNBR= v_polNbr AND COV_EFFDT = v_cov_effdt AND ROWNUM = 1;   
   RETURN  v_new_key;
   
EXCEPTION
WHEN NO_DATA_FOUND THEN
   RETURN '0';
WHEN VALUE_ERROR THEN
   RETURN '0';
WHEN OTHERS THEN
   RETURN '0';
END GET_INSURED_NAME;

Open in new window

I would think that the function as written would error every time and return 0.  It should be getting a too many values error.

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
slightwv (䄆 Netminder) Commented:
I commented on the parameter names and column names in your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28678570.html#a40792229
Mark GeerlingsDatabase AdministratorCommented:
Slightwv's comment in your other question says:

"Try to not use parameters that are the same name as columns in the tables you are working with [in your procedure/function]."

I would write that much stronger:

Make sure that you *NEVER* use input parameter names with the same names as column names in tables that you reference inside your function or procedure!  That will not cause an error at compile time.  But it *WILL* cause very unexpected behavior at run-time.
jknj72Author Commented:
Thanks for the help. I should have realized that I asked this question already and had the answer.
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.