Function compile errors in PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Function compile errors in PL/SQL

  1. #1
    Join Date
    Oct 2004
    Location
    Warminster,PA
    Posts
    10

    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

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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;
    /
    this space intentionally left blank

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool

    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

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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;
    /
    this space intentionally left blank

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.
    this space intentionally left blank

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width