Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: doubt in the usage of TYPE

    Hi ,

    I created the below simple OBJECT TYPE and compiled it successfully in Oracle,

    Code:
    CREATE TYPE pnr_typ AS OBJECT
       (
            principle NUMBER,
            interest NUMBER,
            year NUMBER,
            MEMBER FUNCTION si RETURN FLOAT,
            MEMBER FUNCTION ci RETURN FLOAT,
            MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER
       );
    /
    show err
    
    CREATE TYPE BODY pnr_typ IS
    
          MEMBER FUNCTION si RETURN FLOAT IS
           BEGIN
               RETURN (principle*interest*SELF.year)/100;
           END;      MEMBER FUNCTION ci RETURN FLOAT IS
           BEGIN
               RETURN POWER(SELF.principle*(1 + SELF.interest/100), year);
           END;
    
    
          MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
           BEGIN
               RETURN (year + invent);
           END;
          END;
    /
    Show err
    Now, I created a table calcinterest with a column col of the type pnr_type and inserted some values with the help of following DDL .

    Code:
    insert into calcinterest values (pnr_typ(1000, 2, 3));
    And the values returned by the select statment to the table are ,

    Code:
    SQL> select * From calcinterest;
    
    COL(PRINCIPLE, INTEREST, YEAR)
    --------------------------------------------------------------------------------
    PNR_TYP(1000, 2, 3)
    However i did not know to access the methods si and ci from the pnr_typ object in the column col.


    1. Could any body help me to do the same ??

    2. I would also like to know how to use the TYPE object inside a procedure, if it could be done could you pls gimme a simple example (it should use the above object and its method) with the above 'pnr_typ' obj.

    Thanks,
    Sn

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    1.
    Code:
    SQL> select pnrt, c.pnrt.si() as si from calcinterest c;
    
    PNRT(PRINCIPLE, INTEREST, YEAR)
    -------------------------------------------------------------------------------
            SI
    ----------
    PNR_TYP(1000, 2, 3)
            60
    Important: you need the alias ("c.") and the parentheses ("()") to make this work. (Don't ask me why - that's just how it works!)

    2.
    Code:
    SQL> declare
      2    pnrt pnr_typ := pnr_typ(1000, 2, 3);
      3  begin
      4    dbms_output.put_line('si='||pnrt.si);
      5  end;
      6  /
    si=60
    
    PL/SQL procedure successfully completed.
    Note: this time you don't need the parentheses after the method name!

    Opinion: Most experienced Oracle designers never use object types in database objects, because they are a PITA that solves no real problems but introduces plenty!

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by andrewst
    Opinion: Most experienced Oracle designers never use object types in database objects, because they are a PITA that solves no real problems but introduces plenty!
    I agree with you Tony, except for some cases such as when using Advanced Queuing with which you HAVE to use object types if you want Queue messages to be Oracle-Structured (I mean messages using Oracle data types). I think that you were mainly talking about "no object within tables" on which I totally agree, but I wanted to highlight that objects are useful (if not mandatory) sometimes. Concerning AQ, IMO this is only due to the fact that the RECORD datatype does not exist as a SQL datatype but only as a PL/SQL datatype, which leads to the use of objects without methods to simulate records... which is not what I would call "100% neat" .

    If only computer science was always as neat and clear as a good relational modeling ...

    Best Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Apr 2005
    Posts
    127
    Hi Andrews and Rbaraer

    Thanks for ur efforts, now with your guidance i am able to work on it.

    Thanks again,
    Sn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •