Hello All,
I am working on a project for school, so I appreciate any assitance as I am learning. :).
I have created a Function that returns a table.
I am receiving this error:
ORA-06575: Package or function GET_MAINT_DUE is in an invalid state.
I am not getting any compilation errors, nor is anything listed when I run
SELECT *
FROM user_errors
WHERE name = 'get_maint_due';
So I am not sure what the problem is.
Do I need to create an object type for FUNCTION get_maint_due? If so how should I do that and can it be done within the Function script (or within the package body once I add the Function to a package.
The purpose of the Function is to collect some existing columns from the boat table and then
an aggregatre MONTHS_BETWEEN to show how long it has been since the last maintenance was performed on the craft.
The table the Function goes against is called BOAT.
CREATE TABLE Boat
(Boat_ID NUMBER(10)
Boat_Name VARCHAR2(30) NOT NULL,
Model VARCHAR2(30) NOT NULL,
Manufacturer VARCHAR2(30) NOT NULL,
Seating_Capacity NUMBER (10) NOT NULL,
STATUS VARCHAR2(30) NOT NULL,
pur_date DATE NOT NULL,
last_maint_date DATE,
CONSTRAINT "Boat_PK"
PRIMARY KEY ( Boat_ID)
)
/
--- Boat Data
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL, 'Speedy', 'Speedster', 'Seadoo', '4', 'Available', '15-Jan-2010', '15-Jan-2012');
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL, 'Gone', 'Speedster', 'Seadoo', '4', 'Available', '15-Jan-2010', '15-Jan-2012');
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL,'Mama Kin', 'Deck Boat', 'Ranger', '6', 'Available', '15-May-2010', '15-May-2012');
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL,'Beer R-U-N','Cuddy', 'Aerocraft', '6', 'Available', '15-October-2011', '15-May-2012');
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL, 'Rampage', 'Pontoon', 'Sailstar', '10', 'Available', '15-March-2009', '15-March-2012');
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL, 'Justyn Time', 'Pontoon', 'Sailstar', '10', 'Available', '15-March-2009', '15-March-2012');
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL, 'Triumph', 'Pontoon', 'Sailstar', '12','Available', '15-Jan-2012', '15-Jan-2012');
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL, 'Big House', 'House Boat', 'Gulfstream', '16','Available', '15-Jan-2010', '15-Jan-2012');
INSERT INTO boat
VALUES (boat_id_seq.NEXTVAL, 'Cabo Wabo', 'Barge', 'Sea Raider', '25', 'Available', '15-March-2009', '15-March-2012');
COMMIT;
/
Here is the Function
CREATE OR REPLACE FUNCTION get_maint_due
RETURN TABLE
AS
BEGIN
SELECT boat_ID,boat_name, model, manufacturer, seating_capacity, status, pur_date, last_maint_date,
MONTHS_BETWEEN (to_date(SYSDATE, 'yyyy/mm/dd'), to_date(LAST_MAINT_DATE, 'yyyy/mm/dd')) Mnths_Since_Ser
FROM BOAT
WHERE MONTHS_BETWEEN (to_date(SYSDATE, 'yyyy/mm/dd'), to_date(LAST_MAINT_DATE, 'yyyy/mm/dd')) > 3
END get_maint_due;
Thanks for taking the time to look.