1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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,012
    Likes Received:
    203
    Trophy Points:
    0
    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