1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

custom function problem

Discussion in 'SQL PL/SQL' started by Marco, Sep 24, 2012.

  1. Marco

    Marco Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi everyone,

    please tell me what is wrong with this code:

    DECLARE
    empty_val varchar2(100);

    function test_fun (val_1 NUMBER, val_2 NUMBER) return number IS
    total number(5);
    BEGIN
    total := val_1 + val_2;
    return total;
    END test_fun;

    BEGIN
    select test_fun(3, 5) from dual;
    END;
    /

    I get this error:


    Error starting at line 1 in command:
    DECLARE
    empty_val varchar2(100);

    function test_fun (val_1 NUMBER, val_2 NUMBER) return number IS
    total number(5);
    BEGIN
    total := val_1 + val_2;
    return total;
    END test_fun;

    BEGIN
    select test_fun(3, 5) from dual;
    END;
    Error report:
    ORA-06550: line 12, column 10:
    PLS-00231: function 'TEST_FUN' may not be used in SQL
    ORA-06550: line 12, column 10:
    PL/SQL: ORA-00904: : invalid identifier
    ORA-06550: line 12, column 3:
    PL/SQL: SQL Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:


    I checked many tutorials but I can not find what is wrong.
    Thanks, Marco.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Code (SQL):
    SQL> RUN 1
      1  DECLARE
      2  ABC NUMBER;
      3
      4  FUNCTION test_fun (val_1 NUMBER, val_2 NUMBER) RETURN NUMBER IS
      5  total NUMBER(5);
      6  BEGIN
      7  total := val_1 + val_2;
      8  RETURN total;
      9  END test_fun;
     10
     11  BEGIN
     12  ABC :=  test_fun(3, 5);
     13  DBMS_OUTPUT.PUT_LINE('OUTPUT = '||ABC);
     14* END;
    OUTPUT = 8

    PL/SQL PROCEDURE successfully completed.

    SQL>
     
    Marco likes this.
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    In addtion here is the description of the error according to oracle

    http://docs.oracle.com/cd/E11882_01/server.112/e17766/pcmus.htm#sthref18172

    Regards,
    Dariyoosh
     
    Marco likes this.
  4. jamuna_j

    jamuna_j Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    Your SQL query is trying to call a function placed in an anonymous block, which is not allowed by SQL.

    Two issues here as far as Oracle rules are concerned -
    1. Only stored function may be called through SQL expressions (what you make with the CREATE or REPLACE clause).
    2. To execute the statement with the stored function, there are two ways -
    (a) To display the result in SQL*Plus, you have to run the statement alone - no need of the BEGIN..END block.
    (b) To run a SELECT query inside a block as in your example, must have the INTO clause and a variable to hold the output.

    If you have no purpose for the block other than to display the function result, running it as an independent SQL query is better. Unless you plan to do something with that output - which is when PL/SQL is helpful

    The solutions for both (a) and (b) below -

    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION test_fun (val_1 NUMBER, val_2 NUMBER) RETURN num
    ber IS
      2  total NUMBER(5);
      3  BEGIN
      4  total := val_1 + val_2;
      5  RETURN total;
      6  END test_fun;
      7  /

    FUNCTION created.

    SQL> SELECT test_fun(3,5) FROM dual;

    TEST_FUN(3,5)
    -------------
                8

    SQL> DECLARE
      2  vTotal NUMBER := 0;
      3  BEGIN
      4  SELECT TEST_FUN(3,5) INTO vTotal FROM DUAL;
      5  DBMS_OUTPUT.PUT_LINE('Total is: '||vTotal);
      6  END;
      7  /
    Total IS: 8

    PL/SQL PROCEDURE successfully completed.

    SQL>