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
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?