Hi guys
Prior moving to Oracle EBS (R12) we were using a mini ERP system, that was as robust as possible, however lacked many features those were required for modern business environments. While the company that distributed the software in the market over a decade finally dissolved, we were also provided a full copy of the source codes & the same was in my custody for whole these years.
Recently I wanted to revive it, after WebLogic 12c & seeing the possibility of “JAVA WEBSTART” in small & middle scale industries.
One of the major hurdles I came across were with the columns those saved document numbers. This software which was designed during late 90s only had maximum 6 digits document numbers across modules, totaling 496 tables!
It was not a very feasible idea to go through each table & to modify them using GUI tools like Oracle SQL developer. Instead I planned for a PL/SQL procedure.
& here comes the PL/SQL block
[code language=”sql” gutter=”false”]
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;
CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;
V_EXECUTE_MODIFY BOOLEAN := FALSE; –‘TRUE’ FOR TABLE MODIFY
BEGIN
FOR T IN T1(‘OPMAN’) LOOP
–DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||’—-‘||T.TNAME);
FOR C IN C1(‘OPMAN’,T.TABLE_NAME) LOOP
IF (C.DATA_TYPE=’NUMBER’ AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN
DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ‘ COLUMN NAME –>’||C.COLUMN_NAME);
–T_YR_MTH
IF (V_EXECUTE_MODIFY) THEN
IF (C.COLUMN_NAME ‘T_YR_MTH’) THEN
/*INCLUDE ALL COLUMN NAMES THOSE SHOULDN’T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN (‘ABC’,’DEF’,’GHI’)*/
EXECUTE IMMEDIATE ‘ALTER TABLE ‘||T.TABLE_NAME||’ MODIFY ‘||C.COLUMN_NAME||’ NUMBER’;
END IF;
END IF;
END IF;
END LOOP;
END LOOP;
END;
[/code]
So what does this block do?
[code language=”sql” gutter=”false”]
CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;
[/code]
This block fetches all table names from the DBA_TABLES for the owner that is passed in as a parameter, in this specific case ‘OPMAN’
[code language=”sql” gutter=”false”]
CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;
[/code]
The above cursor takes in two parameters, OWNER name and the table name, that comes from the 1st cursor
[code language=”sql” gutter=”false”]
FOR T IN T1(‘OPMAN’) LOOP
–DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||’—-‘||T.TNAME);
FOR C IN C1(‘OPMAN’,T.TABLE_NAME) LOOP
IF (C.DATA_TYPE=’NUMBER’ AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN
DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ‘ COLUMN NAME –>’||C.COLUMN_NAME);
–T_YR_MTH
IF (V_EXECUTE_MODIFY) THEN
IF (C.COLUMN_NAME ‘T_YR_MTH’) THEN
/*INCLUDE ALL COLUMN NAMES THOSE SHOULDN’T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN (‘ABC’,’DEF’,’GHI’)*/
EXECUTE IMMEDIATE ‘ALTER TABLE ‘||T.TABLE_NAME||’ MODIFY ‘||C.COLUMN_NAME||’ NUMBER’;
END IF;
END IF;
END IF;
END LOOP;
END LOOP;
[/code]
Let us analyze this part of the block now.
The first cursor passes the table name to 2nd cursor and the loop goes through all columns matching data type, precision and scale. I’ve checked for “Number”, precision 6 & scale 0 which are common for all columns those were defined for document numbers. However there were few instances where YearMonth (YYYYMM) values captured for month end processes, which shouldn’t be checked. As our mini ERP is a standard business application, it was designed following same nomenclature across tables while columns defined. Thus it become easier for me to isolate the columns whose were matching same data type, precision and scale & to limit the alter commands modifying only those columns which were defined for document numbers.
The above example could be further developed to easily alter tables while the entire application is modernized and to easily modify columns with bigger sizes.
Interesting? Why don’t you give it a try and post your comments?
regards,
rajesh