Creating Objects for OOP

[dougalim]

I'm trying to figure out if I can use the OO capabilities in Oracle. I've had a little success but while trying to do a bit more I'm getting errors and don't know if I can do what I'm trying to do.

I want to instantiate an object where the constructor loads a table with a small number of rows into a collection. A member function can then return values from the collection using the parameters passed to the function. Can I do it? I'm struggling to find a web site that shows anything beyond really basic code examples.

Here's the code I tried:

CREATE OR REPLACE TYPE agingBucket AS OBJECT(
	
    CURSOR agingBuckets_cur IS
    SELECT *
    FROM aging_buckets,
          
    TYPE agingBucketsType IS 
        TABLE OF VARCHAR2(20)
        INDEX BY PLS_INTEGER,
                
    agingBuckets	agingBucketsType,
            
	CONSTRUCTOR FUNCTION agingBucket RETURN SELF AS RESULT
    
    ,MEMBER FUNCTION getAgingBucket(
        p_service_date		DATE
    ) RETURN VARCHAR2
    
) INSTANTIABLE FINAL;
/

CREATE OR REPLACE TYPE BODY agingBucket AS
    
	CONSTRUCTOR FUNCTION agingBucket RETURN SELF AS RESULT IS
    
    BEGIN
    
        OPEN agingBuckets_cur;
        FETCH agingBuckets_cur BULK COLLECT INTO agingBuckets;
        CLOSE agingBuckets_cur;
             
        RETURN;
        
    END agingBucket;
    
    --
    
    MEMBER FUNCTION getAgingBucket(
        p_service_date		DATE
    ) RETURN VARCHAR2 IS
                 
        l_aging_bucket	VARCHAR2(5);
        l_months	NUMBER(3);
              
    BEGIN
        
        l_months := floor(months_between(last_day(SYSDATE), last_day(p_service_date)));

        FOR i IN 1 .. agingBuckets.count
        LOOP
            
            l_aging_bucket := agingBuckets(i).aging_bucket_cd;
                
            EXIT	WHEN l_months >= agingBuckets(i).from_range 
                    AND l_months <= agingBuckets(i).to_range;
                        
        END LOOP;
                                  
        RETURN l_aging_bucket;

    END;
  
END;
/

 

[dougalim]

I was experimenting a little to try and get past errors. Instead of

    TYPE agingBucketsType IS 
        TABLE OF VARCHAR2(20)
        INDEX BY PLS_INTEGER,

it should read

    TYPE agingBucketsType IS 
        TABLE OF agingBuckets_curs%rowtype
        INDEX BY PLS_INTEGER,