1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need some help understanding member functions for objects

Discussion in 'SQL PL/SQL' started by ssjaronx4, Jan 7, 2012.

  1. ssjaronx4

    ssjaronx4 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Guys,

    I'm currently doing a project for uni based on Oracle. I'm trying to get the hang of using member functions but I'm a bit stuck so thought someone may be able to help. Ive created an object and given it a function. Basically when I call the function I want it to bring back the first and last name columns in a string as the result. It doesnt work so can someone point out where I'm going wrong?


    create or replace
    TYPE client_ty AS OBJECT
    (clientno number(4),
    client_firstname varchar2(30),
    client_lastname varchar2(30),
    address1 varchar2(20),
    town varchar2(20),
    postcoe varchar2(10),
    totalsales number(6),
    MEMBER FUNCTION
    get_fullname RETURN varchar2);


    create or replace
    TYPE BODY Client_ty
    AS
    MEMBER FUNCTION
    get_fullname RETURN VARCHAR2 IS
    BEGIN
    RETURN (client_firstname || ' ' || client_lastname);
    END;
    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Below is a working object type including member functions; please examine it to see how to construct such types:

    Code (SQL):
    CREATE OR REPLACE TYPE string_agg_type AS object
    (
       total varchar2(4000),
     
       static FUNCTION
            ODCIAggregateInitialize(sctx IN OUT string_agg_type )
            RETURN NUMBER,
     
       member FUNCTION
            ODCIAggregateIterate(SELF IN OUT string_agg_type ,
                                 VALUE IN varchar2 )
            RETURN NUMBER,
     
       member FUNCTION
            ODCIAggregateTerminate(SELF IN string_agg_type,
                                   returnValue OUT  varchar2,
                                   flags IN NUMBER)
            RETURN NUMBER,
     
       member FUNCTION
            ODCIAggregateMerge(SELF IN OUT string_agg_type,
                               ctx2 IN string_agg_type)
            RETURN NUMBER
    );
    /
    CREATE OR REPLACE TYPE body string_agg_type
    IS
     
    static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    RETURN NUMBER
    IS
    BEGIN
        sctx := string_agg_type( NULL );
        RETURN ODCIConst.Success;
    END;
     
    member FUNCTION ODCIAggregateIterate(SELF IN OUT string_agg_type,
                                         VALUE IN varchar2 )
    RETURN NUMBER
    IS
    BEGIN
        SELF.total := SELF.total || ',' || VALUE;
        RETURN ODCIConst.Success;
    END;
     
    member FUNCTION ODCIAggregateTerminate(SELF IN string_agg_type,
                                           returnValue OUT varchar2,
                                           flags IN NUMBER)
    RETURN NUMBER
    IS
    BEGIN
        returnValue := ltrim(SELF.total,',');
        RETURN ODCIConst.Success;
    END;
     
    member FUNCTION ODCIAggregateMerge(SELF IN OUT string_agg_type,
                                       ctx2 IN string_agg_type)
    RETURN NUMBER
    IS
    BEGIN
        SELF.total := SELF.total || ctx2.total;
        RETURN ODCIConst.Success;
    END;
     
     
    END;
    /
    CREATE OR REPLACE
    FUNCTION stragg(INPUT varchar2 )
    RETURN varchar2
    PARALLEL_ENABLE AGGREGATE USING string_agg_type;
    /