asked on

Function in SQL Developer

trying to create a function that if there is a value then I want to return 'X' but if no value I have to create an empty string with a space kinda like RPAD(' ',1,' ') or something like that

create or replace FUNCTION CHECK_SOLE_PROPRIETOR(polNbr IN VARCHAR2, cov_effdt IN DATE)
RETURN NUMBER
IS
v_EndorseID NUMBER;
BEGIN
--ENDORSE_ID is a NUMBER when there is No value it looks like the pic below
SELECT ENDORSE_ID into v_EndorseID from ENDORSE where POLNBR = polNbr AND COV_EFFDT= cov_effdt AND ENDORSE_CD_NBR = '290307';
RETURN v_EndorseID;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN VALUE_ERROR THEN
RETURN 0;
WHEN OTHERS THEN
RETURN 0;
END CHECK_SOLE_PROPRIETOR;

When there is a value from the Select statement being run by itself with variables set that I know have a EndorseID it returns but if there is no Value it doesn't have any value displayed so I don't know what Im doing?

I would accept returning varchars 2, I don't care, but I need to evaluate the empty value so I can set its value to ' ' or 'X' if there is a return value.
Now, when I know there is a value that should be returned it is returning 0 e verytime regardless now...

Mark Geerlings

This looks like a contradiction to me: "create an empty string with a space kinda like RPAD(' ',1,' ')". I would say that it is easy to have a PL\SQL function return: "an empty string" *OR* "a string with a space kinda like RPAD(' ',1,' ')", but those are two different things. One is an empty string, the other is a non-empty string that contains a space (but no visible characters).

The easy way in SQL or PL\SQL to return something when a query finds no value in a particular column is with Oracle's "NVL" operator. That allows you to substitute any value you like when Oracle finds a null (or empty value). Here is an example (to return a zero, instead of a null)
RETURN nvl(v_EndorseID,0);

Your function currently returns a "number", so that limits what you can return from this function, at least as it is now. If you have the flexibility to modify the function to return a "varchar2' value instead, then you could have it return a string with a single space (or a question mark, or something else if you like) when the query finds no match.

I would also suggest that the exception-handling section of this function currently contains some wasted lines:

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN VALUE_ERROR THEN
RETURN 0;
WHEN OTHERS THEN
RETURN 0;

That can (and I would say should) be simply this:
EXCEPTION
WHEN OTHERS THEN
RETURN 0;

That is, unless you change it to something more like this, so you can tell what the problem actually was:

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -2;
WHEN VALUE_ERROR THEN
RETURN -1;
WHEN OTHERS THEN
RETURN 0;

jknj72

ASKER

I actually did try nvl(v_EndorseID,0); and it returns me a 0 for everytime I call the function.

create or replace FUNCTION CHECK_SOLE_PROPRIETOR(polNbr IN VARCHAR2, cov_effdt IN DATE)
RETURN NUMBER
IS
v_EndorseID NUMBER;
BEGIN

SELECT ENDORSE_ID into v_EndorseID from ENDORSE where POLNBR = polNbr AND COV_EFFDT= cov_effdt AND ENDORSE_CD_NBR = '290307';
RETURN nvl(v_EndorseID,0);

EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END CHECK_SOLE_PROPRIETOR;

Mark Geerlings

What does this query return:

SELECT ENDORSE_ID
from ENDORSE
where POLNBR = &polNbr
AND COV_EFFDT= '&cov_effdt'
AND ENDORSE_CD_NBR = '290307';

Mark Geerlings

Please test that query outside of PL\SQL (like in SQL Developer, or in SQL*Plus). Substitute actual values for the "&polNbr" and "&cov_effdt" parameters. This will help you determine if your problem is actually a data problem or a PL\SQL problem. I suspect you have a data problem of some kind, either you aren't passing valid values for: polNbr and cov_effdt, that match actual records in your table. Or, maybe the actual values in your COV_EFFDT column include a non-midnight time-of-day component, and you are passing date values in that do *NOT* include the time-of-day.

Try this command:
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Then run this query and post the results here:
SELECT ENDORSE_ID, POLNBR, COV_EFFDT
from ENDORSE
where ENDORSE_CD_NBR = '290307';

And, after running this query (and copying the results here please) close the window that you ran this in, and log in again to set your nls_date_format back to the usual value in your system. Or, you could do another command more like one of these (if your usual date format is like one of these):

alter session set nls_date_format='MM/DD/YYYY';

alter session set nls_date_format='DD-MON-YY';

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jknj72

ASKER

I cant believe what just happened. I read the last part of your answer where you said

>>Try to not use parameters that are the same name as columns in the tables you are working with. At times I've seen Oracle get confused when trying to figure out what is a variable and what is a column.
>>

Not believing this could be the case, I tried it and changed my functions parameters with p_ and it returns different and I think correct values.

Thanks Slight

PS - I thought I was doing the Dates correctly, Comparing Dates to Dates? I wasn't passing in a VARCHAR2, I passed in a DATE and didn't think I should have to do any kind of conversion?

jknj72

ASKER

awesome

slightwv (䄆 Netminder)

>> thought I was doing the Dates correctly, Comparing Dates to Dates?

The select you posted in the image was a string.

The procedure looks like it does things right. I typically don't use DATE for input parameters because developers have trouble with dates from time to time.