-
Function compile errors in PL/SQL
Here is code that makes compile errors:
CREATE OR REPLACE FUNCTION isnumber(strn_len IS VARCHAR2)
REPLACE retvar AS
retvar INTEGER;
BEGIN
IF (select decode(translate(strn_len,'1234567890','N'),'N',1,0) from dual) THEN
retval := 1;
ELSE
retval := 0;
END IF;
RETRUN retval;
END;
/
The select works fine, as I testd with strings and numbeers only and got either ones or zeros.
Rich
-
Try this:
Code:
CREATE OR REPLACE FUNCTION isnumber(strn_len IS VARCHAR2)
RETURN IS
retvar INTEGER;
BEGIN
select decode(translate(strn_len,'1234567890','N'),'N',1,0)
into retvar
from dual;
RETURN retvar;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error - ' || SQLERRM);
END;
Cheers!
OraKid.
-
Try this.
Code:
CREATE OR REPLACE FUNCTION isnumber(strn_len IN VARCHAR2)
RETURN BINARY_INTEGER
IS
retvar BINARY_INTEGER;
BEGIN
select decode(translate(strn_len,'1234567890','N'),'N',1,0)
into retvar
from dual;
RETURN retvar;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error - ' || SQLCODE ||
' == ' || SQLERRM );
RAISE;
END;
/
-
What you actually need is a function that returns 1 (one) if number and 0 (zero) if not number, like this:
Code:
CREATE OR REPLACE FUNCTION isnumber(strn_len IN VARCHAR2)
RETURN BINARY_INTEGER
IS
testval NUMBER;
BEGIN
testval:=strn_len;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Well if you are going to nitpick then you need to do this.
Since if you get another error in this function you code will
hide the error.
Code:
CREATE OR REPLACE FUNCTION isnumber(strn_len IN VARCHAR2)
RETURN BINARY_INTEGER
IS
data_conversion_error EXCEPTION;
PRAGMA EXCEPTION_INIT (
data_conversion_error, -6502 );
testval NUMBER;
BEGIN
testval:=strn_len;
RETURN 1;
EXCEPTION
WHEN data_conversion_error THEN
RETURN 0;
WHEN OTHERS THEN
RAISE;
END;
/
-
Hm, very inteligent and - most imporrtantly - very efficient way: to perform a select when there is not even a slight need for it.
besides, have anyone looked at all what that piece of code actualy returns? Well, it returns 1 if the input string contains exactly one occurance of character '1', no matter what else is contained in that string, and it returns 0 for any other input string. Based on the name of the procedure (ISNUMBER) I would assume that the original poster actualy wants to get 1 if the input string contains only numerical characters and 0 if it doesn't.
So if you insist on using TRANSLATE (apparently the initial poster expects only positive *******s in the input string) then it should be something like this (without any select, of course):
Code:
CREATE OR REPLACE FUNCTION isnumber(strn_len IN VARCHAR2)
RETURN BINARY_INTEGER
IS
retval BINARY_INTEGER;
BEGIN
if translate(:strn_len,'x1234567890','x') is null then
retval := 1;
else
retval := 0;
end if;
RETURN retvar;
END;
But the "proper" way to do it does not require any TRANSLATE or any other similar function, just a simple TO_NUMBER conversion (implicit or explicit):
Code:
CREATE OR REPLACE FUNCTION isnumber(strn_len IN VARCHAR2)
RETURN BINARY_INTEGER
IS
retval BINARY_INTEGER;
l_dummy BINARY_INTEGER;
BEGIN
BEGIN
l_dummy := strn_len;
if l_dummy >= 0 then
retval := 1;
else
retval := 0;
end if;
EXCEPTION
WHEN OTHERS THEN retval := 0;
END;
RETURN retvar;
END;
Last edited by jmodic; 11-04-2004 at 04:04 PM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Doesn't this code need to handle all errors that might come up?
Code:
EXCEPTION
WHEN OTHERS THEN retval := 0;
END;
Hence I always use the specific exception that I am trapping along with a when others. However, I tend to be cautious when I code.
In my first example I was not attempting to change the logic in the code, but merely to demonstrate the proper syntax for creating a function.
-
Originally posted by gandolf989
Doesn't this code need to handle all errors that might come up?
Code:
EXCEPTION
WHEN OTHERS THEN retval := 0;
END;
Right! I was simply too lazy in typing and haven't had a database opened to find out what is the err number for conversion error.
Again, generaly speaking your remark is totaly valid. However, I'd be interested in what would be other possible errors that your re-raise statement in OTHERS will raise in that particular block.....
P.S.
When I started to write my previous answer, the LKBrwn_DBA's post wasn't there yet - if it was I wouldn't bother to interfere as he sugested more or less the same sulution. What actualy made me nervous was seing "SELECT FROM dual" in three subsequent posts when there is no need for any select at all....
Last edited by jmodic; 11-04-2004 at 05:03 PM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
Right! I was simply too lazy in typing and haven't had a database opened to find out what is the err number for conversion error.
no problem
Originally posted by jmodic
Again, generaly speaking your remark is totaly valid. However, I'd be interested in what would be other possible errors that your re-raise statement in OTHERS will raise in that particular block.....
I'm not sure what other error would come up. That is what I call defensive programming.
Originally posted by jmodic
P.S.
When I started to write my previous answer, the LKBrwn_DBA's post wasn't there yet - if it was I wouldn't bother to interfere as he sugested more or less the same sulution. What actualy made me nervous was seing "SELECT FROM dual" in three subsequent posts when there is no need for any select at all....
I agree the select and the dual table should not have been used.
Prayer, have you looked at the book Oracle PL/SQL Best Practices by Steven Feuerstein? It is a good guide on how to write PL/SQL.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|