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!

Function is not giving correct output

Discussion in 'SQL PL/SQL' started by Amarendra Sahoo, May 25, 2016.

  1. Amarendra Sahoo

    Amarendra Sahoo Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    create or replace
    FUNCTION GRADECOM ( GRADE IN VARCHAR2 )
    RETURN VARCHAR2 IS
    COMMENTS VARCHAR2(30);
    BEGIN
    CASE GRADE
    when 'A' then dbms_output.put_line('Excellent');
    when 'B' then dbms_output.put_line('Very good');
    when 'C' then dbms_output.put_line('Well done');
    when 'D' then dbms_output.put_line('You passed');
    when 'F' then dbms_output.put_line('Better try again');
    else dbms_output.put_line('No such grade');
    END CASE;
    RETURN NULL;
    END GRADECOM;



    select gradecom('A') from dual;

    Output:
    null

    expected:it should give excellent.

    Where it is going wrong? Please help.
     
  2. naveen.lazaus

    naveen.lazaus Active Member

    Messages:
    2
    Likes Received:
    2
    Trophy Points:
    85
    Here you go...

    create or replace
    FUNCTION GRADECOM ( GRADE IN VARCHAR2 )
    RETURN VARCHAR2 IS
    COMMENTS VARCHAR2(30);
    BEGIN
    CASE GRADE
    when 'A' then
    COMMENTS := 'Excellent';
    when 'B' then
    COMMENTS :='Very good';
    when 'C' then
    COMMENTS :='Well done';
    when 'D' then
    COMMENTS :='You passed';
    when 'F' then
    COMMENTS :='Better try again';
    else
    COMMENTS :='No such grade';
    END CASE;
    RETURN COMMENTS;
    END GRADECOM;

    select GRADECOM('B') from dual;
     
    jagadekara and Amarendra Sahoo like this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You did not do this:

    set serveroutput on size 1000000

    DBMS_OUTPUT relies on serveroutput being set to ON before any text will be displayed.
     
  4. Amarendra Sahoo

    Amarendra Sahoo Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    thanks a lot.
     
  5. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    In the above program u have defined a variable comments and u haven't used it..Instead of dbms_output--- write comments and declare it using your own message..