CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
Ahoura Ghotbi
Ahoura Ghotbi

Reputation: 2896

Oracle ERROR: ORA-00900: invalid SQL statement

I am trying to create the following datatype with 2 functions:

-- Employee

CREATE OR REPLACE TYPE EmployeeType AS OBJECT (
    EmployeeNumber NUMBER,
    EmployeeName VARCHAR2(150),
    EmployeeAddress VARCHAR2(255),
    MAP MEMBER FUNCTION getEmployeeNumber RETURN NUMBER,
    MEMBER FUNCTION CalculateSalary RETURN FLOAT(2)
)
NOT FINAL;
CREATE OR REPLACE TYPE BODY EmployeeType AS

    MAP MEMBER FUNCTION getEmployeeNumber RETURN NUMBER IS
    BEGIN
        RETURN EmployeeNumber;
    END;
     -- function that can be overriden by subtypes, make abstract
    MEMBER FUNCTION CalculateSalary RETURN FLOAT(2) IS
    BEGIN
         -- function returns empty, has to be overwritten by fulltimeemployee
        RETURN 0.00;
    END;
END;

However I keep getting an error stating 

ERROR: ORA-00900: invalid SQL statement

Error Code: 900

Query = END

I am using RazorSQL to execute my queries, I cant seem to get the line number causing this error but through trial and error I have found it to be one of the function descriptions in my TYPE BODY definition.

I have tried adding / after the last END; but it does not help solve the issue.

Upvotes: 1

Views: 2463

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Replace FLOAT(2) with just FLOAT:

CREATE OR REPLACE TYPE EmployeeType AS OBJECT (
    EmployeeNumber NUMBER,
    EmployeeName VARCHAR2(150),
    EmployeeAddress VARCHAR2(255),
    MAP MEMBER FUNCTION getEmployeeNumber RETURN NUMBER,
    MEMBER FUNCTION CalculateSalary RETURN FLOAT
)
NOT FINAL;
/
CREATE OR REPLACE TYPE BODY EmployeeType AS

    MAP MEMBER FUNCTION getEmployeeNumber RETURN NUMBER IS
    BEGIN
        RETURN EmployeeNumber;
    END;
     -- function that can be overriden by subtypes, make abstract
    MEMBER FUNCTION CalculateSalary RETURN FLOAT IS
    BEGIN
         -- function returns empty, has to be overwritten by fulltimeemployee
        RETURN 0.00;
    END;
END; 
/

The documentation for CREATE TYPE doesn't mention this, but you can find the explanation in the topic related to CREATE FUNCTION : http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_function.htm RETURN datatype

RETURN datatype
For datatype, specify the data type of the return value of the function. The return value can have any data type supported by PL/SQL.
......
......
The data type cannot specify a length, precision, or scale. The database derives the length, precision, or scale of the return value from the environment from which the function is called.

Upvotes: 2

Related Questions