Oracle - PLS 00538 Type created with errors

pasanindeewara's Avatar, Join Date: Aug 2009
Light Poster
See below my script file.

Code:
-- Lab 4 answers
-- Pasan Indeewara

CREATE TYPE exchanges_t AS VARRAY(5) OF VARCHAR2(12)
/
CREATE TYPE stock_t AS OBJECT(
   company      char(7),
   price    number(6,2),
   exchanges exchanges_t,
   dividend number(4,2),
   eps      number(4,2)
)
/

CREATE TYPE address_t AS OBJECT(
   street_number CHAR(10),
   street_name CHAR(20),
   suburb VARCHAR2(20),
   state VARCHAR2(15),
   pin CHAR(15)
)
/

CREATE TYPE investment_t AS OBJECT(
   company REF stock_t,
   pprice NUMBER(6,2),
   pdate DATE,
   qty NUMBER(6)
)
/

CREATE TYPE investment_ntt AS TABLE OF investment_t
/

CREATE TYPE client_t AS OBJECT(
   name    varchar2(12),
   address address_t,
   investments investment_ntt
)
/

CREATE TABLE stock_tab OF stock_t(
   company PRIMARY KEY
)
/

CREATE TABLE client_tab OF client_t (name primary key)
NESTED TABLE investments STORE AS investments_nttab
/

ALTER TABLE investments_nttab ADD SCOPE FOR (company) IS stock_tab
/

INSERT INTO stock_tab VALUES (stock_t('BHP',10.5,exchanges_t('Sydney','New York'),1.5,3.2))
/
INSERT INTO stock_tab VALUES (stock_t('IBM',70.00,exchanges_t('New York','London','Tokyo'),4.25,10.00))
/
INSERT INTO stock_tab VALUES (stock_t('INTEL',76.5,exchanges_t('Sydney','New York'),5,12.4))
/
INSERT INTO stock_tab VALUES (stock_t('FORD',40,exchanges_t('New York'),2,8.5))
/
INSERT INTO stock_tab VALUES (stock_t('GM',60,exchanges_t('New York'),2.5,9.2))
/
INSERT INTO stock_tab VALUES (stock_t('INFOSYS',45,exchanges_t('New York'),3,7.8))
/

INSERT INTO client_tab VALUES (client_t('John Smith',
                               address_t ('3','East Av','Bently','WA','6102'),
                               investment_ntt(
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='BHP'), 12.00,'02-OCT-2001',1000),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='BHP'), 10.50,'08-JUN-2002',2000),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='IBM'), 58.00,'12-FEB-2000',500),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='IBM'), 65.00,'10-APR-2001',1200),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INFOSYS'), 64.00,'11-AUG-2001',1000)
                               )
                              )
)
/
INSERT INTO client_tab VALUES (client_t('Jill Brody',
                               address_t ('42','Bent st','Perth','WA','6001'),
                               investment_ntt(
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 35.00,'30-JAN-2000',300),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 54.00,'30-JAN-2001',400),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 60.00,'02-OCT-2001',200),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='FORD'), 40.00,'05-OCT-1999',300),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='GM'), 55.50,'12-DEC-2000',500)
                               )
                              )
)
/

--1
SELECT DISTINCT c.name, i.company.company, i.company.price, i.company.dividend,i.company.eps
FROM client_tab c, TABLE(c.investments) i
ORDER BY c.name
/

--2
SELECT c.name, i.company.company, SUM(i.qty) tot,AVG(i.company.price) Avgpprice
FROM client_tab c, TABLE(c.investments) i
GROUP BY c.name, i.company.company
ORDER BY c.name
/

--3
SELECT c.name, SUM(i.qty), (SUM(i.qty)*i.company.price)
FROM client_tab c,
TABLE(SELECT investments FROM client_tab WHERE name=c.name) i,
TABLE(SELECT exchanges FROM stock_tab WHERE company=i.company.company) e
WHERE e.COLUMN_VALUE='New York'
GROUP BY c.name,i.company.company,i.company.price
/
--4
SELECT c.name, (SUM(i.qty*i.pprice)) tot
FROM client_tab c, TABLE(c.investments) i
GROUP BY c.name
/
--5
SELECT c.name, SUM(i.company.price*i.qty)-SUM(i.qty*i.pprice)
FROM client_tab c, TABLE(c.investments) i
GROUP BY c.name
/


--TRANSACTION part
SELECT DISTINCT c.name, i.company.company, i.company.price, i.company.dividend,i.company.eps
FROM client_tab c, TABLE(c.investments) i
ORDER BY c.name;

DELETE table(SELECT c.investments FROM client_tab c WHERE c.name='John Smith') i
WHERE i.company=(SELECT REF(s) FROM stock_tab s WHERE s.company='INFOSYS');

INSERT INTO table(SELECT c.investments FROM client_tab c WHERE c.name='John Smith') i
VALUES (investment_t((SELECT REF(s) FROM stock_tab s WHERE s.company='GM'),(SELECT s.price FROM stock_tab s WHERE s.company='INFOSYS'),(SELECT sysdate FROM 

dual),'1000'));

DELETE table(SELECT c.investments FROM client_tab c WHERE c.name='Jill Brody') i
WHERE i.company=(SELECT REF(s) FROM stock_tab s WHERE company='GM');

INSERT INTO table(SELECT c.investments FROM client_tab c WHERE c.name='Jill Brody') i
VALUES (investment_t((SELECT REF(s) FROM stock_tab s WHERE s.company='INFOSYS'),(SELECT s.price FROM stock_tab s WHERE s.company='GM'),(SELECT sysdate FROM 

dual),500));

SELECT DISTINCT c.name, i.company.company, i.company.price, i.company.dividend,i.company.eps
FROM client_tab c, TABLE(c.investments) i
ORDER BY c.name;
--------------------------------------------------------------------------------------------------------------------------
---  Lab 04 ---
--------------------------------------------------------------------------------------------------------------------------
--1a
ALTER TYPE stock_t ADD MEMBER FUNCTION calcStkYield RETURN NUMBER CASCADE
/
CREATE OR REPLACE TYPE BODY stock_t AS
MEMBER FUNCTION calcStkYield
RETURN NUMBER IS
BEGIN
   RETURN (self.dividend/self.price);
END;
END;
/
--1b
ALTER TYPE stock_t ADD MEMBER FUNCTION Convert2US(USRate NUMBER) RETURN NUMBER CASCADE
/
CREATE OR REPLACE TYPE BODY stock_t AS MEMBER FUNCTION Convert2US(USRate NUMBER)
RETURN NUMBER IS
BEGIN
   RETURN (self.price*USRate);
END;
END;
/
show error;

--1c
ALTER TYPE stock_t ADD MEMBER FUNCTION CountExchanges(stock VARCHAR2) RETURN INTEGER CASCADE
/
CREATE OR REPLACE TYPE BODY stock_t AS MEMBER FUNCTION CountExchanges(stock VARCHAR2)
RETURN INTEGER IS
cnt NUMBER;
BEGIN
   SELECT COUNT(e.COLUMN_VALUE) INTO cnt
   FROM stock_tab s, TABLE(s.exchanges) e
   WHERE s.company=stock;

   RETURN cnt;
END;
END;
/
show error;

DROP TABLE client_tab
/
DROP TABLE stock_tab
/
DROP TYPE client_t
/
DROP TYPE investment_ntt
/
DROP TYPE investment_t
/
DROP TYPE address_t
/
DROP TYPE stock_t
/
DROP TYPE exchanges_t
/
I ran the above code using
@c:\users\Pasan Indeewara\desktop\lab4.txt;
via Oracle SQL+ in version Oracle XE 10g.

It prompts me an error in function definition, but I couldn't figure it out yet. Please help me to find ou the issue here.

Warning: Type Body created with compilation errors.

Errors for TYPE BODY STOCK_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/41 PLS-00538: subprogram or cursor 'CALCSTKYIELD' is declared in an
object type specification and must be defined in the object type
body


Type altered.


Warning: Type Body created with compilation errors.

Errors for TYPE BODY STOCK_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/41 PLS-00538: subprogram or cursor 'CALCSTKYIELD' is declared in an
object type specification and must be defined in the object type
body

9/41 PLS-00538: subprogram or cursor 'CONVERT2US' is declared in an
object type specification and must be defined in the object type
body
0
xpi0t0s's Avatar, Join Date: Aug 2004
Mentor
Well, have a look at the error message. What does it tell you?
Does "must be defined in the object type body" give you a clue as to how to fix the error?
For example, have you checked the object type body for these two functions, and are they present?