CODEWITHSUNDEEP
oracle-databasefunctionplsql
Randy Gilman
Randy Gilman

Reputation: 457

ORA-06575: is in an invalid state

Hello I am attempting to do the following problem and keep receiving errors. Create a user-defined function called DOLLARS_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total gross sales amount of the sales by that combination. My code is as follows:

CREATE OR REPLACE FUNCTION DOLLARS_BY_VEHICLE_TYPE (V_AUTODESC VARCHAR2)
RETURN VARCHAR2
IS
V_AMT   VARCHAR2 (50);
BEGIN
SELECT NVL (SUM (GROSS_SALES_AMOUNT), 0)
INTO V_AMT
FROM VEHICLES v, SALES_FACTS s
WHERE V.Vehicle_Code = S.Vehicle_Code AND V.DESCRIPTION= V_AUTODESC;

RETURN V_AMT;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
/

However, when I run it I get:

Warning: Function created with compilation errors. ERROR at line 1: ORA-06575: Package or function DOLLARS_BY_VEHICLE_TYPE is in an invalid state

Upvotes: 1

Views: 1601

Answers (1)

XING
XING

Reputation: 9886

Please see that you are trying to return the sum from your function and that can be only number/int/float. So the return type of your function should be number not varchar2. Also note that you are using a deprecated sql syntax which can be changed. See below:

CREATE OR REPLACE FUNCTION DOLLARS_BY_VEHICLE_TYPE (V_AUTODESC VARCHAR2)
  -- RETURN VARCHAR2
  RETURN NUMBER
IS
   --V_AMT   VARCHAR2 (50);
     V_AMT  NUMBER;
BEGIN
   SELECT NVL (SUM (GROSS_SALES_AMOUNT), 0)
     INTO V_AMT
     FROM VEHICLES v 
     inner join SALES_FACTS s
     ON V.Vehicle_Code = S.Vehicle_Code 
     where V.DESCRIPTION = V_AUTODESC;

   RETURN V_AMT;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 0;
END;
/

Upvotes: 2

Related Questions