Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: Custom functions

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE 11.1.0.7.0 Production
    TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production

    I am having trouble creating a custom function defined with a character parameter.
    I can create this function fine using a number for the parameter.
    Here is a cut down version. As you can see it's not even referencing the parameters!, but I can't call it with a character string value.

    e.g.
    THIS WORKS:

    CREATE or replace FUNCTION foo(p IN NUMBER)
    RETURN NUMBER
    IS ret NUMBER(5);
    BEGIN
    ret :=999;
    RETURN(ret);
    END;

    > FUNCTION foo(p Compiled.

    select foo(1) from dual;

    FOO(1)
    ----------------------
    999

    FAILS:

    CREATE or replace FUNCTION foo(p in VARCHAR2(10)))
    RETURN NUMBER
    IS ret NUMBER(5);
    BEGIN
    ret:=999;
    RETURN(ret);
    END;

    > Warning: execution completed with warning -- ???
    > FUNCTION foo(p Compiled.

    select foo('x') from dual;

    Error at Command Line:1 Column:7
    Error report:
    SQL Error: ORA-06575: Package or function FOO is in an invalid state
    06575. 00000 - "Package or function %s is in an invalid state"
    *Cause: A SQL statement references a PL/SQL function that is in an
    invalid state. Oracle attempted to compile the function, but
    detected errors.
    *Action: Check the SQL statement and the PL/SQL function for syntax
    errors or incorrectly assigned, or missing, privileges for a
    referenced object.


    Any ideas?

    Thanks for any help on this.

    Andy

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    it doesn't matter if it uses the parameter, it needs a number and 'x' can't be converted to one so it fails. exactly as expected.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    ?
    I don't think you read the post correctly.

    >>>>>
    FAILS:

    CREATE or replace FUNCTION foo(p in VARCHAR2(10)))
    RETURN NUMBER
    IS ret NUMBER(5);
    BEGIN
    ret:=999;
    RETURN(ret);
    END;

    > Warning: execution completed with warning -- ???
    > FUNCTION foo(p Compiled.

    select foo('x') from dual;
    <<<<<

    This isn't expecting a NUMBER, its expecting a STRING.

    I've found the problem anyway.

    - I should not have specified "IN" or a size, i.e this now works:

    CREATE or replace FUNCTION foo(p VARCHAR2)
    RETURN NUMBER IS
    ret NUMBER(5);
    BEGIN
    ret:=999;
    RETURN(ret);
    END;


    Cheers.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    At this point:
    > Warning: execution completed with warning -- ???
    in SQL Plus you would type "SHOW ERRORS" and see this:
    Code:
    SQL> CREATE or replace FUNCTION foo(p in VARCHAR2(10)))
      2  RETURN NUMBER
      3  IS ret NUMBER(5);
      4  BEGIN
      5  ret:=999;
      6  RETURN(ret);
      7  END;
      8  /
    
    Warning: Function created with compilation errors.
    
    SQL> show errors
    Errors for FUNCTION FOO:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    1/27     PLS-00103: Encountered the symbol "(" when expecting one of the
             following:
             := . ) , @ % default character
             The symbol ":=" was substituted for "(" to continue.
    
    1/32     PLS-00103: Encountered the symbol ")" when expecting one of the
             following:
             return
    The problem is that VARCHAR2 parameters cannot have a size specification. It should be:
    Code:
    CREATE or replace FUNCTION foo(p in VARCHAR2)...

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    That, plus you have an extra closing paren

    Code:
    CREATE or replace FUNCTION foo(p in VARCHAR2(10))) 
    ...

Posting Permissions

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