Wednesday, March 14, 2018

How to implement continue option in Oracle PL/SQL block?

PL/SQL:

DECLARE 
TNAME VARCHAR2(100);
BEGIN
    FOR I IN (SELECT TABLE_NAME FROM DM_TABLES)
    LOOP
       begin 
        SELECT TABLE_NAME INTO TNAME FROM ALL_TAB_COLS WHERE OWNER ='SYSTEM' AND TABLE_NAME = I.TABLE_NAME AND COLUMN_NAME='VERSION';
        
        EXCEPTION
            WHEN NO_DATA_FOUND THEN GOTO end_loop;
            
        end;
        INSERT INTO DM_TABLE_VERSION VALUES(TNAME);
        <<end_loop>>
        null;  
    END LOOP;
    COMMIT;
END;


Note:
<<end_loop>> - Lable name


/*Whenever there is no table with version column then I need to skip that particular record, so I need to go end of loop and followed by null*/
EXCEPTION
            WHEN NO_DATA_FOUND THEN GOTO end_loop;

No comments:

Post a Comment