Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: How can I create a type object from cursor%rowtype? Alternatives?

    I'm new to PL/SQL and got my first working package. Maybe I'm asking something that actually is not possible because of my short knowledge. So I also ask for alternatives. I'm using Oracle 9.2.0.4.0.

    The problem: Make an application on PL/SQL that makes an update and insert (on a different table as history) for every row on a cursor. Easy at the moment. Also if the process for every row fails for any reason, catch the exception, save the error message and continue to process the next row. Show to the user the actual result set on the cursor with it's corresponding message about success or error (showing the state of data before it's processed).

    Performance needed? I asked how many rows the cursor could have at the moment of execution, I was told at most 100. So I decided to do a simple nested begin-end block for every update-insert:

    Code:
    CREATE OR REPLACE TYPE objAcknowledgement AS
    		-- Cursor structure plus one column for the message result
        OBJECT  (KEY_ENTITY             VARCHAR2(5),
                 ID_CONTRACT            NUMBER(12),
                 NUM_CONTRACT           VARCHAR2(20),
                 TODAY                  DATE,
                 ID_PROMOTER            NUMBER(10),
                 NAME_PROMOTER          VARCHAR2(255),
                 KEY_CURRENCY           VARCHAR2(3),
                 DESC_AMOUNT            VARCHAR2(50),
                 AMOUNT_DISC_PROMOT     NUMBER(16),
                 AMOUNT_INFAV_PROMOT    NUMBER(16),
                 MESSAGE                VARCHAR2(255)
                 ); 
    /
    
    CREATE OR REPLACE TYPE ttAcknowledgement AS TABLE OF objAcknowledgement;
    /
    
    CREATE OR REPLACE PACKAGE pkgAcknowledgement AS
        FUNCTION funAcknowledgement (pEntity        IN CB_BALANCE_PROM_DET.KEY_ENTITY%TYPE,
                                     pUserId        IN CB_BALANCE_PROM_DET.ID_USER_ACKNOW%TYPE,                            
                                     pMessage      OUT VARCHAR2 ) RETURN ttAcknowledgement;
        -- ... more functions ...                                  
    END pkgAcknowledgement;
    /
    
    CREATE OR REPLACE PACKAGE BODY pkgAcknowledgement AS
        FUNCTION funAcknowledgement (pEntity        IN CB_BALANCE_PROM_DET.KEY_ENTITY%TYPE,
                                     pUserId        IN CB_BALANCE_PROM_DET.ID_USER_ACKNOW%TYPE,                           
                                     pMessage      OUT VARCHAR2 ) RETURN ttAcknowledgement
        IS                                                          
            CURSOR curBalance (KeyEntity    IN CB_BALANCE_PROM_DET.KEY_ENTITY%TYPE := pEntity)
            IS  
                SELECT KEY_ENTITY,              -- value to insert
                       ID_CONTRACT,             -- value to insert
                       C.NUM_CONTRACT,          -- info for user
                       Z.TODAY,                 -- info for user
                       Z.ID_PROMOTER,           -- info for user and value to insert
                       (SELECT NAME_PERSON
                          FROM CORP_PERSON
                         WHERE KEY_ENTITY = KeyEntity
                           AND ID_PERSON = Z.ID_PROMOTER)
                               AS NAME_PROMOTER,    -- info for user
                       Z.KEY_CURRENCY,              -- info for user and value to insert
                       Z.DESC_AMOUNT,               -- info for user
                       Z.AMOUNT_DISC_PROMOT,        -- criteria
                       Z.AMOUNT_INFAV_PROMOT,       -- criteria
                       'EMPTY' AS MESSAGE           -- info for user about process 
                  FROM CTO_CONTRACT C
                 RIGHT JOIN
                      (SELECT KEY_ENTITY,
                              ID_CONTRACT,
                              ID_PROMOTER,
                              to_char(sysdate,'dd/mm/yyyy') AS TODAY,
                              KEY_CURRENCY,
                              to_char(AMOUNT_INFAV_PROMOT,'999,999,999,999,990.00') AS DESC_AMOUNT,
                              AMOUNT_DISC_PROMOT,
                              AMOUNT_INFAV_PROMOT
                         FROM CB_BALANCE_PROM
                        WHERE KEY_ENTITY = KeyEntity
                          AND AMOUNT_DISC_PROMOT = AMOUNT_INFAV_PROMOT
                          AND IMP_SDO_VIRTUAL = 0
                          AND AMOUNT_INFAV_PROMOT > 0
                          AND AMOUNT_DISC_PROMOT > 0
                       ) Z
                USING (KEY_ENTITY, ID_CONTRACT)
                FOR UPDATE OF AMOUNT_DISC_PROMOT, AMOUNT_INFAV_PROMOT WAIT 10        
            ;
    
            recBalance curBalance%ROWTYPE;
            tResult ttAcknowledgement := ttAcknowledgement();
            recSys SYS_REFCURSOR;
            no_data_found EXCEPTION;    
    
        BEGIN
            OPEN curBalance;
            LOOP
                SAVEPOINT spCurrentBalance;
                FETCH curBalance INTO recBalance;
                IF ((curBalance%ROWCOUNT = 0) AND (curBalance%NOTFOUND)) THEN
                   RAISE no_data_found;
                END IF;            
                EXIT WHEN curBalance%NOTFOUND OR curBalance%NOTFOUND IS NULL;
    
                -- Local-block
                BEGIN                
                    UPDATE CB_BALANCE_PROM
                       SET AMOUNT_INFAV_PROMOT = 0,
                           AMOUNT_DISC_PROMOT = 0
                     WHERE CURRENT OF curBalance;
    
                    INSERT INTO CB_BALANCE_PROM_DET (KEY_ENTITY,
                            DATE_TRANS, ID_PROMOTER, ID_CONTRACT,
                            KEY_CURRENCY, KEY_TRANS, AMOUNT_TRANS,
                            CURRENCY_TRANS, DESC_TRANS, ID_USER_ACKNOW)
                    VALUES (recBalance.KEY_ENTITY,
                            to_date(sysdate,'dd/mm/yyyy'),
                            recBalance.ID_PROMOTER,
                            recBalance.ID_CONTRACT,
                            recBalance.KEY_CURRENCY,
                            'CDPSFPR',
                            recBalance.AMOUNT_INFAV_PROMOT,
                            1,
                            (SELECT DESC_LARGE
                            FROM CB_TRANSACTION
                            WHERE KEY_ENTITY=pEntity
                            AND KEY_TRANS='CDPSFPR'),
                            pUserId
                            );
    
                    -- Success message to collection                    
                    recBalance.MESSAGE := 'OK';           
    
                EXCEPTION
    
                    WHEN OTHERS THEN
                        -- Error message to collection
                        recBalance.MESSAGE := 'ERROR: ' || Trim(SubStr(SQLERRM, 1, 255));
                        -- Rollback particular process for one row and continue with next row
                        ROLLBACK TO spCurrentBalance;
                END;
    
            -- Every processed result is copied to collection 
            tResult.EXTEND;
            tResult(tResult.COUNT) := objAcknowledgement(recBalance.KEY_ENTITY,recBalance.ID_CONTRACT,
                                                       recBalance.NUM_CONTRACT,recBalance.TODAY,
                                                       recBalance.ID_PROMOTER,recBalance.NAME_PROMOTER,
                                                       recBalance.KEY_CURRENCY,recBalance.DESC_AMOUNT,
                                                       recBalance.AMOUNT_DISC_PROMOT,recBalance.AMOUNT_INFAV_PROMOT,
                                                       recBalance.MESSAGE  );    
            END LOOP;    
            CLOSE curBalance;
    
            COMMIT;
            pMessage := 'Acknowledgement process successfully completed';
            
            RETURN tResult;
                        
        -- Global Exceptions for funAcknowledgement --
        EXCEPTION
    
            WHEN no_data_found THEN
                ROLLBACK;
                pMessage := 'funAcknowledgement: No rows found, no changes made.';
                IF tResult.COUNT > 0 THEN
                    tResult.TRIM(tResult.COUNT);
                END IF;                  
                RETURN tResult;
    
            WHEN OTHERS THEN
                ROLLBACK;
                pMessage := 'funAcknowledgement Error: '||Chr(10)||
                Trim(SubStr('Error: '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255))||Chr(10);
                IF curBalance%ISOPEN THEN
                    CLOSE curBalance;
                END IF;
                IF tResult.COUNT > 0 THEN
                    tResult.TRIM(tResult.COUNT);
                END IF;  
                
                -- Prepararing results
                OPEN curBalance;
                LOOP
                     FETCH curBalance INTO recBalance;
                     EXIT WHEN curBalance%NOTFOUND OR curBalance%NOTFOUND IS NULL;
                     recBalance.MESSAGE := 'ERROR: ' || Trim(SubStr(SQLERRM, 1, 255));                                
                     tResult.EXTEND;
                     tResult(tResult.COUNT) := objAcknowledgement(recBalance.KEY_ENTITY,recBalance.ID_CONTRACT,
                                                       recBalance.NUM_CONTRACT,recBalance.TODAY,
                                                       recBalance.ID_PROMOTER,recBalance.NAME_PROMOTER,
                                                       recBalance.KEY_CURRENCY,recBalance.DESC_AMOUNT,
                                                       recBalance.AMOUNT_DISC_PROMOT,recBalance.AMOUNT_INFAV_PROMOT,
                                                       recBalance.MESSAGE  ); 
                END LOOP;    
                CLOSE curBalance;
                RETURN tResult;
    
        END funAcknowledgement;
    
        -- ... more functions ...  
     END pkgAcknowledgement;
    Now the problem I see for some reason on the future, if the datatype of any column on the cursor are changed, then the object would need to be re-edited and re-compiled? I was looking a way to do the function without the CREATE TYPE declarations outside the package but didin't found a way. Is there a way?

    I would really appreciate any advice on my approach. Also critics are welcome. I'm here to learn (and help as soon as I can).

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Change is inevitable.

    If the datatype of any column on the cursor are changed, it means the structure of one of the underlying tables of the cursor has changed. Therefore ALL (or most of) the references to the changed column of that table may need to be modified also.

    So if the schema changes, the least of your worries would be changing a type definition.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    Quote Originally Posted by LKBrwn_DBA View Post
    If the datatype of any column on the cursor are changed, it means the structure of one of the underlying tables of the cursor has changed. Therefore ALL (or most of) the references to the changed column of that table may need to be modified also.

    So if the schema changes, the least of your worries would be changing a type definition.
    That's are my worries but I can't find a solution. What would be other way to do it? I tried a collection declared inside the function with structure cursor%rowtype (when I still wasn't using a package) and also a record with the cursor%rowtype but then I found myself into compilation errors because of data not compatible, I tried many ways and this one was the one to work. I found myslef also needing the object constructor to be able to update the collection.

Posting Permissions

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