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;
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