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;

---

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;
/

---

:rolleyes:

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. :rolleyes:

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;

---

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.

Quote:

---

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

Quote:

---

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

Quote:

---

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.

Quote:

---

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.