Oracle - PLS 00538 Type created with errors

Discussion in 'Oracle' started by pasanindeewara, Aug 2, 2009.

  1. pasanindeewara

    pasanindeewara New Member

    Joined:
    Aug 2, 2009
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    SE
    Location:
    Ice Age
    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
     
  2. xpi0t0s

    xpi0t0s Mentor

    Joined:
    Aug 6, 2004
    Messages:
    3,009
    Likes Received:
    203
    Trophy Points:
    63
    Occupation:
    Senior Support Engineer
    Location:
    England
    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?
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice