Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   Oracle - PLS 00538 Type created with errors (http://www.go4expert.com/forums/oracle-pls-00538-type-created-errors-t18794/)

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

xpi0t0s 9Aug2009 00:45

Re: Oracle - PLS 00538 Type created with errors
 
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?


All times are GMT +5.5. The time now is 10:00.