Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

FUNCTION Return Table

GmoneyJun 15 2012 — edited Jun 15 2012
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.
This post has been answered by JustinCave on Jun 15 2012
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 13 2012
Added on Jun 15 2012
14 comments
11,513 views