Function Creation in Oracle database

Question

Question is : I have to Create Function with below Details 
Function Name : Credit_Limit 
Input         : Entity_Id (Shipment_entity) 
Returns       : VARCHAR2 (Limit_Status)

Design rules:

If the credit_limit of the given entity id is greater then 50000,then display the limit_status as 'Credit limit is greater than 50000'
If the credit_limit of the given entity id is less then 50000,then display the limit_status as 'Credit limit is less than 50000'

Note: DO NOT CHANGE the given status message in your solution.

    CREATE OR REPLACE FUNCTION credit_limit (entity_id IN INTEGER)
         RETURN VARCHAR2
       IS
         c_credit_limit   NUMBER (5, 2);
         limit_status     VARCHAR (255);
        BEGIN
          SELECT credit_limit
             INTO c_credit_limit
           FROM shipment_entity
          WHERE id = entity_id;

    IF c_credit_limit > 50000
    THEN
        limit_status := 'Credit limit is greater than 50000';
    ELSE
        IF c_credit_limit < 50000
        THEN
            limit_status := 'Credit limit is less than 50000';
        END IF;
    END IF;

    RETURN (c_credit_limit);
    END;
    /

I executed the above mentioned code the function is created but it shows wrong answer after submit the code. help on this.

Better use c_credit_limit shipment_entity.credit_limit%TYPE; instead of c_credit_limit NUMBER (5, 2); — Wernfried Domscheit
– Wernfried Domscheit, Commented May 9, 2018 at 8:06

MT0 · Accepted Answer · 2018-05-09 07:37:27Z

2

You want:

RETURN limit_status;

not

RETURN (c_credit_limit);

answered May 9, 2018 at 7:37

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FTSY1993 · Accepted Answer · 2018-05-09 12:32:11Z

0

Below Function is working fine

CREATE OR REPLACE FUNCTION credit_limit (entity_id IN INTEGER) RETURN VARCHAR2 IS c_credit_limit shipment_entity.credit_limit%TYPE; limit_status VARCHAR (255); BEGIN SELECT credit_limit INTO c_credit_limit FROM shipment_entity WHERE id = entity_id;

IF c_credit_limit > 50000
THEN
    limit_status := 'Credit limit is greater than 50000';
ELSE
    IF c_credit_limit < 50000
    THEN
        limit_status := 'Credit limit is less than 50000';
    END IF;
END IF;

RETURN limit_status;
END;
/

answered May 9, 2018 at 12:32

FTSY1993

213 bronze badges

Collectives™ on Stack Overflow

Function Creation in Oracle database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related