| pasanindeewara |
3Aug2009 03:14 |
Oracle - PLS 00538 Type created with errors
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 |
